Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

pauljohansson
Not applicable

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:MMSmiley FrustratedS:FFF TT'), 'YYYY-MM-DD HH:MMSmiley FrustratedS')

But it doesnt give any reuslt.  Any pointers?

thanks in advance,
Paul

1 Solution

Accepted Solutions
kush141087
Not applicable

Re: Interpret and format timestamp field

then this should work


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

25 Replies
sunny_talwar
Not applicable

Re: Interpret and format timestamp field

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


Capture.PNG

kush141087
Not applicable

Re: Interpret and format timestamp field

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


sunny_talwar
Not applicable

Re: Interpret and format timestamp field

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

sunny_talwar
Not applicable

Re: Interpret and format timestamp field

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

pauljohansson
Not applicable

Re: Interpret and format timestamp field

my misstake, it is actually 24h, example:

2016-04-04T21:08:57.453

kush141087
Not applicable

Re: Interpret and format timestamp field

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

kush141087
Not applicable

Re: Interpret and format timestamp field

then this should work


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

sunny_talwar
Not applicable

Re: Interpret and format timestamp field

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

sunny_talwar
Not applicable

Re: Interpret and format timestamp field

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


Capture.PNG