0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Tom Andersen
  • 1
  • 1
    Well, `to_timestamp()` returns a `timestamp` (hence the name) which includes a time part. If you don't want a timestamp, then why use `to_timestamp`. But the real question is: if you want a date, then why not simply declare the column as `date`? https://blog.sql-workbench.eu/post/epoch-mania/ –  May 31 '22 at 20:21
  • Only tag the RDBMS you want a solution in... tagging SQL Server is no help, because that is asking for people with expertise in SQL Server. – Dale K May 31 '22 at 20:23
  • @DaleK Without SQL Server knowledge you cannot understand the question. Perhaps you should vote to close rather than changing tags.. – Laurenz Albe Jun 01 '22 at 06:11
  • @LaurenzAlbe I disagree because the question should describe and illustrate the required results, not depend on someone being able to fully understand one SQL dialect and translate to another. The only situation requirings multiple rdbms tags is when a solution is required for both. – Dale K Jun 01 '22 at 06:22
  • @DaleK Exactly. If the question does not describe the required results, it should be closed as lacking detail. – Laurenz Albe Jun 01 '22 at 06:31
  • @LaurenzAlbe Thats a separate issue from correcting the tags. – Dale K Jun 01 '22 at 06:32
  • what is "fired_time"? Is it a number of time units? (what unit?) what date does zero represent? – Paul Maxwell Jun 01 '22 at 23:19

0 Answers0