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
sunny_talwar

The one problem I see with this is that the micro second information is still intact. If displaying till seconds is the requirement, then I think this is good, but if we want to get rid of them, you would need to use Floor() function

=TimeStamp(Floor(Timestamp#('2016-04-04T21:08:57.453', 'YYYY-MM-DDThh:mm:ss.fff'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss')

pauljohansson
Creator III
Creator III
Author

Hi Kushal,

thanks for prompt the reply.

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

works OK, however, when milliseconds are missing from timevalue, it doesnt return any result.

br

Paul

Kushal_Chawda

Do you need miliseconds too?

sunny_talwar

Seems to be working for me:

=TimeStamp(Floor(Timestamp#('2016-04-04T21:08:57', 'YYYY-MM-DDThh:mm:ss.[fff]'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss')

Capture.PNG

Kushal_Chawda

then try


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

Kushal_Chawda

I think you have timestamp format with milisecnds and without mili second then try this

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


Update

sunny_talwar

Isn't [.fff] means optional Kush?

magavi_framsteg
Partner - Creator III
Partner - Creator III

Dear Paul.

You can grab the desired positions by using the left() and mid() functions.

On your input field 'YYYY-MM-DD H:MM:SS:FFF TT' it will be something like:

Year will be left(4)

Hour will be mid(13, 2)

Month will be mid(6, 2)

You get the point.

Doing this, you can build a new date or timestamp field.

Med vänliga hälsningar / Best Regards

Magnus Åvitsland

Business Intelligence Architect, Enfo Pointer

Kushal_Chawda

I don't think so, otherwise first expression would have work.

I think when you are converting from string to timestamp it is considering the miliseconds as well to match with exact string format

sunny_talwar

I know you don't have QlikView right now, but look at the screenshot I posted at this link

Re: Interpret and format timestamp field