在 MySQL 中存储时区偏移的数据类型/结构

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

示例:

  • -5:00 瓜亚基尔,ECU
  • -4:30 加拉加斯,威尼斯
  • 0:00 某个城市
  • 2:00 波恩,德国

推荐答案

你应该使用 TIME.这是任务的正确数据类型:您有格式和 计算 可用.此外,根据文档, TIME 也应该被用作两个时刻之间差异的结果,这实际上是 Timezones.

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.

来自文档:

MySQL 以HH:MM:SS"格式(或'HHH:MM:SS' 格式用于大小时值).TIME 值的范围可以从-838:59:59"到838:59:59".小时部分可能如此之大,因为TIME 类型不仅可以用来表示一天中的某个时间(它必须少于 24 小时),但也包括经过的时间或时间间隔两个事件之间(可能远大于 24 小时,甚至阴性).

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

相关文章