Trying to read mm/dd/yyyy format in sql while creating table

Question

So I am very new in SQL and I am trying to create a table where I will later import a .csv file. In this table there is a time stamp column that I want to set it up to read mm/dd/yyyy hh:mi:ss, yet I've tried doing this:

 create table Particle_counter_HiSam ( time_utc  timestamp(m/d/Y hh:mi:ss),...

and i get this error

 ERROR:  syntax error at or near "m"

I just can't seem to figure this out.

Any help will do. Thanks!

Answer 1

Create the table as normal timestamp and use SET with STR_TO_DATE in load data infile as below.

-- table definition
create table Particle_counter_HiSam ( time_utc  timestamp, ... );

-- load data
load data infile 'data.csv' 
into table Particle_counter_HiSam
fields terminated BY ',' ESCAPED BY ""
lines terminated by '\r\n'
(@var1, c2, ....)
SET time_utc = STR_TO_DATE(@var1,'%m/%d/%Y %H:%i:%S');