Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vermilyeaqlik
New Contributor II

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:MISmiley FrustratedS') 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:MISmiley FrustratedS') 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?

4 Replies
MVP
MVP

Re: Having trouble getting 1970-01-01 to be interpreted as a Timestamp

Seems to be working

Capture.PNG

vermilyeaqlik
New Contributor II

Re: Having trouble getting 1970-01-01 to be interpreted as a Timestamp

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:

MVP
MVP

Re: Having trouble getting 1970-01-01 to be interpreted as a Timestamp

Is the issue resolved yet?

Not applicable

Re: Having trouble getting 1970-01-01 to be interpreted as a Timestamp

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.