CODEWITHSUNDEEP
sqloracle-databasedatedst
user3896656
user3896656

Reputation: 37

how to get EST and UTC irrespective of Day

I have to show EST/EDT and GMT/UTC using Oracle 10g.The issue is on Nov 2nd UTC difference will change '+ 05' hour which will cause my jobs to fail.Please let me know how to fix it without manually changing interval to 5 hours.I have tried new_time function but that does not solve the problem. The all columns are DATE datatype.

SELECT PROCESS_NAME, 
     date_modified as EST_DATE_MODIFIED,
     date_modified + interval '+ 04' hour as GMT_DATE_MODIFIED
FROM tbl_process_status ;

Result

EST_DATE_MODIFIED    GMT_DATE_MODIFIED
22-OCT-14 11:08:25  22-OCT-14 03:08:25

Thanks Ed but above query is not returning correct UTC time.

EST - 22-OCT-14 12:19:55
Correct UTC- 22-OCT-14 04:19:55
Above Query UTC 22-OCT-14 08:19:55 

 select (TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'TZH')) * INTERVAL '1' HOUR) from dual;
 -0 4:0:0.0

Thanks Multisync. I got below result but I need data only in DD-MON-YY HH:MM:SS Format. 

Expected 22-OCT-14 12:19:55    
Expected 22-OCT-14 04:19:55    
Result   22-OCT-14 12.19.55.000000000 PM AMERICA/NEW_YORK      
Result   22-OCT-14 04.19.55.000000000 PM UTC

Upvotes: 0

Views: 79

Answers (1)

Multisync
Multisync

Reputation: 8797

select systimestamp at time zone 'est' for_timestamp,
       cast(sysdate as timestamp) at time zone 'est' for_date, 
       cast(sysdate as timestamp) at time zone 'gmt' gmt_timezone 
  from dual;

So you can try this (edited according to the comments)

SELECT PROCESS_NAME, 
     cast(cast(date_modified as timestamp) at time zone 'America/New_York' as date) as EST_DATE_MODIFIED,
     cast(cast(date_modified as timestamp) at time zone 'UTC' as date) as GMT_DATE_MODIFIED
FROM tbl_process_status;

Upvotes: 2

Related Questions