timestamp with 16, 13 and 10 digits to Qlik date


Sometimes, we need to convert the Linux timestamps to Qlik date, it can be done in the SQL script, and can also be done in the Qlik Load/expression.

Linux timestamps with 16 digits are microseconds timestamps, and 13 digits are milliseconds, and 10 digits are second timestamps.

To convert to a Qlik date, hour or time as following:
As the timestamps starts from "19700101 00:00:00",  then this start timestamps needs be added:

For 16 digits:

Date(Date#('19700101', 'YYYYMMDD')+(SynchronousQuery_Event_Time/1000000/86400)

or 

Date(25569+Round(SynchronousQuery_Event_Time/1000000/86400),'YYYYMMDD')

25569 is the number format for date '19700101'


For 13 digits:


Date(Date#('19700101', 'YYYYMMDD')+(SynchronousQuery_Event_Time/1000/86400)

or 

Date(25569+Round(SynchronousQuery_Event_Time/1000/86400),'YYYYMMDD')


For 10 digits:

Date(Date#('19700101', 'YYYYMMDD')+(SynchronousQuery_Event_Time/86400)

or 


Date(25569+Round(SynchronousQuery_Event_Time/86400),'YYYYMMDD')

Comments

Popular posts from this blog

install ipython in Cloudera VM using pip

install postgreSQL in ubuntu 16.04

Install MYSQL 5.6 in ubuntu 12.04/14.04 and set password and remote access