What's the calculate logical when a DateTime data which is beyond 2105-12-31 23:59:59 is inserted into ClickHouse

[ggchangan]

BackGround:

1. Table schema
   CREATE TABLE test.dt
   (
   timestamp DateTime,
   event_id UInt8
   )
   ENGINE = TinyLog
2. DateTime
   Supported range of values: [1970-01-01 00:00:00, 2105-12-31 23:59:59].
   https://clickhouse.tech/docs/en/data_types/datetime/

Then, I insert one record using golang like the following:
sql := "insert into test.dt(event_id, timestamp) values(?, ?)"
//2201-04-15 19:58:20
tt := time.Date(2201, 04, 15, 19, 58, 20, 0, time.UTC)
tx, err := conn.Begin()
if err != nil {
return
}
_, err = tx.Exec(sql, 1, tt)
if err != nil {
return
}
if err = tx.Commit(); err != nil {
assert.Nil(t, err)
}
However, I get the following result:
SELECT *
FROM test.dt

┌───────────timestamp─┬─event_id─┐
│ 2065-03-08 13:30:04 │ 1 │
└──────────────── ┴────── ┘
My question is why 2201-04-15 19:58:20 is converted to 2065-03-08 13:30:04 in this situation.
And how 2201-04-15 19:58:20 is converted to 2065-03-08 13:30:04 in this situation.
Thanks!

[den-crane]

Int32 overflow

[ggchangan]

@den-crane Thanks very much for your response. However I still can't get your point. Can you give more details? When the Int32 overflow happens? How the Int32 overflow Happens? Can you give some source code to explain this?

[den-crane]

DateTime -- Int32 -- number of seconds from 1970-01-01 00:00:00 UTC.

2^ 32 / 3600 / 24 /365 ~ 136 years
1970 + 136 = 2106

So !!! Int32 able to store only a period of 136 years -- 1970 -- 2106

(2201-1970)*3600*24*365 = 7284816000 seconds bigger than 2^32 (4294967296)

SELECT toUInt32(7284816000)

┌─toUInt32(7284816000)─┐
│           2989848704 │
└──────────────────────┘

SELECT toDateTime(toUInt32(7284816000))

┌─toDateTime(toUInt32(7284816000))─┐
│              2064-09-28 17:31:44 │
└──────────────────────────────────┘

https://www.epochconverter.com/
2201-04-15 19:58:20  == 7298722700

SELECT toDateTime(toUInt32(7298722700))

┌─toDateTime(toUInt32(7298722700))─┐
│              2065-03-08 16:30:04 │
└──────────────────────────────────┘

SELECT toDateTime(7298722700)

┌─toDateTime(7298722700)─┐
│    2065-03-08 16:30:04 │
└────────────────────────┘

without CH:

https://www.epochconverter.com/ 2201-04-15 19:58:20  == 7298722700
2^32 = 4294967296
7298722700-4294967296 = 3003755404
https://www.epochconverter.com/ 3003755404  == Sunday, March 8, 2065 4:30:04 PM

I suspect this overflow happens before CH in go-driver, but it does not matter at all.

#9404