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)).

4 comments:

  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?

    ReplyDelete
  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.

    ReplyDelete
  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))

    ReplyDelete
  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 :)

    ReplyDelete