How do you convert TEXT to timestamp in redshift?
If the score column has data in given format, how can you display the timestamp.
{"Choices":null, "timestamp":"1579650266955", "scaledScore":null}
select cast(json_extract_path_text(score, 'timestamp') as timestamp) from schema.table limit 10;
This sql will fail with —
ERROR: Invalid data DETAIL: ----------------------------------------------- error: Invalid data code: 8001 context: Invalid format or data given: 1579650266955 query: 2057693 location: funcs_timestamp.cpp:261 process: query1_120_2057693 [pid=6659] -----------------------------------------------
In order to extract the timestamp correctly, you can use the below sql –
select timestamp 'epoch' + cast(json_extract_path_text(score, 'timestamp') as bigint)/1000 * interval '1 second' as timestamp from schema.table limit 5; +---------------------+ | timestamp | +---------------------+ | 2020-01-22 00:35:43 | | 2020-01-17 20:20:52 | | 2020-02-01 01:27:08 | | 2020-01-07 07:20:12 | | 2019-12-09 19:52:47 | +---------------------+ (5 rows)