Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Contributor II
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:


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];



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?

4 Replies

Seems to be working


Contributor II
Contributor II

I have Sense 3.0, not QlikView.


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:

Is the issue resolved yet?

Not applicable

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.