topic Re: Interpret and format timestamp field in QlikView

Interpret and format timestamp field

pauljohansson — Tue, 05 Apr 2016 09:19:46 GMT

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

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:22:19 GMT

May be try this:

=TimeStamp(Floor(Timestamp#('2016-04-04T11:16:44.327', 'YYYY-MM-DDThh:mm:ss.fff'), 1/(24*60*60)))

or this:

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

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 09:25:37 GMT

I am not sure but try

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

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:25:52 GMT

How do you know if 2016-04-04T11:16:44.327 is AM or PM? Does it have that information at the end?

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:28:09 GMT

I guess this could work:

=TimeStamp(Floor(Timestamp#(PurgeChar('2016-04-04T11:16:44.327 PM', 'T'), 'YYYY-MM-DDhh:mm:ss.fff TT'), 1/(24*60*60)), 'YYYY-MM-DD hh:mm:ss')

Re: Interpret and format timestamp field

pauljohansson — Tue, 05 Apr 2016 09:30:46 GMT

my misstake, it is actually 24h, example:

2016-04-04T21:08:57.453

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 09:30:50 GMT

I Updated the expression can you check? I don't have qv

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 09:32:39 GMT

then this should work

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

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:34:10 GMT

I think what you had earlier should have worked, but I guess the input just got changed

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:35:06 GMT

This would work:

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

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:40:56 GMT

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

Re: Interpret and format timestamp field

pauljohansson — Tue, 05 Apr 2016 09:49:19 GMT

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.

Paul

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 09:51:44 GMT

Do you need miliseconds too?

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:52:10 GMT

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

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 09:53:17 GMT

then try

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

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 09:57:54 GMT

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

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 09:59:56 GMT

Isn't [.fff] means optional Kush?

Re: Interpret and format timestamp field

magavi_framsteg — Tue, 05 Apr 2016 10:04:17 GMT

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

Re: Interpret and format timestamp field

Kushal_Chawda — Tue, 05 Apr 2016 10:04:30 GMT

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

Re: Interpret and format timestamp field

sunny_talwar — Tue, 05 Apr 2016 10:06:17 GMT

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