Date - timestamp & CURRENT TIMESTAMP

[Orangutang]

Hi,

I'm trying to echo out the date field from mysql db.

I set it up not by using anything in an INSERT statement but mysql functionality. I added a column called 'datesent' then set the field type as 'TIMESTAMP' with default value as 'CURRENT_TIMESTAMP'.

When I send the form now the 'datesent' cell is now populated with '2010-09-06 11:27:37' which was what I needed.

All I need to do with this date is echo it out as a confirmation. I use '$quoteid = mysql_insert_id()' to retrieve the 'quoteid' value which mysql populated and I was hoping there's an equivalent cmd to retrieve the 'datesent' value seeing as mysql populated that as well.

Or do I need to look at constructing a specific SELECT statement like 'SELECT DATE_FORMAT('THE_COLUMN_NAME', %M %d %Y) AS `formatted date` as I've seen on another post because I would rather display DDMMYYYY anyway.

And is there any security or performance issues because I'm not inserting the CURRENT DATE from the mysql INSERT statement which I've seen on another post, just using mysql as outlined above.

Any help or pointers please.

[Matthew1980]

Hi there Orangutang,

There may well be a way to do what your asking with mysql_insert_id(), but I would just pop another query in specifically to get the date in the date format your requesting using DATE_FORMAT as you have pointed out; though I suppose that once you have it working you could just pop a post in the mysql forum to see if there would be a way of combining those requests so that the overhead is on one query & not two. But as mysql is not a strong suite of mine I wouldn't like to hazard a guess.

Lol! Don't forget to quote the format parameter too, that's what I got wrong in that thread!

SELECT DATE_FORMAT(`THE_COLUMN_NAME`, "%M %d %Y")

Though you don't necessarily need to have the field name back ticked (I just find that it helps)

Cheers,
MRb

[Orangutang]

Hi Matthew,

Thanks for the help DATE_FORMAT was what I needed, many thanks. I've re-read the post and ammended "", lol, oops, :-)

I'm trying to get it to run but no matter what I try I always seem to get unexpected T_STRING error.

My code is:

$result = SELECT DATE_FORMAT(`datesent`, "%d %M %Y %T") FROM `quotes`;
$datesent = mysql_query ($result)or die (mysql_error());

echo $datesent;

Everything looks? logically ok to me, I create a variable called $result for the SELECT DATE_FORMAT statement. I then create a variable called $datesent for the error checking/displaying/debugging, mysql_query ($result)or die (mysql_error()). And finally I echo out the variable $datesent in what I hope will be my chosen format.

Have I understood what I'm trying to do correctly ?

Is it right but I've a syntax error ?

Or I'm wondering if its because I used mysql to create the date and time and it populated the cell with 2010-09-06 15:04:05 which I'm thinking may be a string but don't know?

Many thanks

[Matthew1980]

Hi there orangutang.

Your missing the rest of the date format syntax off: As `temp_var`. See the original posts in the other threads to see what i mean.

Hope that helps.

Cheers.
MRb

[rocknbil]

Do one of two things. If there are multiple records,

$result = SELECT DATE_FORMAT(`datesent`, "%d %M %Y %T") FROM `quotes`;
$datesent = mysql_query ($result)or die (mysql_error());

while ($row=mysql_fetch_array($datesent)) {
echo $row[0];
}

or


$result = SELECT DATE_FORMAT(`datesent`, "%d %M %Y %T") as date_sent FROM `quotes`;
$datesent = mysql_query ($result)or die (mysql_error());

while ($row=mysql_fetch_array($datesent)) {
echo $row['date_sent'];
}

If only one record is expected,

$result = SELECT DATE_FORMAT(`datesent`, "%d %M %Y %T") FROM `quotes`;
$datesent = mysql_query ($result)or die (mysql_error());
$row=mysql_fetch_array($datesent);
echo $row[0];


or


$result = SELECT DATE_FORMAT(`datesent`, "%d %M %Y %T") as date_sent FROM `quotes`;
$datesent = mysql_query ($result)or die (mysql_error());
$row=mysql_fetch_array($datesent);
echo $row['date_sent'];

[Orangutang]

Hi Guys,

Thanks for the help as always, I think I got mixed up because I've used mysql insert id to retrieve the AI field before. To retrieve the timestamp as well requires a very different approach. I would need to construct a SELECT statement which would pull out that particular cell date from that quote by that user at that time. And the time field would need to be retrieved first so I could use it for the SELECT statement. I think this was why I had problems with SELECT DATE_FORMAT, ie, what date, where.

What I've done is use the mysql timestamp CURRENT_TIMESTAMP to populate the datesent field which I will use later for retrieval and php date function to echo out the sent quote confirmation to user.

Interestingly I've noticed there is a 25 sec error between msql and php dates. When form sent timestamp populates mysql with say 2010-09-07 13:10:36 and php date echos out quote sent at 2010-09-07 13:10:11.

I thought the unix timestamp function in mysql was the equivalent to php date function but has auto updating for time zones. Curious about the 25 secs ?

What I've settled on is js calendar to sort date input so will always send y m d as mysql set to date field.

Then I can use a mysql BETWEEN statement to retrieve the data and SELECT DATE_FORMAT to retrieve the between dates, format them and display to the user.

I'm happy that I've at least found a way to retrieve data according to a date or between 2 dates but if anyone has got any advice about this method or a part of it I would very much appreciate any feedback because I'd rather not build it all only to find a better afterwards.

Thanks as always...

[omoutop]

why don't you save directly the timestamp date to your table? (as integer)
I find it easier for me

[Orangutang]

Hi omoutop,

Brills, thanks for the comment. If I may clarify, please bare with me.

When the form data is sent to mysql to populate the columns no date is sent. In mysql I added the column called 'datesent' then set the field type as 'timestamp' with default value as 'CURRENT_TIMESTAMP' and attribute as on update CURRENT_TIMESTAMP.

I did this because I like the way this particular setting automatically takes care of re-adjusting the dates and times for time zone differences before it sends them out. Which I thought saves me doing it.

I tried changing the type to integer rather than timestamp but sql errors. It only seems to let me set type to timestamp if I want to use the on update facility.

I've probably misunderstood but I'm not saving any date or time anywhere. mysql is populating the cell with its own produced date time when it receives the other form data. Like it does with the AI field, I don't send data to populate the timestamp cell, mysql does it.

Hopefully I've explained ok - Have I misunerstood ?

[Orangutang]

Hi,

Sorry but I haven't been as clear as I could of, I wrote about the 2nd issue at the end of the first.

Issue 1:

Send form to db with date it was sent and echo confirmation out with quote id (AI column) and date and time.

This was the one I used timestamp on to populate the date cell when the quote was sent and php date to echo out confirmation of date sent which is where the 25 sec error is.

Issue 2

User needs to enter delivery date required which will be date in the future. This is where I will try to use js calendar to sort input to INSERT statement (and where i will use your advice, set column as date and integer) then BETWEEN statement to get quotes with delivery dates between date x and date y and SELECT DATE_FORMAT to get and format the date period for display.

And Issue 2 is where I was reviewing the process and wondering if there was a better way.

Sorry about that, hopefully its a bit clearer.

[rocknbil]

there is a 25 sec error between msql and php dates.

Complain to your hosting service, this is because they are either not synchronized or there is an actual geographic difference that actually **makes** it a 25 second difference. Doing dates in an external program (PHP) when the tools are inherent in your database is just overhead, but whatever works, works.

[Orangutang]

Thanks for the heads up rocknbil, its nice to know why things happen when they do.

I'm staggered by how many different ways there are to achieve the same result.

At the min I'm happy to find any reasonable way of doing something. For me that's half the battle. Afterwards I try to do a review before I build it because I know there's, shall we say a vast area to make a mistake in.

Thanks again.