CODEWITHSUNDEEP
pythonpandasdatetimetimestampsnowflake-cloud-data-platform
user12637176
user12637176

Reputation: 47

Snowflake displaying wrong Date

I'm trying to write date column from my python dataframe to snowflake. However, snowflake is displaying wrong values/year in the table.

Example:

I try to insert: 2010-10-15T00:00:00.000Z

What snowflake displays: 40788570-09-06 17:00:00.000

When the same is passed in String datatype, it displays the datetime correctly. Can someone please guide, Thank you.

Upvotes: 0

Views: 1318

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26043

Snowflake has a number of ways to parse epoch timestamps, I am not familiar with the panda/python side how to make it happy, but can show you what is happening.

SELECT '2010-10-15T00:00:00.000Z'::timestamp as t1,
    '40788570-09-06 17:00:00.000'::timestamp as t2,
    date_part(epoch_second, t1) as e1,
    date_part(epoch_second, t2) as e2,
    to_timestamp(e1) as tt1,
    to_timestamp(e2, 6) as tt2;
T1 T2 E1 E2 TT1 TT2
2010-10-15 00:00:00.000 Invalid date 1,287,100,800 1,287,100,799,974,800 2010-10-15 00:00:00.000 2010-10-14 23:59:59.974

basically you python date is epoch_microsecond, and the default transform is treating it as epoch seconds, so when it's parsed with the correct level it's more or less correct (it's wrong here due to coverting the wildly future date to s and then read that is us)..

Upvotes: 1

Related Questions