Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pauljohansson
Creator III
Creator III

Interpret and format timestamp field

I have a Timestamp field that i want to interpret and format, but I can get it right.

I want to convert field: 2016-04-04T11:16:44.327


to Timestamp field: 2016-04-04 23:16:44

That is, i want to remove the 'T' and remove the milliseconds. My thinking goes:


=timestamp(timestamp#(replace(timevalue, 'T', ' '), 'YYYY-MM-DD H:MM:SS:FFF TT'), 'YYYY-MM-DD HH:MM:SS')

But it doesnt give any reuslt.  Any pointers?

thanks in advance,
Paul

25 Replies
Kushal_Chawda

Yeah.. have you tried with my expression?

sunny_talwar

This one?

timestamp(

alt(timestamp#(purgechar(timevalue, 'T'), 'YYYY-MM-DDhh:mm:ss.[fff]'),

timestamp#(purgechar(timevalue, 'T'), 'YYYY-MM-DDhh:mm:ss')), 'YYYY-MM-DD hh:mm:ss')

I am sure this will work, I don't see why it won't. But I was wondering if we need Alt function here. My guess is that

2016-04-04T21:08:57

or

2016-04-04T21:08:57.453

both should be handled by this

timestamp(timestamp#(purgechar(timevalue, 'T'), 'YYYY-MM-DDhh:mm:ss.[fff]'), 'YYYY-MM-DD hh:mm:ss')

pauljohansson
Creator III
Creator III
Author

Sunny,

using Alt works.

However, 2016-04-04T21:08:57 is not handled in:

timestamp(timestamp#(purgechar(timevalue, 'T'), 'YYYY-MM-DDhh:mm:ss.[fff]'), 'YYYY-MM-DD hh:mm:ss')

I choose  timestamp(timestamp#(purgechar(left(timevalue,19), 'T'), 'YYYY-MM-DDhh:mm:ss')), since i never care about the milliseconds anyway.

br

Paul

sunny_talwar

Although you have gotten what you were looking for, but I just did some tests:

Table:

LOAD Date,

  TimeStamp(Floor(Timestamp#(Date, 'YYYY-MM-DDThh:mm:ss.[fff]'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss') as Test1,

  Timestamp(TimeStamp#(PurgeChar(Date, 'T'), 'YYYY-MM-DDhh:mm:ss.[fff]'), 'YYYY-MM-DD hh:mm:ss') as Test2;

LOAD Date

FROM

Community_212069.xlsx

(ooxml, embedded labels, table is Sheet1);

Output

Capture.PNG

Not sure why Test2 doesn't work. But my original one works (Test1)

swuehl
MVP
MVP

Without testing, but shouldn't it be

[.fff]

instead of

.[fff]

sunny_talwar

I thought that too, but .[fff] is also working