Home » SQL & PL/SQL » SQL & PL/SQL » Regarding NEW_TIME (Oracle 10.2.0.3)
Regarding NEW_TIME [message #328422] |
Fri, 20 June 2008 02:29 |
kssarayu
Messages: 18 Registered: March 2007
|
Junior Member |
|
|
Hi,
i want to use NEW_TIME function to convert GMT to EST or EDT depending on day light saving time.
I want to know whether i can use NEW_TIME(sysdate,'GMT','EST') always irrespective of the day of the year or do i have to change 3rd arugment from EST to EDT depending on the day of the year.
Thank you
Sarayu K.S.
|
|
|
Re: Regarding NEW_TIME [message #328620 is a reply to message #328422] |
Fri, 20 June 2008 17:51 |
|
Barbara Boehmer
Messages: 9100 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you use new_time, then yes, you need to specify est or edt. However, if you use from_tz and the long time zone name, instead of the short abbreviated name, it will figure whether it is standard time or daylight time for you, as demonstrated below.
SCOTT@orcl_11g> SELECT NEW_TIME (the_date, 'GMT', 'EST') AS est,
2 NEW_TIME (the_date, 'GMT', 'EDT') AS edt,
3 TO_CHAR (FROM_TZ (CAST (the_date AS TIMESTAMP), 'GMT')
4 AT TIME ZONE 'US/Eastern', 'DD-MON-YYYY HH24:MI') "US Eastern Time"
5 FROM (SELECT SYSDATE AS the_date FROM DUAL
6 UNION ALL
7 SELECT ADD_MONTHS (SYSDATE, 6) AS the_date FROM DUAL)
8 /
EST EDT US Eastern Time
----------------- ----------------- -----------------
20-JUN-2008 10:48 20-JUN-2008 11:48 20-JUN-2008 11:48
20-DEC-2008 10:48 20-DEC-2008 11:48 20-DEC-2008 10:48
SCOTT@orcl_11g>
|
|
|
Re: Regarding NEW_TIME [message #329106 is a reply to message #328620] |
Tue, 24 June 2008 02:39 |
skanandkumar
Messages: 1 Registered: June 2008 Location: Chennai
|
Junior Member |
|
|
This function return you the Daylight saving date and Time for the Given Input Date and Time
Please Try this,
CREATE OR REPLACE function new_time_dst(in_date date, tz1 varchar2, tz2 varchar2) return date as
begin
if in_date is not NULL
then
begin
return to_date(to_char(to_date(
to_char(
to_timestamp_tz(
to_char(to_DATE(to_char(in_date,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'),'YYYYMMDDHH24:MI:SS')||' '||trim(tz1)
,'YYYYMMDDHH24:MI:SS TZR') at time zone tz2
,'YYYYMMDDHH24:MI:SS')
,'YYYYMMDDHH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
end;
else
return NULL;
end if;
end;
/
Out put will be
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT NEW_TIME_DST(TO_DATE('05/05/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'),'GMT','US/EASTERN') from dual;
NEW_TIME_DST(TO_DATE
--------------------
05-MAY-2006 10:00:00
[Mod-edit: Frank added code-tags to improve readability]
[Updated on: Tue, 24 June 2008 02:55] by Moderator Report message to a moderator
|
|
|
|
Re: Regarding NEW_TIME [message #329139 is a reply to message #329106] |
Tue, 24 June 2008 04:27 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Good solution, but you're a bit too keen on nested To_Date/To_Chars.
You can simplify your function to this:CREATE OR REPLACE function new_time_dst(in_date date, tz1 varchar2, tz2 varchar2) return date as
begin
if in_date is not NULL
then
return to_date(
to_char(
to_timestamp_tz(
to_char(in_date,'YYYYMMDDHH24:MI:SS')||' '||trim(tz1)
,'YYYYMMDDHH24:MI:SS TZR') at time zone tz2
,'YYYYMMDDHH24:MI:SS')
,'YYYYMMDDHH24:MI:SS');
else
return NULL;
end if;
end;
And then you can use CAST to get rid of the last To_Date/To_Char pair, and get this:CREATE OR REPLACE function new_time_dst(in_date date, tz1 varchar2, tz2 varchar2) return date as
begin
if in_date is not NULL
then
return cast(to_timestamp_tz(
to_char(in_date,'YYYYMMDDHH24:MI:SS')||' '||trim(tz1)
,'YYYYMMDDHH24:MI:SS TZR') at time zone tz2
as date);
else
return NULL;
end if;
end;
/
|
|
|
Re: Regarding NEW_TIME [message #396165 is a reply to message #328422] |
Sat, 04 April 2009 09:06 |
NIckman
Messages: 64 Registered: May 2007
|
Member |
|
|
barbara,
i used this function but it is still taking 1 hour off.
These are two statements in my sql.for example
it suppose to show 7:00 am it is showing 6:00 am
I do not want to change script every time time change happes.
It should automatically pick right time.
why script bellow not working correctly.
here is the code:
TO_CHAR (FROM_TZ (CAST (start_time AS timeSTAMP), 'GMT')
AT time ZONE 'US/Eastern', 'MM/DD/YY HH24:MI:SS')
TO_CHAR (FROM_TZ (CAST (end_time AS timeSTAMP), 'GMT')
AT time ZONE 'US/Eastern', 'MM/DD/YY HH24:MI:SS')
These are two colums in my sql
start_time and end_time defined as date in oracle table.
Pl help.
Thx, N.
|
|
|
Re: Regarding NEW_TIME [message #396184 is a reply to message #396165] |
Sat, 04 April 2009 15:12 |
|
Barbara Boehmer
Messages: 9100 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You have not provided enough information to determine the problem. You say that the results are wrong, but you have not demonstrated that it is so. You have also not provided your Oracle version. Prior to 2007, daylight savings time in the U.S. was from the first Sunday in April until the last Sunday in October. Since 2007, daylight savings time in the U.S. is from the second Sunday in March until the first Sunday in November. So, if you are using an older Oracle version that used an outdated algorithm, then you would be off by one hour between the second Sunday in March and the first Sunday in April and between the last Sunday in October and the first Sunday in November, so you would need to add some code that checks whether the date in question is between those dates and compensate for it. As you can see by the demo below, my 11g system recognizes that U.S. daylight savings began on March 8th, which was the second Sunday in March this year. You might try the same test on your system. If both times are the same, then the daylight savings algorithm for your Oracle version is outdated.
SCOTT@orcl_11g> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SCOTT@orcl_11g> CREATE TABLE your_table
2 (start_time DATE,
3 end_time DATE)
4 /
Table created.
SCOTT@orcl_11g> INSERT INTO your_table VALUES (SYSDATE-28, SYSDATE-27)
2 /
1 row created.
SCOTT@orcl_11g> SELECT TO_CHAR (FROM_TZ (CAST (start_time AS TIMESTAMP), 'GMT')
2 AT TIME ZONE 'US/Eastern', 'DD-MON-YYYY HH24:MI') "US East before ds",
3 TO_CHAR (FROM_TZ (CAST (end_time AS TIMESTAMP), 'GMT')
4 AT TIME ZONE 'US/Eastern', 'DD-MON-YYYY HH24:MI') "US East after ds"
5 FROM your_table
6 /
US East before ds US East after ds
----------------- -----------------
07-MAR-2009 08:08 08-MAR-2009 09:08
SCOTT@orcl_11g>
|
|
|
|
Goto Forum:
Current Time: Wed Nov 13 06:31:12 CST 2024
|