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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #329107 is a reply to message #329106] Tue, 24 June 2008 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Regarding NEW_TIME [message #329139 is a reply to message #329106] Tue, 24 June 2008 04:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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>

Re: Regarding NEW_TIME [message #397519 is a reply to message #396184] Sat, 11 April 2009 13:59 Go to previous message
NIckman
Messages: 64
Registered: May 2007
Member
Barbara,
Thanks for your reply. it is working now. Thanks for info
on new DST timing date changes.
thx,N.

Previous Topic: ORA-24338: statement handle not executed
Next Topic: create table
Goto Forum:
  


Current Time: Wed Nov 13 06:31:12 CST 2024