Thursday, March 29, 2012

QlikView Expression for Converting Unix int Epoch Dates to date

I wrote an earlier post on ways to do this in SSIS, T-SQL, and PL-SQL, but today needed to do it in a QlikView expression (or load script), since QlikView has AddMonths and AddYears functions, it wasn't immediately obvious that adding days is just a datetime or timestamp plus a numeric and cast back.

To get long datetime

=Timestamp(Timestamp('1970-01-01 00:00:00.000') + YOURUNIXDATEVALUE/1000/60/60/24)

To get date only

=Date(Date('1970-01-01 00:00:00.000') + YOURUNIXDATEVALUE/1000/60/60/24)

The only tricky thing is that in both cases, you're converting milliseconds to days, which makes sense with a date value, but slightly less with a value that's granular to the millisecond.


If you just need to add days rather than convert the milliseconds since 1970 data type, you can just add whole days.  For example tomorrow's date:  =(date(today() + 1)).


  1. Hi,

    I just tried you method on Qlikview 11 with an epoch time:
    date(date('1970-01-01 00:00:00.000') +1339752263/1000/60/60/24)

    Result: 1970-01-16

    Doesnt seem to work or did I miss something?

  2. Hi Bastien, can you elaborate on your question please? 1339752263 ms is about 15.5 days, which makes jan 16 1970 right for date only. I might not be understanding your issue.

  3. Oh, wait, I see, my post is confusing. It's specific to converting the weird unix int date type, I'll make it more clear, thanks for the heads up. To add whole days to a date just add as integer values =(date(today() + 1))

  4. This is close to a necro thread but I just thought I'd point out that Bastien was trying to convert an epoch time that was in seconds, while the expression converts a millisecond timestamp. Just leave out the /1000 from the expression and it would work nicely.

    Thanks for this expression Caroline, very simple but I still didn't come to think of it when I was making a dimension for my epochs :)