You are here

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 mathematical calculations is the application code and not the database.

But nevertheless 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.

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 summer time...

If you have smarter solutions please let me know.

Literature

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

Comments

[http://www.blogger.com/profile/12606038649636876830 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
arathorn2005comment

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said... Hi Björn, Thanks a lot for you post. Much better than mine! :) Shinguz
Shinguzcomment