Redshift: Convert TEXT to Timestamp

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)

Avid learner of technology solutions around databases, big-data, Machine Learning. 5x AWS Certified | 5x Oracle Certified. Connect on Twitter @anandp86