
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Having trouble getting 1970-01-01 to be interpreted as a Timestamp
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Seems to be working

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have Sense 3.0, not QlikView.
Script:
Timestamp(Timestamp#('1980-01-01 00:00:00')) as D1980, | |
Timestamp(Timestamp#('1979-12-31 23:59:59')) as D1979, |
Screenshots of the metadata Preview tab in the Data Model viewer:
for D1980:
and D1979:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is the issue resolved yet?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am seeing the same issue in Qlik Sense 3.0. We are scheduled to upgrade on 3-30-2017. I will check and see if that fixes the issue and try to remember to report back.
