Mysql: How To Select The Utc Offset And Dst For All Timezones?

I want a list of all timezones in the mysql timezone tables, and need to select: 1) Their current offset from GMT 2) Whether DST is used by that timezone (not whether it's currentl

Solution 1:

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;

The results are

501-12.0000000000   PHOT    Pacific/Enderbury
369-12.0000000000   GMT+12  Etc/GMT+12513-12.0000000001   KWAT    Pacific/Kwajalein
483-12.0000000001   KWAT    Kwajalein
518-11.5000000001   NUT Pacific/Niue
496-11.5000000001   SAMT    Pacific/Apia
528-11.5000000001   SAMT    Pacific/Samoa
555-11.5000000001   SAMT    US/Samoa
521-11.5000000001   SAMT    Pacific/Pago_Pago
496-11.4488888900   LMT Pacific/Apia
528-11.3800000000   LMT Pacific/Samoa
555-11.3800000000   LMT US/Samoa
521-11.3800000000   LMT Pacific/Pago_Pago
518-11.3333333300   NUT Pacific/Niue
544-11.0000000003   BST US/Aleutian
163-11.0000000003   BST America/Nome
518-11.0000000002   NUT Pacific/Niue
496-11.0000000002   WST Pacific/Apia
544-11.0000000000NST US/Aleutian
163-11.0000000000NST America/Nome
528-11.0000000004   SST Pacific/Samoa
528-11.0000000003   BST Pacific/Samoa