importing different types of date/time values-CodePudding

I have a csv file where date/time is written as 20181014042709 which means 2018-10-14 04:27:09

I tried to use SET datestyle command but I didn't find anything that suited.

Is there a way to properly import this data type to a timestamp column?

Thanks

CodePudding user response：

Convert it to a recognized format before you import.
Import into staging table where the column is varchar and then transform the string using to_timestamp like:

select to_timestamp('20181014042709', 'YYYYMMDDHH24MISS');
  to_timestamp      
------------------------
 2018-10-14 04:27:09-07

--The -07 is because:
show timezone;
  TimeZone  
------------
 US/Pacific

when moving the data to final table.

The patterns in format string 'YYYYMMDDHH24MISS' are explained in the link I provided above in section Table 9.26. Template Patterns for Date/Time Formatting.