Adapting SQL Server query for PostgreSQL: Date conversion issue

Data and Databases
1

I’m working on moving a query from SQL Server to PostgreSQL. The original query works fine, but I’m running into trouble with the date conversion in PostgreSQL. Here’s what I’ve got so far:

-- PostgreSQL attempt
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
JOIN ScheduledJobs SJ ON sj.JobID = Q.JOB_NAME
JOIN JobCategories c ON c.ID = sj.CategoryID
ORDER BY FIRED_TIME DESC

The problem is that the “FiredTime” column shows dates from 1970 instead of the correct recent dates (like 2022). In SQL Server, it works correctly.

Does anyone know why this is happening and how to fix it? I’m pretty sure it has something to do with the timestamp conversion, but I can’t figure out the right approach in PostgreSQL.

Also, I’m not sure how to handle the UTC offset in PostgreSQL. Any tips on that would be great too. Thanks!

2

hey there! have you considered using the ‘epoch’ function in postgres? it might help with your timestamp issue. something like:

to_timestamp(extract(epoch from (FIRED_TIME - 599266080000000000) / 10000000))

also, for utc offset, maybe try AT TIME ZONE ‘UTC’? just throwin ideas out there! lemme know if any of this helps :slight_smile:

3

hey CreativeChef89, looks like you’re dealin with Windows timestamps. try this:

to_timestamp(((FIRED_TIME::bigint - 621355968000000000) / 10000000)::double precision)

for utc, add AT TIME ZONE ‘UTC’ after. this should give ya the right dates. good luck!

4

The issue you’re encountering is due to the different ways SQL Server and PostgreSQL handle timestamp conversions. In PostgreSQL, you need to account for the Windows epoch (January 1, 1601) versus the Unix epoch (January 1, 1970). Here’s a modified version of your query that should work:

SELECT
to_timestamp((FIRED_TIME::bigint - 621355968000000000) / 10000000) AT TIME ZONE ‘UTC’ AS “FiredTime”,
now() - to_timestamp((FIRED_TIME::bigint - 621355968000000000) / 10000000) AT TIME ZONE ‘UTC’ AS “RunTimeMin”,
FIRED_TIME
FROM QRTZ_FIRED_TRIGGERS Q
JOIN ScheduledJobs SJ ON sj.JobID = Q.JOB_NAME
JOIN JobCategories c ON c.ID = sj.CategoryID
ORDER BY FIRED_TIME DESC

This adjustment should correctly convert your Windows timestamps to PostgreSQL timestamps and handle the UTC offset. Let me know if you need any further clarification.