Interpret and format timestamp field

[pauljohansson]

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

[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]

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

[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]

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