什么是存储时区的适当数据类型？ [英] What is an appropriate data type to store a timezone?

问题描述

我正在考虑使用格式为+ hh：mm（或-hh：mm）的字符串。这是必需的吗？

注意：我不需要存储日期或时间，只是时区。

解决方案

不幸的是PostgreSQL不提供时区数据类型，所以你应该使用文本 p>

间隔似乎是一个逻辑选项乍一看，它 适合某些用途。然而，它不能考虑夏令时，也不考虑相同UTC偏移量中的不同区域具有不同的DST规则的事实。

没有从UTC偏移到时区的1：1映射。

例如，时间段澳大利亚/悉尼（新南威尔斯州）是 UTC + 10 （ EST ）或 UTC + 11 （ EDT ）。是的，这是美国使用的相同的首字母缩写 EST 时区首字母缩略词在tzdata数据库中是不唯一的，这就是为什么Pg具有 timezone_abbreviations 设置的原因。更糟糕的是，布里斯班（昆士兰州）几乎相同，在 UTC + 10 EST ...但没有夏令时，所以有时它是一个<$在新南威尔斯州的DST期间，c $ c> -1 抵消新南威尔士。

（更新：最近澳大利亚采用 A 前缀，因此它使用 AEST 作为其东部州TZ首字母缩略词，但 EST 和 WST 仍然常用）

如果您需要存储的全部是UTC偏移量，那么间隔是适当的。如果要存储时区，请将其存储为 text 。这是一个痛苦的验证和转换为时区偏移在这一刻，但至少它应付DST。

I'm thinking of simply using a string in the format "+hh:mm" (or "-hh:mm"). Is this both necessary and sufficient?

Note: I don't need to store the date or the time, just the timezone.

解决方案

Unfortunately PostgreSQL doesn't offer a time zone data type, so you should probably use text.

interval seems like a logical option at first glance, and it is appropriate for some uses. However, it fails to consider daylight savings time, nor does it consider the fact that different regions in the same UTC offset have different DST rules.

There is not a 1:1 mapping from UTC offset back to time zone.

For example, the time zone for Australia/Sydney (New South Wales) is UTC+10 (EST), or UTC+11 (EDT) during daylight savings time. Yes, that's the same acronym EST that the USA uses; time zone acronyms are non-unique in the tzdata database, which is why Pg has the timezone_abbreviations setting. Worse, Brisbane (Queensland) is at almost the same longditude and is in UTC+10 EST ... but doesn't have daylight savings, so sometime it's at a -1 offset to New South Wales during NSW's DST.

(Update: More recently Australia adopted an A prefix, so it uses AEST as its eastern states TZ acronym, but EST and WST remain in common use).

Confusing much?

If all you need to store is a UTC offset then an interval is appropriate. If you want to store a time zone, store it as text. It's a pain to validate and to convert to a time zone offset at the moment, but at least it copes with DST.

这篇关于什么是存储时区的适当数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！