CODEWITHSUNDEEP
snowflake-cloud-data-platform
Rahul
Rahul

Reputation: 409

Issues with timestamp_ltz datatypes

Observing a weird issue in Snowflake. for below table the results for all years before 1884-01-01 00:00:00.000001000 are showing different values

 CREATE TABLE time_tester1(time1 TIMESTAMP_LTZ, time2 TIMESTAMP_LTZ)
 INSERT INTO time_tester1
 values('1884-01-01 00:00:00.000001000','1883-01-01 00:00:00.000001000')

Result

SELECT * FROM time_tester1

Row     TIME1                                               TIME2
1       1884-01-01 00:00:00.000001000 -05:00                1882-12-31 23:56:02.000001000 -05:00

this issue is observed for all years less then 1884

Upvotes: 0

Views: 149

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10059

It seems the issue is related with "The Day of Two Noons":

https://dba.stackexchange.com/a/127972

If you use UTC, you will not get this result:

CREATE or replace TABLE time_tester1(time1 TIMESTAMP_LTZ, time2 TIMESTAMP_LTZ);
INSERT INTO time_tester1
values('1884-01-01 00:00:00.000001000','1883-01-01 00:00:00.000001000');

SELECT * FROM time_tester1;

+-------------------------------+-------------------------------+
| TIME1                         | TIME2                         |
+-------------------------------+-------------------------------+
| 1884-01-01 00:00:00.000 +0000 | 1883-01-01 00:00:00.000 +0000 |
+-------------------------------+-------------------------------+

Upvotes: 3

Related Questions