Home » SQL & PL/SQL » SQL & PL/SQL » systimestamp conversion (10g)
systimestamp conversion [message #389548] Mon, 02 March 2009 11:04 Go to next message
angelaldixon
Messages: 9
Registered: April 2008
Junior Member
I need to convert systimestamp to a date. I am doing the following but not getting the correct time.

to_date(to_char(systimestamp at time zone timezone_rec.timezone, 'dd-mon-yyyy hh:mi:ss PM'),'dd-mon-yyyy hh:mi:ss PM')


'timezone_rec.timezone' would be the timezone name from v$timezone_names. The above was working until I set the date to March 8 for DST.

If i just do 'systimestamp at time zone timezone_rec.timezone' it is fine but as soon as I add the to_char() it displays the time one hour previous (2AM), which on March 8 there will not be a 2AM.

The function I am updating is currently returning a DATE, so I need to continue to return a date to not mess up any of the callers of this function.

thanks for any help
angie
Re: systimestamp conversion [message #389550 is a reply to message #389548] Mon, 02 March 2009 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
ou need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Do NOT tell us what you think is happening.
While using sqlplus do CUT & PASTE for the whole session so we can see exactly what you are doing & how Oracle responds!
Re: systimestamp conversion [message #389551 is a reply to message #389548] Mon, 02 March 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First of all, are you time zone files up to date?

Regards
Michel
Re: systimestamp conversion [message #389553 is a reply to message #389548] Mon, 02 March 2009 11:11 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Has Oracle been patched to properly handle Daylight Saving Time adjustment(s)?
Re: systimestamp conversion [message #389556 is a reply to message #389550] Mon, 02 March 2009 11:26 Go to previous messageGo to next message
angelaldixon
Messages: 9
Registered: April 2008
Junior Member
date on the server:

$ date
Sun Mar  8 04:47:02 EDT 2009


from sqlplus:

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
08-MAR-09 04.47.46.962370 AM -04:00


SQL> select systimestamp at time zone 'US/Eastern' from dual;

SYSTIMESTAMPATTIMEZONE'US/EASTERN'
---------------------------------------------------------------------------
08-MAR-09 03.48.08.993406 AM US/EASTERN


SQL> select systimestamp at time zone 'Asia/Hong_Kong' from dual;

SYSTIMESTAMPATTIMEZONE'ASIA/HONG_KONG'
---------------------------------------------------------------------------
08-MAR-09 04.48.55.411087 PM ASIA/HONG_KONG


SQL> select systimestamp at time zone 'Europe/London' from dual;

SYSTIMESTAMPATTIMEZONE'EUROPE/LONDON'
---------------------------------------------------------------------------
08-MAR-09 08.49.10.445783 AM EUROPE/LONDON


I am in the 'US/Eastern' timezone they all seem to be correct except for when I use US/Eastern.
Re: systimestamp conversion [message #389557 is a reply to message #389548] Mon, 02 March 2009 11:29 Go to previous messageGo to next message
angelaldixon
Messages: 9
Registered: April 2008
Junior Member
How would I know if oracle has been patched to handle time zones and how would I know if my file is update?

I am not a DBA, sorry for the questions.
Re: systimestamp conversion [message #389558 is a reply to message #389548] Mon, 02 March 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I am not a DBA, sorry for the questions.
You need to direct these to your DBA or visit http://metalink.oracle.com to find answers.
Re: systimestamp conversion [message #389564 is a reply to message #389557] Mon, 02 March 2009 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you can also post your Oracle version with 4 decimals.
Ask your DBA if (s)he patched your database.

Regards
Michel
Re: systimestamp conversion [message #389565 is a reply to message #389548] Mon, 02 March 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
What do you see if/when you set server date ahead to 01-May-2009?
Re: systimestamp conversion [message #389580 is a reply to message #389548] Mon, 02 March 2009 13:36 Go to previous messageGo to next message
angelaldixon
Messages: 9
Registered: April 2008
Junior Member
Oracle version is 9.2.0.8, I believe I stated it was 10, my apologizes.

When I move the date to 01-May-09, i get the correct output from sql plus:

from the server:

# date
Fri May  1 10:05:41 EDT 2009


from sqlplus:

SQL> select systimestamp at time zone 'US/Eastern' from dual;

SYSTIMESTAMPATTIMEZONE'US/EASTERN'
---------------------------------------------------------------------------
01-MAY-09 10.00.48.032169 AM US/EASTERN
Re: systimestamp conversion [message #389581 is a reply to message #389580] Mon, 02 March 2009 13:40 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 02 March 2009 18:55
And you can also post your Oracle version with 4 decimals.
Ask your DBA if (s)he patched your database.

Regards
Michel

Check Metalink to know what are the updates for your version.

Regards
Michel

Previous Topic: Problem with DBMS_JOB
Next Topic: alter view
Goto Forum:
  


Current Time: Sat Dec 03 18:25:41 CST 2016

Total time taken to generate the page: 0.09840 seconds