Timestamp Conversion Issue

[#1]

Hi all,

Looking for some help with what is most likely a simple issue. I have a timestamp in a csv that I am unable to convert to date and time no matter what I attempt. Format is like this:

TIMESTAMP

26/10/2014 01:06:44:340

Any suggestions on how to break out the date and time portions?

I am looking to use the date in a master calendar and eventually round the time into hours for usage analysis.

Thanks

[JonnyPoole]

How about

= date(floor(Timestamp#('26/10/2014 01:06:44:340','DD/MM/YYYY hh:mm:ss:fff')))

or

= date(floor(Timestamp#( [DateTimeField],'DD/MM/YYYY hh:mm:ss:fff')))

[ashfaq_haseeb]

Hi,

Try like this

Load *, date(floor(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Date,

time((Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Time,

hour((Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Hour;

load * Inline

[

TIMESTAMP

26/10/2014 01:06:44:340

]

Regards

ASHFAQ

[jagan · Accepted solution]

Hi David,

Try like this

Data:

Load *,

Date(Floor(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Date,

Timestamp(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'), 'hh:mm:ss')  as Time,

Timestamp(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'), 'hh')  as Hour;

load * Inline

[

TIMESTAMP

26/10/2014 01:06:44:340

]

[vardhancse]

Date(Floor(TIMESTAMP) as Date,

Time(Floor(TIMESTAMP) as Time

[#6]

Thanks Jagan,

All works perfectly with the exception of hour. As I have multiple timstamps, the hours that are returned represent each individual timestamp. So if i have 300 timestamps falling between 1am and 2am, i would like to see only a single entry for 1am. Any thoughts on how to fix this?

David.

[gartigas]

Had you tried with:

Left (TimeStampField,1,10) as Date_,

Left (TimeStampField,12,12) as Time_,

or any other Chain treatment that brings you the derired fields...

I've used when the dates come in text format.---

I hope that it helps you...

Regards

Gustavo

[jagan]

Hi David,

Try this script

Data:

Load *,

Date(Floor(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))) as Date,

Timestamp(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'), 'hh:mm:ss')  as Time,

Hour(Timestamp#( TIMESTAMP,'DD/MM/YYYY hh:mm:ss:fff'))  as Hour;

load * Inline

[

TIMESTAMP

26/10/2014 01:06:44:340

26/10/2014 01:25:44:340

];

Regards,

Jagan.

[ashfaq_haseeb]

Hi l,

Did you try my solution.

Regards

ASHFAQ

[#10]

Works perfectly - thanks Jagan.