Menu
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:
382
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME .) By default, the current time zone for each connection is the server's time.
The date and time data types for representing temporal values are DATE , TIME , DATETIME , TIMESTAMP , and YEAR . Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent.
To explicitly specify the system time zone for MySQL Server at startup, set the TZ environment variable before you start mysqld. If you start the server using mysqld_safe, its --timezone option provides another way to set the system time zone. The permissible values for TZ and --timezone are system dependent.
The zone offset can be Z for UTC or it can be a value "+" or "-" from UTC. For example, the value 08:00-08:00 represents 8:00 AM in a time zone 8 hours behind UTC, which is the equivalent of 16:00Z (8:00 plus eight hours). The value 08:00+08:00 represents the opposite increment, or midnight (08:00 minus eight hours).
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).
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
