Saturday, August 26, 2023

Oracle - calculate time difference with time_zone adjustment

Task: Calculate difference between CREATEDON  column (with UTC time_zone) and CURRENT_DATE (with local time zone from database) 

Table contents:

select CREATEDON, CURRENT_DATE from TABLE_NAME;



Query:

with time_difference as

(

select

cast(FROM_TZ(CAST(CREATEDON AS TIMESTAMP), 'UTC') at time zone (SELECT DBTIMEZONE FROM DUAL)  as date) AS Created,

cast(FROM_TZ(CAST(CURRENT_DATE AS TIMESTAMP), (SELECT DBTIMEZONE FROM DUAL)) AS DATE) AS CurrentDate

from TABLE_NAME


)

select 

to_char(Created,'YYYY-mm-dd HH24:MI') as Created,

to_char(CurrentDate ,'YYYY-mm-dd HH24:MI') as CurrentDate,

round((CurrentDate - Created) * 24 * 60) as DifferenceInMinutes

from time_difference;


Result:



No comments:

Post a Comment

Problem with database open ORA-19804, ORA-19809, ORA-03113

1. Try to login to database with SYS AS SYSDBA user. If the instance is idle, run the startup command. 2. If ORA-03113 occured, check the la...