Home » SQL & PL/SQL » SQL & PL/SQL » select systimestamp + tz_offset('US/Central') from dual;
icon5.gif  select systimestamp + tz_offset('US/Central') from dual; [message #255778] Wed, 01 August 2007 14:17 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I have searched the forum and the manuals for a solution but have not found one yet.

The system clock is in UTC, and I want to retrieve the current UTC time and change it to Central prevaliing time (-5 hours during CDT, and -6 hours during CST).

I have tried:

select  systimestamp + tz_offset('US/Central') from dual;


which gives me the error:

ORA-01722: invalid number


How is this accomplished?

Thanks.
Re: select systimestamp + tz_offset('US/Central') from dual; [message #255782 is a reply to message #255778] Wed, 01 August 2007 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select tz_offset('US/Central') from dual;
TZ_OFFS
-------
-05:00

1 row selected.

So it is normal you get an invalid number as it is not a number.
Use something like:
SQL> select new_time(sysdate,'GMT','CST') from dual;
NEW_TIME(SYSDATE
----------------
01/08/2007 15:28

1 row selected.

Regards
Michel

[Updated on: Wed, 01 August 2007 14:30]

Report message to a moderator

Re: select systimestamp + tz_offset('US/Central') from dual; [message #255784 is a reply to message #255778] Wed, 01 August 2007 14:31 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
You specified 'CST' in your query:

select new_time(sysdate,'GMT','CST') from dual;


Part of the year I need a CST timestamp, and the other part of the year I need a CDT timestamp.

Re: select systimestamp + tz_offset('US/Central') from dual; [message #255789 is a reply to message #255784] Wed, 01 August 2007 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave you the way, now search.
I'm not familiar with US time zone name and other DST.
If this does not work for you, just add the first part of tz_offset. It is not hard to do it with substr.

Regards
Michel
icon14.gif  Re: select systimestamp + tz_offset('US/Central') from dual; [message #255792 is a reply to message #255778] Wed, 01 August 2007 14:51 Go to previous message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thank you.

I was aware of how I could use substr (like below), but I was hoping for an Oracle function a bit more abbreviated.

select  
    systimestamp    as my_utc_systimestamp
    , systimestamp + 
        (   to_number
            (   substr( tz_offset('US/Central'), 1, 3 ) 
            ) / 24
        )           as my_ct_systimestamp
from dual


... which produces ...

MY_UTC_SYSTIMESTAMP	    MY_CT_SYSTIMESTAMP
8/1/2007 7:50:54.210 PM	    8/1/2007 2:50:54.000 PM


Have a nice day:)
Previous Topic: problem adding column values against multiple rows
Next Topic: Trim time from date field
Goto Forum:
  


Current Time: Wed Dec 07 20:50:21 CST 2016

Total time taken to generate the page: 0.13575 seconds