mysql 夏令时,在MySql中检测日期是否处于夏令时

I have inherited a legacy application where all the dates and times are stored in the local timezone (UK). I am not in a position to change how these are stored.

However, the requirement is to display all the dates in GMT within the app. Therefore when I retrieve a list of events from the database I need it to display them all in this time format whilst observing if daylight saving is in operation for each particular event date. Using the following logic I can determine if daylight saving is active within the query:

IF(CAST(TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS SIGNED) >0,

DATE_FORMAT(CONVERT_TZ(ADDTIME(`event_date`, IFNULL(`event_time`, '00:00')), '+01:00', '+00:00'), '%H:%i'), `event_time`) AS event_time

This however is obviously only checking the date that it is being run at. So any events in the future that cross the daylight saving boundaries don't work.

Is there a way I can detect if a given date is in DST within the mysql query itself?

Any help, much appreciated.

解决方案

You're in UK. So, your TZ should be CET or CEST (Central Europe Summer Time). You can get the info out this way:

mysql> SELECT @@system_time_zone;

+--------------------+

| @@system_time_zone |

+--------------------+

| CEST |

+--------------------+

I use this in many of my Stored Procedures. Note: I need both forms of TZ info, whether I need to compute offsets with or without DST being applied.

CASE @@system_time_zone

WHEN 'CET' THEN SET l_tz = 'Europe/Paris'; SET l_tzOff = '+1:00';

WHEN 'CEST' THEN SET l_tz = 'Europe/Paris'; SET l_tzOff = '+1:00';

WHEN 'SGT' THEN SET l_tz = 'Asia/Singapore'; SET l_tzOff = '+8:00';

ELSE SET l_tz = 'Europe/Paris'; SET l_tzOff = '+1:00';

END CASE;

You can get some inspiration from this.