Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

truppell
Contributor

Help converting to timestamp

Hello,

I have a timestamp field that looks like this:

Screen Shot 2017-07-07 at 11.31.06 AM.png

It is being read as text and I've tried using Timestamp# to convert it, but it is still tagged as $ascii $text:

    Timestamp#(Replace([timestamp],' GMT ',' '),'WWW MMM DD hh:mm:ss YYYY') as newTS,

Screen Shot 2017-07-07 at 11.33.42 AM.png   Screen Shot 2017-07-07 at 11.37.57 AM.png

Would really appreciate some help with this!

Thanks,

Tanya

1 Solution

Accepted Solutions
MVP
MVP

Re: Help converting to timestamp

May be try this

Timestamp#(Mid(Replace([timestamp],' GMT ',' '), Index(Replace([timestamp],' GMT ',' '), ' ')+1),'MMM DD hh:mm:ss YYYY') as newTS

7 Replies
truppell
Contributor

Re: Help converting to timestamp

I am on Qlik Sense 2.2.3

MVP
MVP

Re: Help converting to timestamp

May be try this

Timestamp#(Mid(Replace([timestamp],' GMT ',' '), Index(Replace([timestamp],' GMT ',' '), ' ')+1),'MMM DD hh:mm:ss YYYY') as newTS

Re: Help converting to timestamp

Try this

Dual(TimeStamp#(Replace(timestamp,' GMT',''),'MMM DD hh:mm:ss YYYY'),TimeStamp#(Mid(Replace(timestamp,' GMT',''),5),'MMM DD hh:mm:ss YYYY')) as NewTS

truppell
Contributor

Re: Help converting to timestamp

That works! Can you explain why?

MVP
MVP

Re: Help converting to timestamp

For some reason, the TimeStamp#() or Date#() are unable to read the WeekDay using WWW. I just removed it from the interpretation to fix this

mounachandra1
Contributor

Re: Help converting to timestamp

If you  want to convert this to other Format Like,

06/07/2017 10:30:40

you can use below expression:

Replace Time with your fieldname.

Timestamp#(DATE(DATE#(LEFT(RIGHT(replace(Time, 'GMT',''),21),6)&' '&RIGHT(RIGHT(replace(Time, 'GMT',''),21),4),'MMM DD YYYY'),'MM/DD/YYYY')&' '&SubField(Time,' ',4),'MM/DD/YYYY HH:MMSmiley FrustratedS') as #Timestamp

truppell
Contributor

Re: Help converting to timestamp

Thanks Sunny!