在 Oracle 中，如何检测夏令时开始/结束的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/287563/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Oracle, how can I detect the date on which daylight savings time begins / ends?
提问by JosephStyons
Is there a way in Oracle to select the date on which daylight savings will switch over for my locale?
Oracle 中是否可以选择为我的语言环境切换夏令时的日期？
Something vaguely equivalent to this would be nice:
模糊地等同于这个的东西会很好：
SELECT CHANGEOVER_DATE
FROM SOME_SYSTEM_TABLE
WHERE DATE_TYPE = 'DAYLIGHT_SAVINGS_CHANGEOVER'
AND TO_CHAR(CHANGEOVER_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY'); -- in the current year
Edit: I was hoping for a solution that would not require changes when Congress adjusts DST laws, as they did in 2007. The posted solutions will work, though.
编辑：我希望有一个解决方案，在国会调整夏令时法律时不需要更改，就像他们在 2007 年所做的那样。不过，发布的解决方案将起作用。
采纳答案by Leigh Riffel
We use the following two functions to calculate the start and end dates for any given year (post 2007, US).
我们使用以下两个函数来计算任何给定年份（2007 年后，美国）的开始和结束日期。
Function DaylightSavingTimeStart (p_Date IN Date)
Return Date Is
v_Date Date;
v_LoopIndex Integer;
Begin
--Set the date to the 8th day of March which will effectively skip the first Sunday.
v_Date := to_date('03/08/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
--Advance to the second Sunday.
FOR v_LoopIndex IN 0..6 LOOP
If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
Return v_Date + v_LoopIndex;
End If;
END LOOP;
End;
Function DaylightSavingTimeEnd (p_Date IN Date)
Return Date Is
v_Date Date;
v_LoopIndex Integer;
Begin
--Set Date to the first of November this year
v_Date := to_date('11/01/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
--Advance to the first Sunday
FOR v_LoopIndex IN 0..6 LOOP
If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
Return v_Date + v_LoopIndex;
End If;
END LOOP;
End;
There is probably a simpler way to do it, but these have worked for us. Of course this query doesn't know whether daylight saving time is observed for where you are. For that you will need location data.
可能有一种更简单的方法来做到这一点，但这些方法对我们有用。当然，此查询不知道您所在的位置是否遵守夏令时。为此，您将需要位置数据。
回答by Reimius
To improve on Leigh Riffel's answer, this is much simpler with the same logic:
为了改进 Leigh Riffel 的答案，使用相同的逻辑要简单得多：
Function DaylightSavingTimeStart (p_Date IN Date)
Return Date Is
Begin
Return NEXT_DAY(TO_DATE(to_char(p_Date,'YYYY') || '/03/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') + 7;
End;
Function DaylightSavingTimeEnd (p_Date IN Date)
Return Date Is
Begin
Return NEXT_DAY(TO_DATE(to_char(p_Date,'YYYY') || '/11/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN');
End;
回答by Rahul Bhawsar
Instead of looping to get the next sunday you can also use the next_day(date, 'SUN') function of oracle.
除了循环获取下一个星期日之外，您还可以使用 oracle 的 next_day(date, 'SUN') 函数。
回答by Leigh Riffel
Here is a way to use Oracles internal knowledge of whether a timezone observes daylight saving time or not to determine the start and end of it. Aside from the complexity and general strangeness of it, it requires two timezones to be know have identical times when daylight saving time is not in effect and different times when it is. As such it is resilient to congressional changes in when daylight saving time occurs (assuming your database is up to date with the patches), but is not resilient to regional changes effecting the timezones keyed off of. With those warnings, here is what I have.
这是一种使用 Oracle 内部关于时区是否遵守夏令时的知识来确定它的开始和结束的方法。除了它的复杂性和一般陌生之外，它需要知道两个时区在夏令时不生效时具有相同的时间，而在夏令时生效时具有不同的时间。因此，它可以适应夏令时发生时的国会变化（假设您的数据库是最新的补丁），但不能适应影响关闭时区的区域变化。有了这些警告，这就是我所拥有的。
ALTER SESSION SET time_zone='America/Phoenix';
DROP TABLE TimeDifferences;
CREATE TABLE TimeDifferences(LocalTimeZone TIMESTAMP(0) WITH LOCAL TIME ZONE);
INSERT INTO TimeDifferences
(
SELECT to_date('01/01/' || to_char(sysdate-365,'YYYY') || '12:00:00','MM/DD/YYYYHH24:MI:SS')+rownum-1
FROM dual CONNECT BY rownum<=365
);
COMMIT;
ALTER SESSION SET time_zone='America/Edmonton';
SELECT LocalTimeZone-1 DaylightSavingTimeStartAndEnd
FROM
(
SELECT LocalTimeZone,
to_char(LocalTimeZone,'HH24') Hour1,
LEAD(to_char(LocalTimeZone,'HH24')) OVER (ORDER BY LocalTimeZone) Hour2
FROM TimeDifferences
)
WHERE Hour1 <> Hour2;
I told you it was strange. The code only figures out the day of the change, but could be enhanced to show the hour. Currently it returns 09-MAR-08 and 02-NOV-08. It is also sensitive to the time of year it is run, which is why I had to do the -365...+365. All in all I don't recommend this solution, but it was fun to investigate. Maybe someone else has something better.
我告诉过你这很奇怪。该代码仅计算出更改的日期，但可以增强以显示小时。目前它返回 09-MAR-08 和 02-NOV-08。它也对它运行的一年中的时间很敏感，这就是为什么我必须做 -365...+365。总而言之，我不推荐此解决方案，但调查起来很有趣。也许别人有更好的东西。
回答by m0j0
In the United States, Daylight Savings Time is defined as beginning on the second Sunday in March, and ending on the first Sunday in November, for the areas that observe DST, for years after 2007.
在美国，夏令时被定义为从 3 月的第二个星期日开始，到 11 月的第一个星期日结束，对于遵守夏令时的地区，在 2007 年之后的几年里。
I don't think there's an easy way to get this information from Oracle, but based on the standard definition, you should be able to write a stored procedure that calculates the beginning and ending date using the Doomsday Algorithm.
我不认为有一种简单的方法可以从 Oracle 获取此信息，但是根据标准定义，您应该能够编写一个使用Doomsday Algorithm计算开始和结束日期的存储过程。
回答by Alon Gingold
Here's my version of the above. It's advantage is that it does not need a second 'alter session set time zone', and can be used more easily from an application. You create the stored function, and then you simply use: ALTER SESSION SET time_zone='Asia/Jerusalem'; select GetDSTDates(2012,1) DSTStart,GetDSTDates(2012,2) DSTEnd,SessionTimeZone TZ from dual;
这是我上面的版本。它的优点是它不需要第二个“更改会话设置时区”，并且可以更轻松地从应用程序中使用。您创建存储的函数，然后您只需使用： ALTER SESSION SET time_zone='Asia/Jerusalem'; 选择 GetDSTDates(2012,1) DSTStart,GetDSTDates(2012,2) DSTEnd,SessionTimeZone TZ from dual;
which will return the dst start date,dst end date, timezone for the specified year.
这将返回指定年份的 dst 开始日期、dst 结束日期和时区。
create or replace function GetDSTDates
(
year integer,
GetFrom integer
)
return Date
as
cursor c is
select 12-to_number(to_char(LocalTimeZone at time zone '+00:00','HH24')) offset,
min(to_char(LocalTimeZone at time zone '+00:00','DD/MM/YYYY')) fromdate,
max(to_char(LocalTimeZone at time zone '+00:00','DD/MM/YYYY')) todate
from (
SELECT cast((to_date('01/01/'||to_char(year)||'12:00:00','MM/DD/YYYYHH24:MI:SS')+rownum-1) as timestamp with local time zone) LocalTimeZone
FROM dual CONNECT BY rownum<=365
)
group by 12-to_number(to_char(LocalTimeZone at time zone '+00:00','HH24'));
dstoffset integer;
offset integer;
dstfrom date;
dstto date;
begin
offset := 999;
dstoffset := -999;
for rec in c
loop
if rec.offset<offset
then
offset := rec.offset;
end if;
if rec.offset>dstoffset
then
dstoffset := rec.offset;
dstfrom := to_date(rec.fromdate,'DD/MM/YYYY');
dstto :=to_date(rec.todate,'DD/MM/YYYY');
end if;
end loop;
if (offset<999 and dstoffset>-999 and offset<>dstoffset)
then
if GetFrom=1
then
return dstfrom;
else
return dstto;
end if;
else
return null;
end if;
end;
/
ALTER SESSION SET time_zone='Asia/Jerusalem';
select GetDSTDates(2012,1) DSTStart,
GetDSTDates(2012,2) DSTEnd,
SessionTimeZone TZ from dual;
