0

I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the hundredths or thousandths place in the same column & same file.

Example:

rx_timestamp
2019-07-11 16:54:27.45
2019-07-11 16:54:27.556

Currently, my solution is as follows:

COALESCE(
  TRY(DATE(date_parse(rx_timestamp, '%Y-%m-%d %H:%i:%S.%f'))),
  TRY(DATE(date_parse(rx_timestamp, '%Y-%m-%d %H:%i:%S'))))

Is there a more efficient way to parse the date or dateparts from this column?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.