Page 1 of 1

*** using mySql timestamp date with php date() :(

Posted: Mon Jan 19, 2004 7:30 pm
by nin9999
IM a newbie and I am really stumped here. In my mySql DB i have a dateTime field of type TIMESTAMP(14). When I retrieve this dateTime value (i.e. 20040125103000 for Sunday Jan 25 2004 10:30) and i use php's date() function I get incorrect results. Here is an example of my problem ...

$timeStamp1 = 20040125103000;

$day = date("l, M d", (int)$timeStamp1); //i.e. Sunday, Jan 25
$time = date("g:i a", (int)$timeStamp1); //i.e. 10:30 am

echo "<br>Day = $day and time = $time";

My output should say - Day = Sunday, Jan 25 and Time = 10:30 am

But my output is - Day = Thursday, Nov 28 and time = 1:17 am

I searched the forums and read the manuals and couldnt find my problem.

Any help would be very much appreciated.
TIA
J

Posted: Mon Jan 19, 2004 9:39 pm
by xisle
yeh just [php_man]explode()[/php_man] the MySQL date to get what you want or insert the date into your database as a UNIX_TIMESTAMP()

PHP [php_man]date()[/php_man] is based on a unix timestamp not on the mysql timestamp format...

Posted: Mon Jan 19, 2004 11:28 pm
by nin9999
xisle the timestamp is in format 20040120123000 so there is nothing to explode ... but i think im going to take the easy way out and just use the format 1-20-04 6:00am etc so I can use the current timestamp.

If I was however to use the UNIX_TIMESTAMP() option how do I go about getting the time for an event in the future to be a UNIX TIMESTAMP? Like if I want to enter a time in the db for May 5th 2004 at 9:00 am how would I go about converting that to a UNIX timestamp before i enter it into the db?

Thanks for the help!
J

Posted: Tue Jan 20, 2004 4:26 am
by twigletmac
You can use MySQL's DATE_FORMAT() function to format the date as it comes out of the database:
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

If you decide to change to using Unix timestamps in your table you'll need to change the type of field you're using for storage - from TIMESTAMP to INT and then you can use PHP's [php_man]mktime[/php_man]() function to create a timestamp or MySQL's UNIX_TIMESTAMP() function on the data going into the table.

Mac

Posted: Tue Jan 20, 2004 6:59 pm
by nin9999
mac,
Thats exactly what I needed!

Much appreciated and thanks for your time!
J

Posted: Wed Jan 21, 2004 3:01 pm
by xisle
nin9999 wrote:xisle the timestamp is in format 20040120123000 so there is nothing to explode ...
forgot the DATE_FORMAT() on output,
looks like Macs got you covered... 8)