timestamp VS TIMESTAMP_NTZ in snowflake sql-CodePudding

I am using an sql script to parse a json into a snowflake table using dbt.

One of the cols contain this datetime value: '2022-02-09T20:28:59 0000'.

What's the correct way to define ISO datetime's data type in Snowflake?

I tried date, timestamp and TIMESTAMP_NTZ like this in my dbt sql script:

JSON_DATA:",my_date"::TIMESTAMP_NTZ AS MY_DATE

but clearly, these aren't the correct one because later on when I test it in snowflake with select * , I get this error:

SQL Error [100040] [22007]: Date '2022-02-09T20:28:59 0000' is not recognized

SQL Error [100035] [22007]: Timestamp '2022-02-13T03:32:55 0100' is not recognized

so I need to know which Snowflake time/date data type suits the best for this one

EDIT: This is what I am trying now.

SELECT 
    JSON_DATA:"date_transmission" AS DATE_TRANSMISSION
  , TO_TIMESTAMP(DATE_TRANSMISSION:text, 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM') AS DATE_TRANSMISSION_TS_UTC
    , JSON_DATA:"authorizerClientId"::text AS AUTHORIZER_CLIENT_ID
  , JSON_DATA:"apiPath"::text API_PATH
    , MASTERCLIENT_ID
  , META_FILENAME
  , META_LOAD_TS_UTC
  , META_FILE_TS_UTC
FROM {{ source('INGEST_DATA', 'TABLENAME') }}

I get this error:

000939 (22023): SQL compilation error: error line 6 at position 4
10:21:46    too many arguments for function [TO_TIMESTAMP(GET(DATE_TRANSMISSION, 'text'), 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM')] expected 1, g

However, if I comment out the the first 2 lines(related to timpstamp types), the other two work perfectly fine. What's the correct syntax of parsing json with TO_TIMESTAMP?

Not that JSON_DATA:"apiPath"::text API_PATH gives the correct value for it in my snowflake tables.

CodePudding user response：

Did some testing and it seems you have 2 options.

You can either get rid of the 0000 at the end: left(column_date, len(column_date)-5)

or try_to_timestamp with format try_to_timestamp('2022-02-09T20:28:59 0000','YYYY-MM-DD"T"HH24:MI:SS TZHTZM')

TZH and TZM are TimeZone Offset Hours and Minutes

CodePudding user response：

So there are 2 main points here.

when getting data from JSON to pass to any of the timestamp functions that want a ::TEXT object, but the values to get from JSON are still ::VARIANT so they need to be cast. This is the cause of the error you quote

(22023): SQL compilation error: error line 6 at position 4 10:21:46 too many arguments for function [TO_TIMESTAMP(GET(DATE_TRANSMISSION, 'text'), 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM')] expected 1, g

also your SQL is wrong there it should have been
```
    TO_TIMESTAMP(DATE_TRANSMISSION::text, 
```
How you handle the timezone format.As other have noted you (as I did in your last question) do you want to ignore the timezone values or read them. I forgot about the TZHTZM formatting. Given you have timezone data, you should use the TO_TIMESTAMP_TZ`TRY_TO_TIMESTAMP_TZto make sure the time zone data is keep, given you second example shows 0100`

putting those together (assuming you didn't want an extra date_transmission as a variant in you data) :

SELECT 
    TO_TIMESTAMP_TZ(JSON_DATA:"date_transmission"::text, 'YYYY-MM-DDTHH24:MI:SS TZHTZM') AS DATE_TRANSMISSION_TS_UTC
    , JSON_DATA:"authorizerClientId"::text AS AUTHORIZER_CLIENT_ID
    , JSON_DATA:"apiPath"::text AS API_PATH
    , MASTERCLIENT_ID
    , META_FILENAME
    , META_LOAD_TS_UTC
    , META_FILE_TS_UTC
FROM {{ source('INGEST_DATA', 'TABLENAME') }}

CodePudding user response：

You should use timestamp (not date which does not store the time information), but probably the format you are using is not autodetected. You can specify the input format as YYYY-MM-DD"T"HH24:MI:SSTZHTZM as shown here. The autodetected one has a : between the TZHTZM.