How to get entries from DB depending on timezone?

[eluser]esset[/eluser]
Wow - thanks for all the good comments.

Jelmer: In what format do you store the shows date?

Do you store them as GMT+0 or as they are with the GMT calculated in? (Maybe its the same result in the end?)

Code:

strtotime('2010-04-12 15:00:00 +0300');
strtotime('2010-04-12 18:00:00 +0000');

Also would this approach give you shows that overlap months, i.e. if a show airs at 2010-04-30 23:30 at GMT+5 and you are in Europe (which would make the airdate 2010-05-01 05:30)?


Thank you so much for the help, this is clearing up a lot for me

[eluser]Jelmer[/eluser]
The endresult of strtotime() is the amount of seconds since (or before) January 1st 1970 GMT, which you might consider in GMT but in reality has no timezone. That's the advantage of using UNIX timestamps because all dates are saved in a format that doesn't care about timezones or dates.

Because it's a simple int you can make whatever selection you want between two points in time you want. If you look at my previous example you'll see that this selects the EST:

Code:

$start_date = strtotime('2010-04-12 00:00:00 -0500');
$end_date = strtotime('2010-04-12 23:59:59 -0500');

Which would get the exact same timestamps as this one (same moment in time but in the GMT timezone):

Code:

$start_date = strtotime('2010-04-12 05:00:00 0000');
$end_date = strtotime('2010-04-13 04:59:59 0000');

Both these examples will return:

Code:

$start_date == 1271048400;
$end_date == 1271134799

[eluser]WanWizard[/eluser]
Unfortunately, dealing with timezones is a lot more complicated than this.

DST rules change on a regular basis. To be able to convert a date/time input on a form by a user, you need to know to which timezone that date/time belongs. You then have to figure out the DST settings for that date/time given the timezone. Now you can convert it correctly to GMT. When you want to display it, you need to know the timezone in which to display it, and the DST settings in that timezone for the given date/time. Now you can convert the stored GMT timestamp to the proper 'local' value.

Check this http://derickrethans.nl/storing-date-tim...abase.html from the author of PHP's new DateTime class...