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 w开发者_运维知识库ork.

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

Any help, much appreciated.

Timezones are not stored in DATETIME values. Interestingly, they are for TIMESTAMPs.

Given a stored date, you can posthumously figure out if DST was "on" at that time based on the local rules. Since you can't change the dates, I would guess that you can't add a column to store the timezone...

Make a stored procedure that contains the rules and converts a given date to GMT.

Note that only the people that live around Greenwich want their times displayed in GMT. :)

Good luck.

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.

As an alternative, here are some functions to determine whether the North American Daylight Savings rules are in effect.

As of 2007* the rule in North America has been that Daylight Savings (DST):

starts at 2am (local time) on the 2nd Sunday of March
ends at 2am (local time) on the 1st Sunday of November

*_{(From 1987 to 2006 it was the April's 1st Sunday to October's last Sunday.)}

MySQL Functions:

DELIMITER // CREATE FUNCTION DSTstart(dt DATETIME) RETURNS DATETIME DETERMINISTIC BEGIN 
RETURN cast(concat(year(now()),'-3-',(14-WEEKDAY(concat(year(now()),'-3-1'))),' 2:00') as datetime);
END// DELIMITER ;

DELIMITER // CREATE FUNCTION DSTstop(dt DATETIME) RETURNS DATETIME DETERMINISTIC BEGIN 
RETURN cast(concat(year(now()),'-11-',(7-WEEKDAY(concat(year(now()),'-11-1'))),' 2:00') as datetime);
END// DELIMITER ;

DELIMITER // CREATE FUNCTION isDST(dt DATETIME) RETURNS BIT DETERMINISTIC BEGIN 
RETURN (dt>=cast(concat(year(now()),'-3-',(14-WEEKDAY(concat(year(now()),'-3-1'))),' 2:00') as datetime))
AND (dt<cast(concat(year(now()),'-11-',(7-WEEKDAY(concat(year(now()),'-11-1'))),' 2:00') as datetime));
END// DELIMITER ;

Just for reference I'll include the VBA and Excel Worksheet versions of the functions:

Excel/Access/etc VBA Functions:

Function DSTstart(dt As Date) As Date 
  DSTstart = CDate(Year(dt) & "-3-" & (15 - Weekday(CDate(Year(dt) & "-3-1"), 2)) & " 2:00")
End Function

Function DSTstop(dt As Date) As Date 
  DSTstop = CDate(Year(dt) & "-11-" & (8 - Weekday(CDate(Year(dt) & "-11-1"), 2)) & " 2:00")
End Function

Function isDST(dt As Date) As Boolean
  isDST = (dt >= CDate(Year(dt) & "-3-" & (15 - Weekday(CDate(Year(dt) & "-3-1"), 2)) & " 2:00") _
          And dt < CDate(Year(dt) & "-11-" & (8 - Weekday(CDate(Year(dt) & "-11-1"), 2)) & " 2:00"))
End Function

Excel Worksheet Functions:

DST Start: (returns the date/time that DST begins in the year of the date in [A1])

=DATE(YEAR(A1),3,15-WEEKDAY(DATE(YEAR(A1),3,1),2))+TIMEVALUE("2:00")

DST End: (returns the date/time that DST begins in the year of the date in [A1])

=DATE(YEAR(A1),11,8-WEEKDAY(DATE(YEAR(A1),11,1),2))+TIMEVALUE("2:00")

Is DST?: (returns TRUE if DST is in effect for the date in [A1])

=AND(A1>=DATE(YEAR(A1),3,15-WEEKDAY(DATE(YEAR(A1),3,1),2))+TIMEVALUE("2:00"),A1<DATE(YEAR(A1),11,8-WEEKDAY(DATE(YEAR(A1),11,1),2))+TIMEVALUE("2:00"))

found an ugly complicated way, @DT is input Date&Time

Set @DT=20150329020304; # -> 0

Set @DT=20150329030304; # -> 1(-0.0511)

Set @DT=20150329040304; # -> 1(-1)

Select 0!=(UNIX_TIMESTAMP(@DT)-   UNIX_TIMESTAMP(Concat(Year(@DT),'0101',Time_Format(@DT,'%H%i%s')))-DateDiff(@DT,Concat(Year(@DT),'0101',Time_Format(@DT,'%H%i%s')))*86400)/3600 as DlsIsOn