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.
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?