sql - Datatype/structure to store timezone offset in MySQL

Question

Which would be the proper datatype/structure to store timezone offsets in MySQL? I just want to store the numeric value (the city and country are obviously stored in other columns).

Examples:

• -5:00 Guayaquil, ECU
• -4:30 Caracas, VEN
• 0:00 Some city
• 2:00 Bonn, GER

Answer 1

You should use TIME. It's the right data type for the task: you have formatting and calculations are available. Moreover, according to the docs, TIME is also supposed to be used as a result of differences between two moments, which is what Timezones are in fact.

From the docs:

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).