topic Re: Fixing timestamp in QlikView

Fixing timestamp

Thu, 17 Apr 2014 01:08:15 GMT

Hi,

I'm getting a timestamp in the format below.

2014-03-01T00:01:01-0000

Does anyone know how to re-write this to one long string?

I was able to break it down per below into two separate strings but I would like to make it as one - MM/DD/YYYY hh:mm:ss

Timestamp#(date(date#(left(@1, 10), 'YYYY-MM-DD'), 'MM/DD/YYYY')) as [Date UTC],

Timestamp#(date(date#(mid(@1, 12,8), 'hh:mm:ss'), 'hh:mm:ss')) as [Time UTC],

Can anyone help? I don't think Qlikivew recognizes the format?

Re: Fixing timestamp

Thu, 17 Apr 2014 01:13:09 GMT

you simply have to concatenate?

Re: Fixing timestamp

Thu, 17 Apr 2014 02:01:48 GMT

Hi DD Yang,

You would just have to concatenate it, and save it into a column.

Best Regards,

Re: Fixing timestamp

jagan — Thu, 17 Apr 2014 02:38:29 GMT

Hi Yang,

Try like this

=Timestamp(Timestamp#(Replace(Left('2014-03-01T00:01:01-0000', 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'MM/DD/YYYY hh:mm:ss')

Regards,

Jagan.

Re: Fixing timestamp

Thu, 17 Apr 2014 14:53:40 GMT

Ram Asokan Ramkumar Ramagopalan

I will have millions of rows of data... would concatenate be the most efficient way to execute this?

Jagan Mohan thank you for the answer but it doesn't work as 2014-03-01T00:01:01-0000 was just an example.

My date format is YYYY-MM-DDThh:mm:ss-XXXX (the XXXX represents time zone, in this case its UTC.

Re: Fixing timestamp

Colin-Albert — Thu, 17 Apr 2014 15:06:01 GMT

If you have a large number of rows, it is better to keep the data as separate date and time fields and concatenate them in you charts.

This will save memory as fewer unique values are being stored in your data model.

Re: Fixing timestamp

Thu, 17 Apr 2014 15:13:39 GMT

Colin Albert

Thank you for your insight.

This is where it gets tricky...

I need to put my time in PDT. Right now, I receive it in UTC.

I used a previous code below to change this (subtract 7 hours) but my timestamp was in a more standard format.

Previous: DD/MMM/YYY:hh:mm:ss+0000

ie - [01/Apr/2014:00:00:00 +0000]

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYYY:hh:mm')-Timestamp#(7,'hh'), 'YYYY')) as [Year PDT],

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYYY:hh:mm')-Timestamp#(7,'hh'), 'MMM')) as [Month PDT],

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYYY:hh:mm')-Timestamp#(7,'hh'), 'DD')) as [Day PDT],

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYYY:hh:mm')-Timestamp#(7,'hh'), 'hh')) as [Hour PDT],

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYYY:hh:mm')-Timestamp#(7,'hh'), 'mm')) as [Minute PDT],

Current: YYYY-MM-DDThh:mm:ss+0000

ie - 2014-03-01T00:01:01-0000

Timestamp#(date(date#(left(@1, 10), 'YYYY-MM-DD'), 'YYYY')) as [Year UTC],

Timestamp#(date(date#(left(@1, 10), 'YYYY-MM-DD'), 'MM')) as [Month UTC],

Timestamp#(date(date#(left(@1, 10), 'YYYY-MM-DD'), 'DD')) as [Day UTC],

Timestamp#(date(date#(mid(@1, 12,8), 'hh:mm:ss'), 'hh')) as [Hour UTC],

Timestamp#(date(date#(mid(@1, 12,8), 'hh:mm:ss'), 'mm')) as [Minute UTC],

I need to do this again with my new timestamp format but I don't think I think Qlikview is not recognizing the timestamp format I'm using with the T in the middle (see below)?

How can I put this into PDT?

Re: Fixing timestamp

maxgro — Thu, 17 Apr 2014 15:18:19 GMT

try

date(

num(date#(left('2014-03-01T00:01:01-0000', 10), 'YYYY-MM-DD'))

+ num(date#(mid('2014-03-01T00:01:01-0000', 12,8), 'hh:mm:ss') )

, 'MM/DD/YYYY hh:mm:ss')

Re: Fixing timestamp

Thu, 17 Apr 2014 15:21:13 GMT

Massimo Grossi

This will not work, 2014-03-01 is only an example. I will have various dates in my data.

Re: Fixing timestamp

maxgro — Thu, 17 Apr 2014 15:22:36 GMT

replace with the field you use, @1

or post some data

Re: Fixing timestamp

Thu, 17 Apr 2014 17:20:06 GMT

Timestamp#(date(date#(Replace(Left(@1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'MM/DD/YYYY hh:mm:ss')) as [Date UTC],

Timestamp#(date(date#(Replace(Left(@1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'YYYY')) as [Year UTC],

Timestamp#(date(date#(Replace(Left(@1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'MM')) as [Month UTC],

Timestamp#(date(date#(Replace(Left(@1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'DD')) as [Day UTC],

Timestamp#(date(date#(Replace(Left(@1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'hh')) as [Hour UTC],

Timestamp#(date(date#(Replace(Left(@1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss'), 'mm')) as [Minute UTC],