Link to home
Create AccountLog in
Avatar of marcparillo

asked on

MySQL + PHP Timestamp advice

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,

Avatar of effx
Flag of United Kingdom of Great Britain and Northern Ireland image

Could you not work out the offset on the unix time?
Avatar of marcparillo


Can you give me an example of what you mean?  
YOURUNIXTIME - (60*60*2)

I think thats right for minus 2 hours
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.
"... 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.
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.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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.
Thanks for the points.  It's a great question, ~Ray