MySQL:如何为所有时区选择 UTC 偏移量和 DST? [英] MySQL: How to select the UTC offset and DST for all timezones?

问题描述

我想要mysql时区表中所有时区的列表，需要选择:

I want a list of all timezones in the mysql timezone tables, and need to select:

1) 他们当前与 GMT 的偏移量
2) 该时区是否使用 DST(不是当前是否正在使用，只是该时区在一年中的某个时间点是否考虑 DST)

1) Their current offset from GMT
2) Whether DST is used by that timezone (not whether it's currently in use, just whether DST is considered at some point in the year for that timezone)

原因:我需要构建一个 Web 表单并将用户时区信息(我可以从 javascript 生成)与存储在 mysql 数据库中的正确时区进行匹配.我可以找到 UTC 偏移量并从 javascript 函数中获取 DST 标志.

Reason: I need to build a web form and match the users time zone information (which I can generate from javascript) to the correct time zone stored in the mysql DB. I can find UTC offset and get a DST flag from javascript functions.

推荐答案

试试这个查询.偏移时间是 (Offset/60/60)

Try this query. The offsettime is the (Offset / 60 / 60)

SELECT tzname.`Time_zone_id`,(`Offset`/60/60) AS `offsettime`,`Is_DST`,`Name`,`Transition_type_id`,`Abbreviation`
FROM `time_zone_transition_type` AS `transition`, `time_zone_name` AS `tzname`
WHERE transition.`Time_zone_id`=tzname.`Time_zone_id`
ORDER BY transition.`Offset` ASC;

结果是

501 -12.00000000    0   0   PHOT    Pacific/Enderbury
369 -12.00000000    0   0   GMT+12  Etc/GMT+12
513 -12.00000000    0   1   KWAT    Pacific/Kwajalein
483 -12.00000000    0   1   KWAT    Kwajalein
518 -11.50000000    0   1   NUT Pacific/Niue
496 -11.50000000    0   1   SAMT    Pacific/Apia
528 -11.50000000    0   1   SAMT    Pacific/Samoa
555 -11.50000000    0   1   SAMT    US/Samoa
521 -11.50000000    0   1   SAMT    Pacific/Pago_Pago
496 -11.44888889    0   0   LMT Pacific/Apia
528 -11.38000000    0   0   LMT Pacific/Samoa
555 -11.38000000    0   0   LMT US/Samoa
521 -11.38000000    0   0   LMT Pacific/Pago_Pago
518 -11.33333333    0   0   NUT Pacific/Niue
544 -11.00000000    0   3   BST US/Aleutian
163 -11.00000000    0   3   BST America/Nome
518 -11.00000000    0   2   NUT Pacific/Niue
496 -11.00000000    0   2   WST Pacific/Apia
544 -11.00000000    0   0   NST US/Aleutian
163 -11.00000000    0   0   NST America/Nome
528 -11.00000000    0   4   SST Pacific/Samoa
528 -11.00000000    0   3   BST Pacific/Samoa

这篇关于MySQL:如何为所有时区选择 UTC 偏移量和 DST?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！