在 MySQL 中存储时区偏移的数据类型/结构 [英] Datatype/structure to store timezone offset in 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).

这篇关于在 MySQL 中存储时区偏移的数据类型/结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！