带有夏时制的时间戳计算 [英] Timestamp calculation with daylight saving time
问题描述
中欧夏令时开始于3月的最后一个星期日.我们将时钟设置为02:00到03:00.如果我在数据库请求中执行时间戳计算-可以在01:59说什么?
The Central European Daylight Saving Time begins on last Sunday in March. We set our clocks from 02:00 to 03:00. What happens if I do timestamp calcuations in a database request - lets say at 01:59?
UPDATE sessions SET aliveuntil = (CURRENT_TIMESTAMP + INTERVAL '1' MINUTE) WHERE id = ?
结果是03:00还是02:00?
Do I get 03:00 as result or 02:00?
如果我们将时钟设置为03:00到02:00,该怎么办?
And what about the over way around if we set our clocks from 03:00 to 02:00?
SELECT id FROM sessions WHERE aliveuntil < (CURRENT_TIMESTAMP - INTERVAL '1' MINUTE)
时间从03:00更改为02:00 ... (CURRENT_TIMESTAMP - INTERVAL '1' MINUTE)
在02:00会发生什么?是02:59还是01:59?
After time changed from 03:00 to 02:00... what happens with the (CURRENT_TIMESTAMP - INTERVAL '1' MINUTE)
at 02:00? Is it 02:59 or 01:59?
应如何处理?最佳实践以及Oracle Database 11g 11.2.0.2.0版如何处理(在我的特定情况下)?
How should this be handled? Best practice and how it's handle (in my particular case) by Oracle Database 11g Release 11.2.0.2.0?
推荐答案
如果我正确地理解了他们的文档,这取决于在数据库中如何设置表/列.如果将这些列设置为使用WITH TIME ZONE,则Oracle自动确定正确/相关的值.在上面的示例中,如果aliveuntil列具有此设置,那么如果您尝试在1:59处添加1分钟,则时间将更新为3:00.
If I'm understanding their documentation correctly, it depends on how the table/columns are setup in the database. If the columns are setup to use WITH TIME ZONE, then Oracle automatically determines the correct/relevant values. In your above example if the column aliveuntil has this setting, then if you try to add 1 minute at 1:59, the time will update to 3:00.
这是我找到的有关该主题的有用文章:
Here is a helpful article I found on the subject:
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
向下滚动到文章底部,您应该看到所要查找的内容.
Scroll down to the bottom of the article and you should see what you're looking for.
这是我发现相关的文章的一部分:
Here was a section of the article I found relevant:
例如,在美国东部地区,时间 当夏令时从上午01:59:59更改为上午3:00:00 生效.上午02:00:00和02:59:59之间的间隔 不存在.该时间间隔内的值无效.
For example, in the Eastern region of the United States, the time changes from 01:59:59 a.m. to 3:00:00 a.m. when Daylight Saving Time goes into effect. The interval between 02:00:00 and 02:59:59 a.m. does not exist. Values in that interval are invalid.
这篇关于带有夏时制的时间戳计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!