Home » SQL & PL/SQL » SQL & PL/SQL » Converting TIMESTAMP from one timezone to other (Oracle 10.2.0.4, Solaris 10)
Converting TIMESTAMP from one timezone to other [message #425546] Fri, 09 October 2009 10:33 Go to next message
kaustubh
Messages: 26
Registered: June 2007
Junior Member
Hi,
I have a table which has a column of datatype as TIMESTAMP.
The database timezone is set as EDT.
While displaying the values from this column I want to display the values in GMT.

Could you please suggest how can I achieve this?

On doing some search I found that if the datatype is used as TIMESTAMP with LOCAL TIMEZONE then by i achieve the desired result by setting session timezone to desired timezone (GMT in my case) but at this stage of our product release cycle I cannot change the column datatype from TIMESTAMP to TIMESTAMP with LOCAL TIMEZONE.

Is there any other way?
Re: Converting TIMESTAMP from one timezone to other [message #425548 is a reply to message #425546] Fri, 09 October 2009 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Is there any other way?
You have ruled out changing the datatype.
What can you change?
In order to get different results, something MUST change.
Re: Converting TIMESTAMP from one timezone to other [message #425549 is a reply to message #425546] Fri, 09 October 2009 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
While displaying the values from this column I want to display the values in GMT.

NEW_TIME

Regards
Michel
Re: Converting TIMESTAMP from one timezone to other [message #425553 is a reply to message #425546] Fri, 09 October 2009 11:24 Go to previous messageGo to next message
kaustubh
Messages: 26
Registered: June 2007
Junior Member
I have one database on IST (Asia/Calcutta) timezone. I am trying the to use the new_time function on it but it gives an error as below:
SQL> select new_time(systimestamp, 'IST', 'GMT') - CAST(to_date('01-Jan-70') AS TIMESTAMP) from dual;
select new_time(systimestamp, 'IST', 'GMT') - CAST(to_date('01-Jan-70') AS TIMESTAMP) from dual
*
ERROR at line 1:
ORA-01857: not a valid time zone

Not sure why does it say invalid timezone.


The database time zone is set to IST
SQL> select DBTIMEZONE from dual;

DBTIME
------
+05:30

Re: Converting TIMESTAMP from one timezone to other [message #425564 is a reply to message #425553] Fri, 09 October 2009 12:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You asked for EDT, you had an answer for EDT.
Next time you will post the right question, I hate wasting my time, now you have to find the answer without me.

Regards
Michel
Previous Topic: slow store procedure (merged 4)
Next Topic: HELP WITH EASY SELECT DECODE ROUND
Goto Forum:
  


Current Time: Sun Dec 04 04:39:40 CST 2016

Total time taken to generate the page: 0.10166 seconds