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)