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,
marc
MySQL ServerPHP
Last Comment
Ray Paseur
Could you not work out the offset on the unix time?
ASKER
Can you give me an example of what you mean?
YOURUNIXTIME - (60*60*2)
I think thats right for minus 2 hours
I think thats right for minus 2 hours
ASKER
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.
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.
ASKER
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(
... 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.
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(
... 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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
I expect that you would get back exactly what you put into the database. PHP and MySQL have different and independent time zone settings.
ASKER
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.
Your suggestions were very helpful. Thank you.
Thanks for the points. It's a great question, ~Ray