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.