topic Unusual timestamp format in QlikView

Unusual timestamp format

Tue, 20 Sep 2016 18:57:47 GMT

Hi all,

I'm working with data that includes a time-stamp format I haven't seen before:

"Mon Nov 21 09:33:19 EST 2016".

What formatting indication would I use with the timestamp#() function in order to tell Qlik that the field is time-stamp and not text? I will need to calculate the difference between a "start" and and "end" time-stamp in my app.

Thanks!

Robin

Re: Unusual timestamp format

swuehl — Tue, 20 Sep 2016 19:34:32 GMT

Maybe something along

LOAD *,

Timestamp(Makedate(Year, Month, DayOfMonth)+Time) as Timestamp;

LOAD *,

Subfield(TS,' ',1) as WeekdayText,

Month(Date#(Subfield(TS,' ',2),'MMM')) as Month,

Subfield(TS,' ',3) as DayOfMonth,

Time#(Subfield(TS,' ',4),'hh:mm:ss') as Time,

Subfield(TS,' ',5) as Timezone,

Subfield(TS,' ',6) as Year ;

LOAD * INLINE [

"Mon Nov 21 09:33:19 EST 2016"

];

You can use the Timezone related date and time functions in QV to transform to your timezone, if needed,

Re: Unusual timestamp format

Tue, 20 Sep 2016 19:57:59 GMT

Oh goodness, yes, I guess I could parse it all out. Was kind of hoping there was some sort of whole-field translation, to avoid possible errors in parsing. Thank you!

Re: Unusual timestamp format

Peter_Cammaert — Tue, 20 Sep 2016 20:29:56 GMT

There are, but they don't like superfluous stuff like DayNames or too complex stuff like timezone codes.

For example, this one will produce a regular TimeStamp in a single stroke but it will ignore the TimeZone (and therefor it'll produce a localtime value):

TimeStamp#( mid('Mon Nov 21 09:33:19 EST 2016', 5, 16) &

right('Mon Nov 21 09:33:19 EST 2016', 4), 'MMM DD hh:mm:ss YYYY') AS TS,

If you want to convert this value into UTC or another region, use a mapping table with TimeZone strings and time corrections. Otherwise, you can simply ignore them.

Peter

Re: Unusual timestamp format

Wed, 21 Sep 2016 01:08:37 GMT

Okay, thanks. I think this might be the best way to go then.

Thank you both!