Timezone and daylight savings time?


#1

Hi,

I have a website that I want to migrate to DH. One issue I see, with a staged version of the site that I have, is that there are pages that show upcoming events (e.g. showing the next two upcoming events, which are stored in a database) and pages that record and then display the time (e.g. new items created).

I’m using PHP and MySQL, and am in the Eastern US timezone. With my current provider, the servers (web & database) are in the same timezone, however, DH servers are in Pacific timezone.

I searched the forums and saw a suggestion to set the timezone in the .htaccess file for PHP solution. That worked, however, the time is still off, as the time used is not accounting for daylight savings time, which we are currently in.

Is there a way to account for this (automatically)? Preferably, without having to change the scripts, where dates are used?

Note, I do have a custom php.ini file, as I needed to adjust other PHP settings. Is there a way to set the default there?

For MySQL, I have queries that use the date/time stored in a record, and compare it to NOW(). What method can I use to either set the default timezone (automatically adjusting for DST), or adjust the time (again, automatically accounting for DST)?

I’m assuming I can probably use DATE_ADD (or it’s inverse) to adjust, but I’m wondering about DST handing.

Please let me know, as it’s the last blocking item (I think :^) to migrating.

PCM


#2

What format are you storing dates/times in? MySQL DATETIME values work fine as long as you don’t care about time zones, but they just store the literal value you see (e.g, “2011-03-15 10:03:02”), so they’re difficult to do timezone conversions on. If you’re using PHP, you’re better off storing UNIX timestamps (e.g, 1300208582 - the result of the PHP time() function), but that will require some significant changes to your code to support.

Bottom line - time zones are tough!


#3

For dates/times stored for things like events, they are in DATETIME format, with the implied timezone/DST of Eastern. I just need to be able to compare “now” to the entry, but now needs to be Eastern time and considering the DST.

This is the most critical, as we need to be able to reflect the date/time of when an event starts or ends (e.g. registration closes).

I’m thinking I can adjust the time comparisons in the SQL queries, but I’m not sure how to make sure it considers DST (automatically).

For most other cases, I use PHP to capture “now”, and store that in the database as a DATETIME, for things like recording the date/time of a news entry created. Again, PHP needs to be using Eastern time zone with the DST taken into consideration.

The .htaccess seems to solve this, except it doesn’t account for DST (at least how I did it). Is there another way to have it also consider DST, or can I do this in php.ini?

PCM