Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

oracle - Time zone conversion in SQL query

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...