插入时间与时区夏令时 [英] Insert time with timezone daylight savings
问题描述
我想在包含时区的postgresql中插入时间数据类型,并且意识到夏时制。这是我所做的:
CREATE TABLE mytable(
...
start_time time(0 )with time zone,
end_time time(0)with time zone
)
INSERT INTO mytable(start_time,end_time)
VALUES(TIME '08:00: 00 MST7MDT',TIME '18:00:00 MST7MDT')
我收到以下错误: / p>
类型时间的无效输入语法:08:00:00 MST7MDT
如果我使用MST而不是MST7MDT,但是我需要它来了解DST。我也尝试使用'America / Edmonton'作为时区,但是我也有同样的错误。
使用时区和DST插入时间值(不是时间戳)的正确方式是什么?
编辑:
我实际上想使用'美国/埃德蒙顿'语法
正确的 方式是不要使用时区(注意 time之间的空格
和 zone
),因为它被设计打破了。它是在SQL标准,所以Postgres支持类型 - 但建议不使用它。更多相关答案:
在选择预定项目时会计Postgres中的DST
由于您在使用 DST , timetz
(简称)是一个特别糟糕的选择。没有办法处理DST。在$或code code code code code code
$ b
使用 带时区的时间戳记
( timstamptz
)。您可以随时丢弃日期部分。只需使用 start_time :: time
从 timestamptz
获取本地时间。或者在时区使用
转换到您的时区。
通常,要自动将DST纳入帐户,请使用时区名称 ,而不是时区缩写。这个相关问题的更多解释答案:
具有相同属性的时区名称在应用于时间戳时会产生不同的结果
在特定情况下,您可能会使用 America / Los_Angeles
(具有 timestamptz
的示例):
INSERT INTO mytable(start_time,end_time)
VALUES('1970-01-01 08:00:00 America / Los_Angeles'
,'1970-01-01 18:00:00 America / Los_Angeles' )
我发现这是通过检查:
SELECT * FROM pg_timezone_names
WHERE utc_offset ='-07:00'
AND is_dst;
有关时区处理的基础知识:
确定Rails和PostgreSQL中的时区
I would like to insert time data type in postgresql that includes the timezone and is aware of daylight savings time. This is what I have done:
CREATE TABLE mytable(
...
start_time time(0) with time zone,
end_time time(0) with time zone
)
INSERT INTO mytable(start_time, end_time)
VALUES(TIME '08:00:00 MST7MDT', TIME '18:00:00 MST7MDT')
I get the following error:
invalid input syntax for type time: "08:00:00 MST7MDT"
It works if I use 'MST' instead of 'MST7MDT', but I need it to be aware of DST. I also tried using 'America/Edmonton' as the timezone, but I got the same error.
What is the proper way to insert a time value (not timestamp) with timezone and DST?
EDIT: I would actually like to use the 'America/Edmonton' syntax
The proper way is not to use time with time zone
(note the space between time
and zone
) at all, since it is broken by design. It is in the SQL standard, so Postgres supports the type - but advises not to use it. More in this related answer:
Accounting for DST in Postgres, when selecting scheduled items
Since you are having problems with DST, timetz
(short name) is a particularly bad choice. It is ill-equipped to deal with DST. It's impossible to tell whether 8:00:00
is in winter or summer time.
Use timestamp with time zone
(timstamptz
) instead. You can always discard the date part. Simply use start_time::time
to get the local time from a timestamptz
. Or use AT TIME ZONE
to transpose to your time zone.
Generally, to take DST into account automatically, use a time zone name instead of a time zone abbreviation. More explanation in this related question & answer:
Time zone names with identical properties yield different result when applied to timestamp
In your particular case, you could probably use America/Los_Angeles
(example with timestamptz
):
INSERT INTO mytable(start_time, end_time)
VALUES('1970-01-01 08:00:00 America/Los_Angeles'
, '1970-01-01 18:00:00 America/Los_Angeles')
I found this by checking:
SELECT * FROM pg_timezone_names
WHERE utc_offset = '-07:00'
AND is_dst;
Basics about time zone handling:
Ignoring timezones altogether in Rails and PostgreSQL
这篇关于插入时间与时区夏令时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!