summertime question

[ojorus]

Hi!
A simple question maybe...

I have a datetime column in a table, and want to automaticly insert
local (Norwegian) time into the column. (I use PHP 5) I can not use
NOW() since the mysql server is set up with a different time zone. So
now i use UTC_TIMESTAMP() + INTERVAL 2 HOUR, which give me the right
time now. BUT a problem will occur some time in october, when we end
summer time, and Norway will go back to UTC + 0100.
Of course I could manually go into my PHP code and change the query to
.... UTC_TIMESTAMP() + INTERVAL 1 HOUR ..., but I was hoping there was
some easier way to to this. (I don't want to change my code twice a
year...).

One solution is of course to tell my PHP code when summer time starts
and ends for the next say ten years, and then vary the INTERVAL number
from 1 to 2 or the other way. But still I was hoping there was some
easier ways...

Any suggestions?

[Jonathan]

ojorus wrote:

Hi!
A simple question maybe...

I have a datetime column in a table, and want to automaticly insert
local (Norwegian) time into the column. (I use PHP 5) I can not use
NOW() since the mysql server is set up with a different time zone. So
now i use UTC_TIMESTAMP() + INTERVAL 2 HOUR, which give me the right
time now. BUT a problem will occur some time in october, when we end
summer time, and Norway will go back to UTC + 0100.
Of course I could manually go into my PHP code and change the query to
... UTC_TIMESTAMP() + INTERVAL 1 HOUR ..., but I was hoping there was
some easier way to to this. (I don't want to change my code twice a
year...).

One solution is of course to tell my PHP code when summer time starts
and ends for the next say ten years, and then vary the INTERVAL number
from 1 to 2 or the other way. But still I was hoping there was some
easier ways...

Any suggestions?

Did you have a look at the MySQL CONVERT_TZ() function:
http://dev.mysql.com/doc/refman/5.0/...functions.html

If that does not work you could perhaps use use the time of your MySQL
server and time stamp using NOW() and determine the offset in php?

You can make a date of the time stamp value, determine if it is daylight
saving time or not and add the necessary offset depending on the
outcome. You can use the php date() function (http://www.php.net/date)
with the I (capital i) option to determine if the given date is a
daylight saving time. This way you do not need to recode your program,
you simply make it decide for it self.

Jonathan