Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help: Converting Unix Times to DD/MM/YYYY

Hello, I have a data field which is in Unix Time format (eg. 1451610061) and I want to create a new date afield which has regular date format. I am very new to qlik sense and I have no idea how to do it. I will paste in the LOAD for the table of the data field in question. The datafield is called dateline:

LOAD ticketid,

    dateline,

SQL SELECT ticketid,

    dateline,

1 Reply
Anonymous
Not applicable
Author

Good day Lorcan,

This is an old post but I had the same problem and found little help from the community that I could understand as a newbee to Qlik.

I actually did it with SQL not with Qlik but I will post the Qlik solution once I understand it, I have included both the Qlik load and SQL select statements so that you can understand where in your load script to use the code:

LOAD dateline,

SQL SELECT dateline = DATEADD(S, CONVERT(int,LEFT(dateline, 10)), '1970-01-01')

FROM ...;

UPDATE

I have found that the above SQL SELECT calculates data to UCT. I live in a UCT +2 Time Zone with no Daylight Savings. I needed to update my script to the below to add two hours to my timestamp:

SQL SELECT dateline = DATEADD(HOUR, 2 , DATEADD(S, CONVERT(int,LEFT(dateline, 10)), '1970-01-01')),