Converting SQL query into PostgreSQL

112 Views Asked by At

I am trying to convert this SQL Server query below into PostgreSQL...

SQL Server Query:

DECLARE @UTC_OFFSET integer
set @UTC_OFFSET = -4  --EDT  -- -5 for EST
select 
 DATEADD(hour,@UTC_OFFSET, CAST((q.FIRED_TIME - 599266080000000000) / 10000000 / 24 / 60 / 60 AS datetime)) FiredTime
, DATEDIFF(MINUTE,DATEADD(MINUTE,0, CAST((q.FIRED_TIME - 599266080000000000) / 10000000 / 24 / 60 / 60 AS datetime)),Getdate()) as RunTimeMin
, FIRED_TIME 
from QRTZ_FIRED_TRIGGERS Q
inner join ScheduledJobs SJ on sj.JobID = Q.JOB_NAME
inner join JobCategories c on c.ID = sj.CategoryID
order by FiredTime desc

Output:

FiredTime   RunTimeMin  FIRED_TIME
2022-04-20 14:26:07.453 58894   637860759674544643

PostgreSQL Query:

select 
 to_timestamp((FIRED_TIME - 599266080000000000) / 10000000 / 24 / 60 / 60) as "FiredTime"
,now()-to_timestamp((FIRED_TIME - 599266080000000000) / 10000000 / 24 / 60 / 60 ) as "RunTimeMin"
, FIRED_TIME 
from QRTZ_FIRED_TRIGGERS Q
inner join ScheduledJobs SJ on sj.JobID = Q.JOB_NAME
inner join JobCategories c on c.ID = sj.CategoryID
order by FIRED_TIME desc

Query Output:

FiredTime                    |RunTimeMin                |fired_time        |
-----------------------------+--------------------------+------------------+
1970-01-01 07:25:10.000 -0500|19143 days 07:42:31.036628|637896173296983621|
1970-01-01 07:25:10.000 -0500|19143 days 07:42:31.036628|637896173288826690|
1970-01-01 07:25:10.000 -0500|19143 days 07:42:31.036628|637896173288762226|
1970-01-01 07:25:10.000 -0500|19143 days 07:42:31.036628|637896173288695965|

Questions:

-The "FiredTime" values are outputting a "1970-01-01 07:25:10.000 -0500" value in Postges, and in SQL Server "2022-04-20". What is causing this issue, and any idea how to resolve?

0

There are 0 best solutions below