I have read all the articles and blog posts I can find and am still stuck. I've simplified and boiled down my issue to this:
I have two integer fields in a postgres database that I want to load as a pair of Date and Timestamp fields in my Qlik Sense model. One works absolutely perfectly, the other does not, in spite of identical script code. I've narrowed the issue down as thus:
In the Data Model Preview pane, Qlik Sense tags "Date Created" and "Date/Time Created" as "$numeric $timestamp", as expected.
However, "Date Resolved" and "Date/Time Resolved" are tagged only as "$numeric" and don't behave as dates or timestamps if the source data contains an integer value less than 315532800, which equates to 1980-01-01 00:00:00.
My script looks like this:
LOAD
Timestamp(Timestamp#("Date/Time Created")) as [Date/Time Created],
Date(Date#("Date/Time Created", 'YYYY-MM-DD hh:mm:ss[.fff]'), 'YYYY-MM-DD') as [Date Created],
Timestamp(Timestamp#("Date/Time Responded")) as [Date/Time Responded],
Date(Date#("Date/Time Responded", 'YYYY-MM-DD hh:mm:ss[.fff]'), 'YYYY-MM-DD') as [Date Responded];
[workorder]:
select
to_char(to_timestamp("createdtime"/1000), 'YYYY-MM-DD HH24:MI:SS') as "Date/Time Created", //Works as expected - no values less than 315532800 in source data
to_char(to_timestamp("respondedtime"/1000), 'YYYY-MM-DD HH24:MI:SS') as "Date/Time Responded" //Fails to be recognized as a timestamp when respondedtime < 315532800
from workorder;
Furthermore, I found that Timestamp#('1979-12-31 23:59:59') is not interpreted as a $timestamp, but Timestamp#('1980-01-01 00:00:01') is.
Is this a bug? The 70's weren't all THAT bad!, Why does Sense want to pretend that decade never existed?
How can I get all dates in the 20th century to be correctly interpreted as dates and times?
