Friday, October 2, 2009

Determine in MySQL if we are in summer time or winter time (daylight saving time, DST)

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such math calculations is the application code and not the database.

But never the less I was interested in how to solve this IN the database.

By default your MySQL server relies on your servers time zone. [1]

So if your server is set-up correctly you should be capable to determine if you are in summer time or winter time by your current time, UTC time and the offset you have to UTC.
mysql> SELECT IF(ROUND(TIME_TO_SEC(SUBTIME(TIME(SYSDATE()), UTC_TIME())) / 3600, 0) = 2, 'summer time', 'winter time') AS time;
Have fun calculating how much power is produced by your solar panels according to winter or sumer time...

If you have smarter solutions please let me know.

[1] Time zone support
[2] Date and time functions

2 comments:

arathorn2005 said...

Nice approach!

Try this:

SELECT
IF(TIMESTAMPDIFF(HOUR , UTC_TIMESTAMP(), SYSDATE()) = 2,
'summer time',
'winter time')
AS TIME;

The keywords are longer, but all in all this maybe slightly easier to read.

Björn

Shinguz said...

Hi Björn,

Thanks a lot for you post. Much better than mine! :)

Shinguz