CODEWITHSUNDEEP
mysqlsqldatabase-designtimezonesqldatatypes
Andres SK
Andres SK

Reputation: 10974

Datatype/structure to store timezone offset in MySQL

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:

Upvotes: 7

Views: 3141

Answers (1)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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).

Upvotes: 5

Related Questions