CODEWITHSUNDEEP
pentahokettle
bytor99999
bytor99999

Reputation: 991

CSV Timestamp not in 24 hour format

So I have a CSV file which has a field that is a datetime String and it has AM PM at the end of the String. I have it coming in as Date type, with format MM/dd/yyyy HH:mm:ss a

I then use that field in the output to a table to two separate fields in the database MySQL. One which is of type date (A) and the other is of type Time (B). So A and B for simplicity

It seems that all goes well in which the date portion only goes into A, and the time goes into B. Except that the time in B is not in 24 hour time and therefore 1:30 PM in the csv file gets inserted at 01:30 which means it is no longer afternoon, but now early morning.

I tried a calculation, field splitter, formula also and nothing worked to make the time go in as 13:30 so that it is afternoon.

Thanks

Upvotes: 3

Views: 2913

Answers (1)

bolav
bolav

Reputation: 6998

For Kettle to correctly identify a AM/PM string you need to have 12-hour time in your hour slot. Your format should probably be MM/dd/yyyy hh:mm:ss a.

With this string the time should be imported correctly, and put in the database as a 24h time. You might have to convert the format with Select Values first.

Upvotes: 3

Related Questions