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)).
Posts
Hi,
ReplyDeleteI 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?
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.
ReplyDeleteOh, 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))
ReplyDeleteThis 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.
ReplyDeleteThanks 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 :)