Code: Select all
$OriginalDate = 'YYYY-MM-DD'
$UnixDate = mktime(0, 0, 0, substr($OriginalDate, 5, 2), substr($OriginalDate, 8, 2), substr($OriginalDate, 0, 4));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:00Does 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