Big Query : how to parse timestamp string into timestamp when timestamp string contains millisecs fraction separated by colon

Question

How to parse to timestamp from a timestamp string with value of milliseconds separated by colon instead of dot? Eg: "2017-08-18 16:04:40:890" I have tried with ('%Y-%m-%d %H:%M:%E3S') but its giving me failed to parse string error.

select parse_timestamp('%Y-%m-%d %H:%M:%E3S','2017-08-18 16:04:40:890')

Answer 1

You need to replace the : with an dot.

Such functions take lots of processing power and slow down queries, you should save the data always in as a date datatype

select parse_timestamp('%Y-%m-%d %H:%M:%E3S'
, REGEXP_REPLACE('2017-08-18 16:04:40:890', r'(.*):([0-9]{3})$', r'\1.\2' ));