Timezone Daylight Saving problem in PHP and SQL

views:

answers:

+2 Q:

Timezone Daylight Saving problem in PHP and SQL

So in my PHP code, I do timezone selection for the user, where I have a table of timezones and their hour-shift values from GMT. I then use that to add to the DATETIME column values whenever the user picks his timezone in the SETTINGS.

So I have two functionalities: reading from DATETIME column in the database, and WRITING to the DATETIME column. Obviously, the first one I need to "add" the timezone difference before showing it to the user (using DATEADD() in SQL), while the second I need to "subtract" the timezone difference before store it in the database (also using DATEADD()). That way, all my stored timezones are in GMT, and viewed based on the selection of timezone of the user.

However, the Daylight Saving is the problem. What approach should I do (whether on SQL level OR PHP level), to solve this problem? Is using DATEADD() in SQL enough to take care of DAYLIGHT Savings? I have no clue. Please help!

GMT does not have daylight savings, so all your stored times should not have daylight savings within them. Before you store, and on retrieval you should also calculate the daylight savings. Only some areas of the world use it.

So in answer to your question no DATEADD() isn't good enough. If you're already finding out where they are, have another table or something to look up if that timezone does daylight savings. But even within some zones some areas have daylight savings and others don't.

Viper_Sb 2010-09-02 21:48:50

So do you mean I need first to set all dates in the database to EST, and to have a full table of daylight savings timezones, and then whenever a user selects/inserts a date, I need to check his default timezone, and if it is a daylight saving timezone, add that daylight difference if the daylight saving period matches the CURRENT time? Am I understanding correctly?Also, when I set timezone in database to EST for example, does it mean I need to physically instruct SQL Server (some setting?) to set timezone to that? Or is it just enough to have the convention that dates are stored in EST?

johnshaddad 2010-09-02 22:04:15

You should use what Remus posted, but to answer your question. You should store everything in UTC not EST or any other timezone, that way you can easily convert to any other zone from pretty much any language including SQL. And yes you were understanding it correctly, but Remus offered a better solution.

Viper_Sb 2010-09-03 13:18:07

+2 A:

With SQL 2008 you have the new data time DATETIMEOFFSET which has time zone awareness as offset (as opposed to as timezone name). With this datatype you can get the UTC time of any value and then you can render it properly according to the user timezone (which is a transformation that belongs in the PHP code). The other option is to just store UTC always, in the old DATETIME data type. Whatever you do, the translation from user local to UTC and from UTC to user local belongs on the rendering in PHP, not to the SQL so DATEADD has not place in this code. The PHP DateTime has timezone support which is daylight savings aware.

Remus Rusanu 2010-09-02 22:17:02

Thanks for the info! Could you please elaborate more on the use of DateTime in PHP, and what parameters should I pass it to be aware that I want daylight savings? Say I have EST as my user's default timezone, and the database is DATETIMEOFFSET (UTC). What should I do now to:1) read the date from UTC to EST2) write the date from EST to UTCAlso, do I need to set anything on the server side to define the server timezone? (eg. a PHP system constant?)Thanks in advance!

johnshaddad 2010-09-03 03:01:55

ansaurus

tags:

views:

answers:

Timezone Daylight Saving problem in PHP and SQL

related questions