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 i开发者_开发百科t 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!

---

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.

---

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.