Fixing timestamp

[#1]

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?

[#2]

you simply have to concatenate?

[#3]

Hi DD Yang,

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

Best Regards,

R

[jagan]

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.

[#5]

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.

[Colin-Albert]

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.

[#7]

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?

[maxgro]

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')

[#9]

Massimo Grossi

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

[maxgro]

replace with the field you use, @1

or post some data