CODEWITHSUNDEEP
phpmysqldatetime
William Bruce
William Bruce

Reputation: 55

DateTime countdown and expiration

I have posts on my site that have an expiration datetime I store these in Mysql in as

'Y-m-d H:i:s'

ie. 2012-08-28 00:00:00

I convert this datetime to the format below which is passed to a jquery countdown

$offset.', Y, n - 1, j, H, i, s'

This part works the way it should.

What I am trying to do is get the current time and only get the unexpired posts from the database. The thingM that is tripping me up is that all the post have different timezones. Most of them expire at midnight but in different timezones some expire at midnight eastern while others expire midnight pacific. These are all stored as 2012-08-28 00:00:00 with a timezone stored in the database as timezone.name ie. America/Eastern. For the countdown the $offset is created from this value being -4 so the input to the countdown is 

-4, 2012, 8 - 1, 28, 00, 00, 00

Which work for the jquery counter I am using but it doesnt work when trying to compare current date to the mysql date entry. I have tried setting the timezone before getting the mysql datetime entry and storing a value like this for eastern

2012-08-27 20:00:00

when I try to set current time and I use my America/Phoenix timezone I get a value that is at 9pm when the eastern post should expire I get

2012-08-28 21:00:00

which to me means the post would have expired an hour early. This is were I am stuck I am open to storing the data in multiple db entries on for the countdown and one for the expiration db check I just dont know what the best way to achieve what I need is. Any help is appreciated. Thanks.

Upvotes: 3

Views: 922

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

I personally would convert all expiration times to UTC (or some other defined timezone - maybe that of your DB server and application server if they are the same) before entering them into the database table and then query against that table based on the the timezone of the particular user. So for example the WHERE clause might look like

WHERE expiration_datetime > NOW() + INTERVAL X HOUR

where X is where you apply the hour difference for user's timezone.

Upvotes: 1

Marcin Orlowski
Marcin Orlowski

Reputation: 75629

You got your database designed wrong if you allow posts timestamp to be not normalized. I is irrelevant what timezone user is in as long as all the timestamp are in the same timezone there's no big deal. So answer to your question is - fix your desing, keep all timestamps in the same timezone (GMT for simplicity).

Upvotes: 0

Related Questions