Can I output datetime string to Redshift and make the column as timestamp?

Question

I need to create a table in Redshift using the data source in S3. There are a couple of columns have datetime like string value. I want the Redshift treats it like timestamp. If I just assign timestamp to those columns during the table creation, would it work or does it need additional steps?

The reason I have those columns as string is those columns were created by Python code and run into json serialization problem at first :How to overcome "datetime.datetime not JSON serializable"?

My input data is:

{
"created_date": "2019-06-21 23:32:28",
"resolved_date": ""
"last_updated_date": "2019-06-23 04:08:35"
}

In Redshift, I want to make the columns in timestamp fomat

"created_date": timestamp,
"resolved_date": timestamp,
"last_updated_date": timestamp

Will Redshift recognize my data and parse it as timestamp?

Any pointers are appreciated.

Answer 1

When using Redshift spectrum you can map string json fields to TIMESTAMP columns directly, but your example would not work, because of the empty string for a timestamp - that's not parsable - you'd have to make sure that your python code generates proper JSON nulls.

That would be your external table:

CREATE EXTERNAL TABLE my_external_schema.my_external_table(
    "created_date": timestamp,
    "resolved_date": timestamp,
    "last_updated_date": timestamp
  )
  ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
  STORED AS TEXTFILE
  LOCATION 's3://my_external_location/'

if you really need that data loaded in redshift, you could then do:

CREATE TABLE my_schema.my_table AS SELECT * FROM my_external_schema.my_external_table;

Another option is to use Redshift COPY command to load the data. It should also be able to treat json string as TIMESTAMP, and there I believe you may be able to specify to treat "" as NULLs.