Date time64 extended range by Enmk · Pull Request #9404 · ClickHouse/ClickHouse

Enmk · 2020-02-27T07:34:14Z

I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):
Extended range of DateTime64 to properly support dates from year 1925 to 2283. Improved support of DateTime around zero date (1970-01-01).
...

Detailed description / Documentation draft:
The Year 1925 is a starting point because most of the timezones switched to saner (mostly 15-minutes based) offsets somewhere during 1924 or before. And that significantly simplifies implementation.

2238 is to simplify arithmetics for sanitizing LUT index access; there are less than 0x1ffff days from 1925.

Extended DateLUTImpl internal LUT to 0x1ffff items, some of which represent negative (pre-1970) time values.
As a collateral benefit, Date now correctly supports dates up to 2149 (instead of 2106).
Added a new strong typedef ExtendedDayNum, which represents dates pre-1970 and post 2149.
Functions that used to return DayNum now return ExtendedDayNum.
Refactored DateLUTImpl to untie DayNum from the dual role of being a value and an index (due to negative time). Index is now a different type LUTIndex with explicit conversion functions from DatNum, time_t, and ExtendedDayNum.
Updated DateLUTImpl to properly support values close to epoch start (1970-01-01 00:00), including negative ones.
Reduced resolution of DateLUTImpl::Values::time_at_offset_change to multiple of 15-minutes to allow storing 64-bits of time_t in DateLUTImpl::Value while keeping same size.
Minor performance updates to DateLUTImpl when building month LUT by skipping non-start-of-month days.
Fixed extractTimeZoneFromFunctionArguments to work correctly with DateTime64.
New unit-tests and stateless integration tests for both DateTime and DateTime64.

Progress:

Larger LUT in DateLUTImpl that supports negative time too
Cleanup
Add more high-level tests (⚠️ Help needed ⚠️)
Fix remaining tests

closes #7316

base/common/DateLUTImpl.cpp

base/common/DateLUTImpl.h

4ertus2 · 2020-05-29T13:15:49Z

base/common/DateLUTImpl.h

static constexpr const unsigned seconds_in_day = 86400; ?

base/common/LocalDateTime.h

base/common/TimeZone.h

src/DataTypes/DataTypeDateTime.cpp

4ertus2 · 2020-05-29T14:10:32Z

src/Functions/DateTimeTransforms.h

Do we only change tabs here?

src/Functions/DateTimeTransforms.h

base/common/TimeZone.h

alexey-milovidov · 2021-03-15T17:03:07Z

Good news are that most of DateTime functions are not slowed down.

alexey-milovidov · 2021-03-15T17:35:18Z

Parsing does not work correctly:

SELECT toDateTime64('1971-01-01 00:00:00', 0) - toIntervalYear(45)

Query id: 9387ca2b-6243-4bfa-b615-3cfee663d747

┌─minus(toDateTime64('1971-01-01 00:00:00', 0), toIntervalYear(45))─┐
│                                               1926-01-01 00:00:00 │
└───────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 

milovidov-desktop :) SELECT toDateTime64('1926-01-01 00:00:00', 0)

SELECT toDateTime64('1926-01-01 00:00:00', 0)

Query id: 4c999cd5-9300-4d6f-86d7-7057bcf1627d

┌─toDateTime64('1926-01-01 00:00:00', 0)─┐
│                    2062-02-06 07:28:16 │
└────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

alexey-milovidov · 2021-03-15T18:10:22Z

Fixed.

alexey-milovidov · 2021-03-15T18:13:41Z

toStartOfDay does not work properly
toRelativeDayNum should be extended from UInt16

This is due to the limitations of DateTime / Date. Won't be fixed in this PR.

alexey-milovidov · 2021-03-15T18:16:16Z

toStartOfTenMinutes is slowed down only in tz with fractional offset (Asia/Kolkata) - passable.

alexey-milovidov · 2021-03-16T09:30:17Z

Now performance is almost break even.

alexey-milovidov · 2021-03-16T09:35:59Z

@KrishnaPG I also have an idea that we can make a LUT of 16777216 second intervals (it is about 194 days) and then assume that there is at most one time transition during this interval (daylight saving time or global change). In the LUT cell we will record an offset to the beginning of the year from beginning of the interval, offset to the transition and the amount of transition if any, year number and a flag if it is a leap year. Then everything is calculated with simple arithmetic and a few branches.

Unfortunately it looks less efficient than our current LUT by days (but I did not try).

For example, the "round time to midnight" is performed in about 300 000 000 iterations per second on single CPU core and I'm not going to lose this performance.

alexey-milovidov · 2021-03-16T09:42:31Z

Another idea is to place a pointer to virtual table in the LUT cells. Basically the idea is to make LUTCell a polymorphic class and allocate it in LUT with placement new. It will make simple cases very cheap in cost of one indirect function call.

Clickhouse DateTime64 extended range ClickHouse/ClickHouse#9404

blinkov added the doc-alert label Feb 27, 2020

Enmk commented Feb 27, 2020

View reviewed changes

base/common/DateLUTImpl.cpp Outdated Show resolved Hide resolved

alexey-milovidov added the can be tested label Feb 28, 2020

den-crane mentioned this pull request Feb 29, 2020

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

Closed

Enmk mentioned this pull request Mar 9, 2020

Date time formatting tests #9567

Merged

Enmk changed the title ~~WIP: Date time64 extended range~~ Date time64 extended range Mar 31, 2020

blinkov added the pr-feature Pull request with new product feature label Apr 1, 2020

blinkov assigned alexey-milovidov and vitlibar Apr 1, 2020

blinkov requested review from alexey-milovidov and vitlibar April 2, 2020 05:32

blinkov unassigned vitlibar Apr 2, 2020

filimonov mentioned this pull request Apr 13, 2020

Does not support Max/Min DateTime value #10229

Closed

filimonov added the altinity label Apr 30, 2020

alexey-milovidov removed their assignment May 13, 2020

4ertus2 self-assigned this May 18, 2020

4ertus2 reviewed May 29, 2020

View reviewed changes

base/common/TimeZone.h Outdated Show resolved Hide resolved

den-crane mentioned this pull request Jun 4, 2020

Store data oldest than 1970 #11441

Closed

alexey-milovidov force-pushed the master branch from 6c77191 to 09b9a30 Compare June 9, 2020 02:04

den-crane mentioned this pull request Sep 22, 2020

What data type should I use to store date before 1970-01-01 00:00:00? #15126

Closed

filimonov added pr-backport Changes, backported to release branch. Do not use manually - automated use only! and removed pr-backport Changes, backported to release branch. Do not use manually - automated use only! labels Sep 24, 2020

4ertus2 removed their assignment Sep 29, 2020

zvonand mentioned this pull request Sep 30, 2020

DateTime64 wrong seconds value prior to 1920 #15486

Closed

zvonand mentioned this pull request Oct 9, 2020

Date and time functions fail when DateTime64 is out of normal range #15784

Closed

This was referenced Oct 21, 2020

DateTime64 extended range fails when date is 1969-12-31 and timezone below UTC #16222

Closed

timeSlots() does not support DateTime64 #16260

Closed

zvonand mentioned this pull request Nov 1, 2020

Some Int conversion functions unsupported #16581

Closed

zvonand mentioned this pull request Nov 12, 2020

Some IANA tz timezone shifts are unsupported #16924

Closed

alexey-milovidov added 2 commits March 15, 2021 20:36

Fix UBSan report in rounding to years intervals

badd516

Fix parsing of pre-epoch time

845f4af

alexey-milovidov added 4 commits March 15, 2021 22:23

Most likely improve performance

671395e

Fix issue

f48bf2a

Remove bad code

31d7f10

Saturation for DateTime

3f67f4f

alexey-milovidov mentioned this pull request Mar 16, 2021

Roadmap 2021 (discussion) #17623

Closed

alexey-milovidov added 5 commits March 16, 2021 14:07

Build fixes

eadf024

Merge branch 'master' into DateTime64_extended_range

ea049b2

Fix UBSan report

de09111

Fix perf test

1dd730d

Fix clang-tidy

637b683

alexey-milovidov merged commit d02726b into ClickHouse:master Mar 17, 2021

This was referenced Mar 22, 2021

Tehran wrong TZ info (+0430) for summer DST #21991

Closed

data not from oracle in date 2020-03-21 (Daylight Saving Time) #13845

Closed

olgarev mentioned this pull request Apr 4, 2021

DOCSUP-8219: DateTime64 extended range #22619

Merged

0x4ec7 added a commit to 0x4ec7/clickhouse-driver that referenced this pull request Jun 2, 2021

Updated: Clickhouse DateTime64 extended range

456f65c

Clickhouse DateTime64 extended range ClickHouse/ClickHouse#9404

This was referenced Jun 2, 2021

Updated: Clickhouse DateTime64 extended range 0x4ec7/clickhouse-driver#1

Merged

Clickhouse DateTime64 extended range mymarilyn/clickhouse-driver#222

Merged

xzkostyan pushed a commit to mymarilyn/clickhouse-driver that referenced this pull request Jun 4, 2021

Clickhouse DateTime64 extended range (#222)

0e7f0b2

Clickhouse DateTime64 extended range ClickHouse/ClickHouse#9404

UnamedRus mentioned this pull request Sep 7, 2021

Extend range of Datetime64 and Date32 #28216

Closed

azat mentioned this pull request Feb 7, 2022

Saturate date/datetime to zero. #29953

Merged

Conversation

Enmk commented Feb 27, 2020 • edited by KochetovNicolai Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

4ertus2 May 29, 2020

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

4ertus2 May 29, 2020

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

alexey-milovidov commented Mar 15, 2021

Uh oh!

alexey-milovidov commented Mar 15, 2021

Uh oh!

alexey-milovidov commented Mar 15, 2021

Uh oh!

alexey-milovidov commented Mar 15, 2021

Uh oh!

alexey-milovidov commented Mar 15, 2021

Uh oh!

alexey-milovidov commented Mar 16, 2021

Uh oh!

alexey-milovidov commented Mar 16, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

alexey-milovidov commented Mar 16, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

9 participants

Enmk commented Feb 27, 2020 •

edited by KochetovNicolai

Loading

alexey-milovidov commented Mar 16, 2021 •

edited

Loading

alexey-milovidov commented Mar 16, 2021 •

edited

Loading