在MySql中检测日期是否处于夏令时 [英] Detect if a date is in Daylight saving time in MySql

问题描述

我继承了一个遗留应用程序，其中所有日期和时间都存储在本地时区(UK)中.我无权更改这些存储方式.

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.

但是，要求是在应用内以GMT显示所有日期.因此，当我从数据库中检索事件列表时，我需要它以这种时间格式显示所有事件，同时观察每个特定事件日期的夏令时是否处于运行状态.使用以下逻辑，我可以确定查询中的夏令时是否处于活动状态:

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.

有没有一种方法可以检测给定日期是否在mysql查询本身的DST中?

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

任何帮助，不胜感激.

Any help, much appreciated.

推荐答案

您在英国.因此，您的TZ应该是CET或CEST(中欧夏季时间).您可以通过以下方式获取信息:

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               |
+--------------------+

我在许多存储过程中都使用了它.注意:无论需要在应用DST还是不应用DST的情况下计算偏移量，我都需要两种形式的TZ信息.

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.

这篇关于在MySql中检测日期是否处于夏令时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！