0

I have a score column with json value -

{"Choices":null,
"timestamp":"1579650266955",
"scaledScore":null}

I am using the below sql to retrive the timestamp value --

select json_extract_path_text(score, 'timestamp') from schema.table limit 10;

Now I need to convert it to actual timestamp. I have been trying few things, but all of them is failing 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]
  -----------------------------------------------

SQLs I have tried --

select cast(json_extract_path_text(score, 'timestamp') as timestamp) from schema.table limit 10;

select cast(replace(json_extract_path_text(score, 'timestamp'), 'T','') as timestamp) from schema.table limit 10;


select timestamp 'epoch' + json_extract_path_text(score, 'timestamp') * interval '1 second' from schema.table limit 10;
ERROR:  operator does not exist: text * interval
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
Time: 451.429 ms
Anand
  • 145
  • 1
  • 3
  • 10
  • This worked `select timestamp 'epoch' + cast(json_extract_path_text(score, 'timestamp') as bigint)/1000 * interval '1 second' from schema.table limit 10;` – Anand Feb 14 '20 at 21:08

1 Answers1

0

Below SQL worked --

select timestamp 'epoch' + cast(json_extract_path_text(score, 'timestamp') as bigint)/1000 * interval '1 second' from schema.table limit 10;
Anand
  • 145
  • 1
  • 3
  • 10