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

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

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

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

[swuehl]

Without testing, but shouldn't it be

[.fff]

instead of

.[fff]

[sunny_talwar]

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