Unusual timestamp format

[#1]

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

[swuehl]

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 [

TS

"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,

[#3]

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!

[Peter_Cammaert]

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

[#5]

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

Thank you both!

R.