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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s