Search code examples
jsontimesnowflake-cloud-data-platform

CAST Time on a JSON Extraction Timestamp

I have a cell that contains JSON Data. Can pull out what I need using the following formula

summary:disconnectTimestamp::String AS disconnectTimestamp

The result of this being 2024-03-21T20:19:57.398Z

Trying to figure out a way to get the time without the T and Z. Basically only want Date and H:M:S.

Using Snowflake to process info.

Tried with CAST(summary:disconnectTimestamp::String AS disconnectTimestamp) AS "TIME" didn't seem to work. Getting an error that says

Unsupported data type 'DISCONNECTTIMESTAMP'.

Other options?

Thanks in advance.

Solution

  • You can cast it directly to timestamp_ntz.

    Sample:

    select parse_json('{"summary": {"disconnectTimestamp":"2024-03-21T20:19:57.398Z"}}'::string) as test_json,
test_json:summary.disconnectTimestamp::timestamp_ntz as final_timestamp

    Result

    2024-03-21 20:19:57.398