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