OraFAQ Forum: SQL & PL/SQL » select systimestamp + tz

Home » SQL & PL/SQL » SQL & PL/SQL » select systimestamp + tz_offset('US/Central') from dual;

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select systimestamp + tz_offset('US/Central') from dual; [message #255778]

Wed, 01 August 2007 14:17

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.

Report message to a moderator

Re: select systimestamp + tz_offset('US/Central') from dual; [message #255782 is a reply to message #255778]

Wed, 01 August 2007 14:29

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: select systimestamp + tz_offset('US/Central') from dual; [message #255789 is a reply to message #255784]

Wed, 01 August 2007 14:38

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: select systimestamp + tz_offset('US/Central') from dual; [message #255792 is a reply to message #255778]

Wed, 01 August 2007 14:51

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:)

Report message to a moderator

Previous Topic:	problem adding column values against multiple rows
Next Topic:	Trim time from date field

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 15 10:46:29 CST 2025