CODEWITHSUNDEEP
pythonpandasnumpydatetimesnowflake-cloud-data-platform
MoneyBall
MoneyBall

Reputation: 2573

convert nanosecond precision datetime to snowflake TIMESTAMP_NTZ format

I have a string datetime "2017-01-01T20:19:47.922596536+09". I would like to convert this into snowflake's DATETIME_NTZ date type (which can be found here). Simply put, DATETIME_NTZ is defined as

TIMESTAMP_NTZ TIMESTAMP_NTZ internally stores “wallclock” time with a specified precision. All operations are performed without taking any time zone into account. If the output format contains a time zone, the UTC indicator (Z) is displayed. TIMESTAMP_NTZ is the default for TIMESTAMP. Aliases for TIMESTAMP_NTZ: TIMESTAMPNTZ TIMESTAMP WITHOUT TIME ZONE

I've tried using numpy.datetime64 but I get the following:

> numpy.datetime64("2017-01-01T20:19:47.922596536+09")
numpy.datetime64('2017-01-01T11:19:47.922596536')

This for some reason converts the time to certain timezone.

I've also tried pd.to_datetime:

> pd.to_datetime("2017-01-01T20:19:47.922596536+09")
Timestamp('2017-01-01 20:19:47.922596536+0900', tz='pytz.FixedOffset(540)')

This gives me the correct value but when I try to insert the above value to snowflake db, I get the following error:

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 252004: Failed processing pyformat-parameters: 255001: Binding data in type (timestamp) is not supported.

Any suggestions would be much appreciated!

Upvotes: 1

Views: 966

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11066

You can do this on the Snowflake side if you want by sending the string format as-is and converting to a timestamp_ntz. This single line shows two ways, one that simply strips off the time zone information, and one that converts the time zone to UTC before stripping off the time zone.

select try_to_timestamp_ntz('2017-01-01T20:19:47.922596536+09',
                            'YYYY-MM-DD"T"HH:MI:SS.FF9TZH') TS_NTZ
      ,convert_timezone('UTC', 
          try_to_timestamp_tz('2017-01-01T20:19:47.922596536+09',
                              'YYYY-MM-DD"T"HH:MI:SS.FF9TZH'))::timestamp_ntz UTC_TS_NTZ
;

Note that Snowflake UI by default only shows 3 decimal places (milliseconds) unless you specify higher precision for the output display using to_varchar() and a timestamp format string.

TS_NTZ UTC_TS
2017-01-01 20:19:47.922596536 2017-01-01 11:19:47.922596536

Upvotes: 0

Related Questions