Reputation: 379
I am using a query to get some application Received Date from Oracle DB which is stored as GMT. Now I have to convert this to Eastern standard/daylight savings time while retrieving. I am using the below query for this:
select to_char (new_time(application_recv_date,'gmt','est'), 'MON dd, YYYY') from application
It works fine for Standard time. But for daylight savings time we need to convert it to 'edt' based on timezone info. I am not very sure on how to do this. Please help me out
Upvotes: 14
Views: 109896
Reputation: 662
There already a function exists which converts the time of your needed column from one timezone to another:-
convert_tz("columname or / the time you want to convert", fromTimeZone, ToTimeZone)
For Example:- I want to convert time of dateTIme column from Indian Time (IST) to Brazil TimeZone as my host database time is by default in india it takes indian time:-
convert_tz(dl.modified_datetime,'+5:30','-3:00') 'modified_time'
So, In this process you can convert it easily.
Upvotes: -1
Reputation: 2587
In Oracle, you can achieve this with below query:
Select current_timestamp, current_timestamp at time zone 'Australia/Sydney' from dual;
Where Australia/Sydney
is the name of your timezone in which you want to convert your time.
Upvotes: 5
Reputation: 10525
You can use this query, without having to worry about timezone changes.
select to_char(cast(application_recv_date as timestamp) at time zone 'US/Eastern',
'MON dd, YYYY'
)
from application;
Ex:
EDT:
select cast(date'2014-04-08' as timestamp) d1,
cast(date'2014-04-08' as timestamp) at time zone 'US/Eastern' d2
from dual;
D1 D2
---------------------------------- -------------------------------------------
08-APR-14 12.00.00.000000 AM 07-APR-14 08.00.00.000000 PM US/EASTERN
EST:
select cast(date'2014-12-08' as timestamp) d1,
cast(date'2014-12-08' as timestamp) at time zone 'US/Eastern' d2
from dual;
D1 D2
---------------------------------- -------------------------------------------
08-DEC-14 12.00.00.000000 AM 07-DEC-14 07.00.00.000000 PM US/EASTERN
UPDATE:
Thanks to Alex Poole for reminding that, when timezone is not specified, local timezone is used for conversion.
To force the date to be recognized as GMT, use from_tz.
from_tz(cast(date'2014-12-08' as timestamp), 'GMT') at time zone 'US/Eastern'
Upvotes: 29