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

effx

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

marcparillo

ASKER

Can you give me an example of what you mean?

effx

YOURUNIXTIME - (60*60*2)

I think thats right for minus 2 hours

marcparillo

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.

Ray Paseur

"... 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.

marcparillo

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(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.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

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.

marcparillo

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.

Ray Paseur

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