How to load CSV data into MYSQL to get a TIME datatype in 24 HR format

Question

I am trying to load data from a csv file into MYSQL. One of the columns in the csv table contains data related to time. The data is stored in the column titled Issue_Time with time values such as as 07:30A and 01:35P, where A and P represent AM and PM. The problem I have is that I cannot get the data to load into MYSQL in a format where I can get both the AM and PM times as a TIME data type or in 24 hr format.

I first tried loading the data in MYSQL using the TIME data type. I was only able to get the data to load by first using this code.

SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'; 

When the data loaded, the data were transformed to time format with times as 07:30:00 and 01:35:00. The problem with this is that the AM and PM are no longer represented. I therefore now just have times between 00:00:00 and 12:59:00. I tried to convert this to 24Hr form using code

UPDATE my_table SET Issue_Time = TIME_FORMAT(Issue_Time, '%H:%i:%s')

but this did not work.

I also tried loading the data first as VARCHAR(255) and then converting to TIME using

UPDATE my_table SET Issue_Time = STR_TO_DATE(Issue_Time, '%H:%i%p')

This resulted in a column of NULL values being loaded.

Can someone help me get the data into MYSQL in either 24 Hr time format ( such as, 13:35:00) or in a form where I will be able to tell the times AM from PM? Thanks.

Answer 1

Assuming your are using LOAD DATA INFILE to import your CSV, you can use Input Preprocessing to format the incoming time value with STR_TO_DATE(). And because you only have the A and P after the time, we will CONCAT() to add the missing M. We don't know the structure of your file or table, so here is an example:

LOAD DATA INFILE 'data.csv' INTO TABLE my_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
    (id, name, @time)
    SET Issue_Time = STR_TO_DATE(CONCAT(@time, 'M'), '%h:%i%p')