CODEWITHSUNDEEP
mysql
user8846809
user8846809

Reputation:

How to import time in HH:MM format in MySQL?

I have a dataset as follows,

Date,Time,ID,Name,Count

01-MAY-2009,00:00,4,Town Hall (West),209

01-MAY-2009,02:00,17,Collins Place (South),28

01-MAY-2009,23:00,18,Collins Place (North),36

For this I have created table with following schema,

CREATE TABLE table_name(date DATE, time TIME, id int, name VARCHAR(50), count int);

And for loading the table from ".csv" file as,

LOAD DATA INFILE '/home/cloudera/dataset.csv' 
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(@var1, @var2, id, name, count) 
SET date = STR_TO_DATE(@var1, '%d-%b-%Y') 
SET time = TIME(@var2, "%H:%i");

But I get an error as,

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET time = TIME(@var2, "%H:%i")' at line 1

I can't understand what the error is. I have tried going through MySQL website and documentation, but can't make out what the correct format is. Can anyone please me. Thanks in advance.

Upvotes: 1

Views: 904

Answers (2)

Shadow
Shadow

Reputation: 34304

Tim is right in pointing out that you do not need to convert your time data in the load data infile statement.

Just to answer why you get a syntax error: load data infile can only have a single set clause, in which assignments to multiple columns are separated by comma. Your code has multiple set clauses, hence it fails.

Also, the time() function does not have a 2nd parameter for a pattern. The function you need to use is called str_to_date().

So, it should look like as follows:

...
SET date = STR_TO_DATE(@var1, '%d-%b-%Y'), time = TIME(str_to_date(@var2, "%H:%i"));

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I don't think that you even need to be using the TIME function here. Your current hour:minute string time literals should be good enough, q.v. the documentation:

Be careful about assigning abbreviated values to a TIME column. MySQL interprets abbreviated TIME values with colons as time of the day. That is, '11:12' means '11:12:00', not '00:11:12'

As valid literals, your times would be interpreted as having a zero second component. So, if you just insert those time strings as is, I think it should work. Try the following code:

LOAD DATA INFILE '/home/cloudera/dataset.csv' 
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(@var1, Time, id, name, count) 
SET date = STR_TO_DATE(@var1, '%d-%b-%Y');

Upvotes: 1

Related Questions