failed defining the timestamp from csv file

Question

I am trying to store data into my Athena DB from S3 and I have date time format that looks like this :-

20171011133902

I am trying to set format to timestamp but it is not recognizable, thus no data was inserted into the table. I set the the format as bigint just to insert the data and my query looks something like this.

CREATE EXTERNAL TABLE IF NOT EXISTS default.elb_logs ( 'request_timestamp' bigint, 'id' int, .....)

I tried to convert in Quicksight, however when I edit data fields and change it to date, i became like this :-

2033-12-02T01:51:53.000Z

Can someone help me on how I can handle this type of date format?

Answer 1

I recommend loading your date time as a string, then parsing it to a timestamp in your select queries using the parse_datetime function. For JSON data like:

{"dt": "20171011133902", ... }

And your date/time field defined as a string:

CREATE EXTERNAL TABLE scratch.test_dates (
  `dt` string
)
...

A query using parse_datetime to reformat dt as a proper timestamp:

SELECT
  parse_datetime(dt, 'YYYYMMddHHmmss') as parsed_date
FROM 
  scratch."test_dates" 

Will yield a timestamp with timezone (as UTC in your case):

2017-10-11 13:39:02.000 UTC