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: 
NZFei
Partner - Specialist
Partner - Specialist

Need help on this date format

Hi all,

I load some data from some web API with this date format. Can you anyone know how to translate it into normal date format in QlikView / Sense?

Thank you very much!

Fei

1 Solution

Accepted Solutions
NZFei
Partner - Specialist
Partner - Specialist
Author

I think I have figured out. Some other article says we need to divide it by 1000 first so I have tried this and it looks working.

=Timestamp( MakeDate(1970) + Num(TextBetween(Date, '(','+'))/1000/60/60/24)

Thank you very much Gysbert!

Fei

Another thing: the result is UTC so to change to local time we need to use converttolocaltime function, for example when converting it to New Zealand time, it will be like ths:

=ConvertToLocalTime(Timestamp( MakeDate(1970) + Num(1487014261)/1000/60/60/24),'Wellington')

View solution in original post

3 Replies
Gysbert_Wassenaar

That looks like a unix timestamp embedded in a text string. Something like this might do the trick:

Timestamp( MakeDate(1970) + Num(TextBetween(Date, '(','+')) /60/60/24)


talk is cheap, supply exceeds demand
NZFei
Partner - Specialist
Partner - Specialist
Author

Hi Gysbert,

Thank you for your reply. If we take /Date(1479078000000+0100)/ as an example, it returns 12/02/48840 8:00:00 AM. How to get the date (48840) please?

Cheers.

Fei

NZFei
Partner - Specialist
Partner - Specialist
Author

I think I have figured out. Some other article says we need to divide it by 1000 first so I have tried this and it looks working.

=Timestamp( MakeDate(1970) + Num(TextBetween(Date, '(','+'))/1000/60/60/24)

Thank you very much Gysbert!

Fei

Another thing: the result is UTC so to change to local time we need to use converttolocaltime function, for example when converting it to New Zealand time, it will be like ths:

=ConvertToLocalTime(Timestamp( MakeDate(1970) + Num(1487014261)/1000/60/60/24),'Wellington')