2

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!

user665997
  • 313
  • 1
  • 4
  • 18

2 Answers2

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');
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • question in the load data portion I have encountered a bunch of errors with the path format this is what I typically put in the quotes 'C:\Users\Alan Cedeno\Desktop\TestFilesForSohl-Aerostat160215 Version 2.0\TestFilesForAlan-Aerostat160215\Aerostat160215\MillCreek(Particle Counter) 2-15-16\HiSAM160215\1-28-16' and I get and error saying there is not path or directory – user665997 Apr 13 '16 at 03:39
  • This is not MySQL related error. You need to find the correct path for your csv file. To have a try, you can copy your .csv file to C:\data.csv and have a try. – Dylan Su Apr 13 '16 at 03:41
  • ERROR: syntax error at or near "data" LINE 1: load data infile 'C:\HiSAM1_data_160206_115813' – user665997 Apr 13 '16 at 03:46
  • are you using pg or mysql? – Dylan Su Apr 13 '16 at 03:52
  • I am using postgres so pg – user665997 Apr 13 '16 at 03:55
  • Then this is not the ans you want. Refer to http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table – Dylan Su Apr 13 '16 at 04:25
0

if your creating a table for timestapm, just use this..

CREATE TABLE IF NOT EXIST 'Particle_counter_HiSam' 
{

'date_log' timestamp NOT NULL,

}

hope this help..

DrkWhz24
  • 1
  • 6