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.

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.

• json
• time
• snowflake-cloud-data-platform

2 Answers 2

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

If you just want the T and Z removed, you can ignore the T and Z by wrapping quotes to exclude them from timestamp format.Note this includes FF part as well

TO_TIMESTAMP(col, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')

Sample

 WITH test AS (
    SELECT '2024-03-21T20:19:57.398Z' AS col
)
SELECT
    TO_TIMESTAMP(col, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') AS format_col
FROM test;

Returns

| FORMAT_COL              |
|-------------------------|
| 2024-03-21 20:19:57.398 |

You can get the YYYY-MM-DD HH:MM:SS like so

WITH test AS (
    SELECT '2024-03-21T20:19:57.398Z' AS col
)
SELECT
    TO_CHAR(TO_TIMESTAMP(col, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'), 'YYYY-MM-DD HH:MI:SS') AS format_col
FROM test;

returns

| FORMAT_COL          |
|---------------------|
| 2024-03-21 20:19:57 |

If its only for presentation you can just alter the session for viewing the desired format

ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';