We help IT Professionals succeed at work.

MySQL + PHP Timestamp advice

marcparillo
marcparillo asked
on

What's the best method for recording times in a MySQL database?  For years, I've used unix timestamps (i.e. 1323092395) but when I need to take DST into account, it becomes an issue.

For example, Client A wants an e-mail sent to her at 5am every day.  The time is recorded as a unix timestamp in my database.  When DST starts, the server time is automatically adjusted back an hour, and suddenly, Client A starts receiving e-mails at 4am because the unix timestamp is a relative number, not a fixed point in time such as a UTC timestamp.

I've read Ray Paseur's great article from EE about Handling Date and Time in PHP and MySQL, but those solutions are all PHP based.  I would like to do the time adjustments on the MySQL end, so I don't have to perform a time function on every record in the DB.

Should I use UTC, RFC 2822?  If I use those formats, would I be able to perform a MySQL query that takes into account the time change?

Thank you,
marc


Comment
Watch Question

Commented:
Could you not work out the offset on the unix time?

Author

Commented:
Can you give me an example of what you mean?  

Commented:
YOURUNIXTIME - (60*60*2)

I think thats right for minus 2 hours

Author

Commented:
I understand your suggestion, but I'm looking for something more durable, I guess.  I don't want to be changing my code every time DST comes and goes.  I was hoping there was a way to maintain the actual time in MySQL.  I'm thinking using DATETIME may be the solution. I'll keep looking around.
Most Valuable Expert 2011
Top Expert 2016

Commented:
"... perform a time function on every record in the DB"

Why not?  MySQL cannot send the email messages, so you will probably be needing to use PHP for something.  I would just do this the easy way.  Store the client's timezone information and adjust the time zones as needed for each client.

Author

Commented:
Hi Ray,

Thanks for responding.  If I had a database of several hundred people, all of whom need an e-mail sent a different times of the day, I was trying to avoid looping through the DB every minute.  I would just like the DB to return records that match the current hour and minute of the server, depending on how the timestamp was recorded.  Something like:

... if storing a unix timestamp
SELECT first, last, email from EmailTable WHERE DATE_FORMAT(from_unixtime(emailtime),'%H:i%')=09:00;

... if storing the time like YYYY-MM-DD HH:II:SS
SELECT first, last, email from EmailTable WHERE HOUR(emailtime)=9 AND MINUTE(emailtime)=0;

Correct me if I'm wrong, but if I recorded today's timestamp in a database (2011-12-05 09:00:00) and then looked up that same record in June, I would get 2011-12-05 10:00.. right? Because DST would be in effect in June.

Admittedly, I think I'm making this too complicated, but I just can't seem to figure out a durable solution that doesn't require me changing a configuration file every time we enter/exit DST.
Most Valuable Expert 2011
Top Expert 2016
Commented:
If you really want to write programming that is sensitive to DST, you can find out about DST with a fairly simple test.  See date('I') on this page.
http://php.net/manual/en/function.date.php

I believe that PHP will rely on the value of date_default_timezone_set() in making this setting.  Example: Phoenix, Arizona has different rules for DST from the rest of the state.

There is a difference between a timestamp and an ISO-8601 DATETIME string.  A timestamp (Unix timestamp) is the number of seconds since the UNIX epoch began.  It began in UTC, so the timestamp is the same all over the world.  However the local DATETIME values differ for different time zones and at different times of the year.

See practical application #8 here.  A little bit of JavaScript might be all you need to capture the offsets so you can handle this automatically.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
Most Valuable Expert 2011
Top Expert 2016

Commented:
Regarding this: "if I recorded today's timestamp in a database (2011-12-05 09:00:00) and then looked up that same record in June, I would get 2011-12-05 10:00.. right?"

I expect that you would get back exactly what you put into the database.  PHP and MySQL have different and independent time zone settings.

Author

Commented:
So, I think I'm starting to understand better.   I just did a test on my Unix server and, sure enough, I got back what I put into the database, even after changing the server time ahead and back one hour.  

Your suggestions were very helpful.  Thank you.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Thanks for the points.  It's a great question, ~Ray