SQL Query - get current timezones

  • Status: Closed
  • Prize: $10
  • Entries Received: 4
  • Winner: josemanuelg

Contest Brief

Attached are MySQL's timezone files. I need to get a list of all the time zones and their current (this means you need to have a "> NOW() logic") offsets. Probably need these columns in the results:

`time_zone_transition`.`time_zone_id`,
`time_zone_name`.`Name`
`time_zone_transition_type`.`Abbreviation`
`time_zone_transition_type`.`Is_DST`
`time_zone_transition_type`.`Offset` # output this as +/- HH:MM

NOTES:
- `transition_time` is a Unix timestamp indicating when that time zone is scheduled to go on or off daylight savings time
- `Offset` is the time zone offset in seconds, which needs to be divided it into 3600 in order to get offset in hours / minutes

First to solve with the fastest execution time wins -- and also can handle a few more tasks for me.

Recommended Skills

Top entries from this contest

View More Entries

Public Clarification Board

  • kpaul08
    kpaul08
    • 2 months ago

    There are multiple abbreviations for a single time zone like LMT, GMT, EAT and so on. Is there any logic to pick one? or if I pick anyone randomly that is fine for you? Please let me know. Also not able to understand the > NOW() logic. If you can explain that a bit it will be really helpful.

    • 2 months ago
  • abdulrazakZakieh
    abdulrazakZakieh
    • 2 months ago

    Dear contest holder,
    I have submitted my entry #1 . However, since the fields I have to select are not related to transition table I did not use the transition table at all.
    If my job is not correct, please tell me what is the required exactly from the transition table.

    • 2 months ago

How to get started with contests

  • Post your contest

    Post Your Contest Quick and easy

  • Get tons of entries

    Get Tons of Entries From around the world

  • Award the best entry

    Award the best entry Download the files - Easy!

Post a Contest Now or Join us Today!