Page 1 of 1

Date Conversion Confusion (mysql vs php functions)

Posted: Thu Apr 19, 2007 4:12 pm
by Swede78
I have been transferring data from one mysql table to another. Before inserting the new data, I am converting the dates from a format of YYYY-MM-DD to a Unix Timestamp. I am using the following code to create the timestamp and forcing 12:00:00 am times:

Code: Select all

$OriginalDate = 'YYYY-MM-DD'

$UnixDate = mktime(0, 0, 0, substr($OriginalDate, 5, 2), substr($OriginalDate, 8, 2), substr($OriginalDate, 0, 4));
(Using PHP 4.3 and MySQL 4.x)

After the new database is populated, I am getting strange results when I pull the data back out to display on a webpage. When I use MySQL's built-in function FROM_UNIXTIME, I sometimes get a different result than when I convert the Unix Timestamp with php's Date() function.

I cannot figure out why only some of them are different. (By the way, the differences are by an hour.) I assume this has to do with time zones.

This is for my use, aka local use, only. So, I just want it to use the server's clock. I've never had this happen before when storing date/time as a UnixTimestamp in the database. After I noticed this, I did some testing of other databases I created with the same method. I pulled the timestamps in both ways (using Mysql's FROM_UNIXTIME and php's date() functions), and compared the results. They all matched.

So, why do the functions work alike in one database, but not another? The data is the same for both functions. And, why does it only happen some of the time for the db that's behaving strangely.

I don't know if this makes any sense. I'm trying to be as clear as possible. Here are some examples of the inconsistencies I'm talking about when creating the unix timestamp from the old format.

Code: Select all

Original Date: 2005-09-01
Unix Time (w/ ): 1125550800 | 09-01-2005 12:00:00
Unix Time (w/0): 1125554400 | 09-01-2005 1:00:00
Unix Time (w/1): 1125550800 | 09-01-2005 12:00:00

Original Date: 2005-01-03
Unix Time (w/ ): 1104732000 | 01-03-2005 12:00:00
Unix Time (w/0): 1104732000 | 01-03-2005 12:00:00
Unix Time (w/1): 1104728400 | 01-02-2005 11:00:00
The unix times are built with the mktime() function that I refer to earlier. The "w/ ", "w/0", and "w/1" describe when I use the is_dst parameter. "w/ " is the function's default.

Does anyone know of a "best practices" reference for storing date/times? I guess that depends on each situation. But, now I'm starting to worry that all my previous database structures are producing problems when it comes to dates.

Thanks

Posted: Thu Apr 19, 2007 4:29 pm
by John Cartwright
Nowadays I use MYSQL's datetime field to store my dates. The benefit is MYSQL can natively perform datetime query operations very easily using the datetime functions. http://dev.mysql.com/doc/refman/5.0/en/datetime.html might be of interest. However, many developers prefer to store their dates as timestamps to easily manipulate the output format of the date.

Code: Select all

$OriginalDate = 'YYYY-MM-DD'

$UnixDate = mktime(0, 0, 0, substr($OriginalDate, 5, 2), substr($OriginalDate, 8, 2), substr($OriginalDate, 0, 4));
can be simplified using strtotime() :wink:

Posted: Fri Apr 20, 2007 9:14 am
by Swede78
Thanks for the suggestions Jcart. I'll have to upgrade php and mysql one of these days to take advantage of some of the new datetime features. I forgot about strtotime... much easier to write, thanks.

A lot of my datetime manipulation takes place in the php code itself, where values come from various sources other than other database fields. So, I like to store them in ways I can more easily calculate differences, add and subtract, etc.

Also, here's some examples of the inconsistencies I'm getting when pulling the dates from the database (after they've been converted to unix timestamps), and displaying them.

Value in database: 1175230800
Using MySQL FROM_UNIXTIME produces: 03-29-2007 11:00 PM
Using PHP Date() produces: 03-30-2007 12:00 am

Value in database: 1172124000
Using MySQL FROM_UNIXTIME produces: 02-22-2007 12:00 AM
Using PHP Date() produces: 02-22-2007 12:00 am

I guess my concern is that one function reads it differently than the other, but inconsistently. Why does my first example show a difference of one hour, and in the second example, it shows the same time???

Posted: Fri Apr 20, 2007 2:06 pm
by Swede78
I think this just has to do with Daylight Saving Time. All the peculiar ones are around March. The two functions must handle DST differently. I just can't find documentation to support this. I'll use php to do the conversion rather than mysql, as php seems to take DST into account.