Introduce TIMESTAMP type to Tarantool #5941

kyukhin · 2021-03-26T09:45:37Z

Most of modern DBMS contain such a type. Let's do that as well.

Need to:

Add new extensions to Msgpack Add TIMESTAMP and INTERVAL extensions to Msgpack #5946
(possibly) re-use icu-date for manipulation routines Choose the fastest internal representation of DATE/TIME/DATETIME #5947
Olson/timezones database support datetime: Olson/timezones database support #6751
Add datetime types support to SQL (datetime, sql: datetime support in sql #6773, sql: introduce conventional <DATE> and <TIME> types #3694)

mumez · 2021-07-14T15:11:10Z

MessagePack specification now has a Timestamp extension type. So I think it would be worth looking at the section.
https://github.com/msgpack/msgpack/blob/master/spec.md#timestamp-extension-type

* created a new Tarantool built-in module `datetime`; * register cdef types for this module; * export some `dt_*` functions from `c-dt` library; * lua implementationis of `asctime` and `strftime`; * datetime parsing unit tests, with and withput timezones; * c test for reversible strftime roundtrip; Part of tarantool#5941

* created app-tap test for new builtin module `datetime.lua` * added case to check datetime string formatting using: - asctime (gmt time); - ctime (local TZ time); - strftime (using given format). * added positive/negative checks to datetime test - extended api of datetime.parse_date, .parse_time, .parse_time_zone with a length of parsed (sub)string; - this allows us to check partially valid strings like "20121224 Foo bar". Part of tarantool#5941

* introduced output routine for converting datetime to their default output format. * use this routine for tostring() in datetime.lua Part of tarantool#5941

Serialize datetime_t as newly introduced MP_EXT type. It saves 1 required integer field and upto 2 optional unsigned fields in very compact fashion. - secs is required field; - but nsec, offset are both optional; * json, yaml serialization formats, lua output mode supported; Part of tarantool#5941 Part of tarantool#5946

* storage hints implemented for datetime_t values; * proper comparison for indices of datetime type. Part of tarantool#5941 Part of tarantool#5946

* correct incorrect encoding of MP_EXT sizes for datetime messagepack values; * export necessary symbols for datetime messagepack size calculations so they will be available for Lua consumption. Part of tarantool#5941

* introduced a set of calculated attributes to data object, e.g.: - timestamp, seconds, microseconds, minute, or hours Part of tarantool#5941

* created few entry points (months(N), years(N), days(N), etc.) for easier datetime arithmetic; * additions/subtractions of years/months use `dt_add_years()` and `dt_add_months()` from 3rd party c-dt library; * also there are `:add{}` and `:sub{}` methods in datetime object to add or substract more complex intervals; * introduced `is_datetime()` and `is_interval()` helpers for checking of validity of passed arguments; * human-readable stringization implemented for interval objects. Note, that additions/subtractions completed for all _reasonable_ combinations of values of date and interval types; Time + Interval => Time Interval + Time => Time Time - Time => Interval Time - Interval => Time Interval + Interval => Interval Interval - Interval => Interval Part of tarantool#5941

* implemented proper range checks for date attributes values; * created `.unixtime` attribute, which is alias to `.secs`, with corresponding setter/getter; * similarly to `unixtime`, created virtual `timestamp` attribute setter. Which is a convenient way to simultaneously assign unixtime (seconds since epoch) and nanoseconds Part of tarantool#5941

Introduced new date/time/interval types support to lua and storage engines. Closes tarantool#5941 Closes tarantool#5946

* created app-tap test for new builtin module `datetime.lua` * added case to check datetime string formatting using: - asctime (gmt time); - ctime (local TZ time); - strftime (using given format). * added positive/negative checks to datetime test - extended api of datetime.parse_date, .parse_time, .parse_time_zone with a length of parsed (sub)string; - this allows us to check partially valid strings like "20121224 Foo bar". Part of tarantool#5941

* introduced output routine for converting datetime to their default output format. * use this routine for tostring() in datetime.lua Part of tarantool#5941

Tarantool supports datetime type since version 2.10.0 [1]. This patch introduced the support of Tarantool datetime type in msgpack decoders and encoders. Tarantool datetime objects are decoded to `tarantool.Datetime` type. `tarantool.Datetime` objects may be encoded to Tarantool datetime objects. `tarantool.Datetime` is basically a `pandas.Timestamp` wrapper. You can create `tarantool.Datetime` objects - from `pandas.Timestamp` object, - by using the same API as in `pandas.Timestamp()` [2], - from another `tarantool.Datetime` object. To work with datetime data as a `pandas.Timestamp`, convert `tarantool.Datetime` object to a `pandas.Timestamp` with `to_pd_timestamp()` method call. You can use this `pandas.Timestamp` object to build a `tarantool.Datetime` object before sending data to Tarantool. To work with data as `numpy.datetime64` or `datetime.datetime`, convert to a `pandas.Timestamp` and then use `to_datetime64()` or `to_datetime()` converter. `pandas.Timestamp` was chosen to store data because it could be used to store both nanoseconds and timezone information. In-build Python `datetime.datetime` supports microseconds at most, `numpy.datetime64` do not support timezones. Tarantool datetime interval type is planned to be stored in custom type `tarantool.Interval` and we'll need a way to support arithmetic between datetime and interval. This is the reason we use custom class instead of plain `pandas.Timestamp`. This patch does not yet introduce the support of timezones in datetime. 1. tarantool/tarantool#5941 2. https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html Part of #204

Tarantool supports datetime type since version 2.10.0 [1]. This patch introduced the support of Tarantool datetime type in msgpack decoders and encoders. Tarantool datetime objects are decoded to `tarantool.Datetime` type. `tarantool.Datetime` may be encoded to Tarantool datetime objects. `tarantool.Datetime` stores data in a `pandas.Timestamp` object. You can create `tarantool.Datetime` objects either from msgpack data or by using the same API as in Tarantool: ``` dt1 = tarantool.Datetime(year=2022, month=8, day=31, hour=18, minute=7, sec=54, nsec=308543321) dt2 = tarantool.Datetime(timestamp=1661969274) dt3 = tarantool.Datetime(timestamp=1661969274, nsec=308543321) ``` `tarantool.Datetime` exposes `year`, `month`, `day`, `hour`, `minute`, `sec`, `nsec` and `timestamp` properties if you need to convert `tarantool.Datetime` to any other kind of datetime object: ``` pdt = pandas.Timestamp(year=dt.year, month=dt.month, day=dt.day, hour=dt.hour, minute=dt.minute, second=dt.sec, microsecond=(dt.nsec // 1000), nanosecond=(dt.nsec % 1000)) ``` `pandas.Timestamp` was chosen to store data because it could be used to store both nanoseconds and timezone information. In-build Python `datetime.datetime` supports microseconds at most, `numpy.datetime64` do not support timezones. Tarantool datetime interval type is planned to be stored in custom type `tarantool.Interval` and we'll need a way to support arithmetic between datetime and interval. This is the main reason we use custom class instead of plain `pandas.Timestamp`. It is also hard to implement Tarantool-compatible timezones with full conversion support without custom classes. This patch does not yet introduce the support of timezones in datetime. 1. tarantool/tarantool#5941 2. https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html Part of #204

Tarantool supports datetime interval type since version 2.10.0 [1]. This patch introduced the support of Tarantool interval type in msgpack decoders and encoders. Tarantool datetime interval objects are decoded to `tarantool.Interval` type. `tarantool.Interval` may be encoded to Tarantool interval objects. You can create `tarantool.Interval` objects either from msgpack data or by using the same API as in Tarantool: ``` di = tarantool.Interval(year=-1, month=2, day=3, hour=4, minute=-5, sec=6, nsec=308543321, adjust=tarantool.IntervalAdjust.NONE) ``` Its attrubutes (same as in init API) are exposed, so you can use them if needed. datetime, numpy and pandas tools doesn't seem to be sufficient to cover all adjust cases supported by Tarantool. This patch does not yet introduce the support of datetime interval arithmetic. 1. tarantool/tarantool#5941 Part of #229

Tarantool supports datetime interval type since version 2.10.0 [1]. This patch introduced the support of Tarantool interval type in msgpack decoders and encoders. Tarantool datetime interval objects are decoded to `tarantool.Interval` type. `tarantool.Interval` may be encoded to Tarantool interval objects. You can create `tarantool.Interval` objects either from msgpack data or by using the same API as in Tarantool: ``` di = tarantool.Interval(year=-1, month=2, day=3, hour=4, minute=-5, sec=6, nsec=308543321, adjust=tarantool.IntervalAdjust.NONE) ``` Its attributes (same as in init API) are exposed, so you can use them if needed. datetime, numpy and pandas tools doesn't seem to be sufficient to cover all adjust cases supported by Tarantool. This patch does not yet introduce the support of datetime interval arithmetic. 1. tarantool/tarantool#5941 Part of #229

Tarantool supports datetime type since version 2.10.0 [1]. This patch introduced the support of Tarantool datetime type in msgpack decoders and encoders. Tarantool datetime objects are decoded to `tarantool.Datetime` type. `tarantool.Datetime` may be encoded to Tarantool datetime objects. `tarantool.Datetime` stores data in a `pandas.Timestamp` object. You can create `tarantool.Datetime` objects either from msgpack data or by using the same API as in Tarantool: ``` dt1 = tarantool.Datetime(year=2022, month=8, day=31, hour=18, minute=7, sec=54, nsec=308543321) dt2 = tarantool.Datetime(timestamp=1661969274) dt3 = tarantool.Datetime(timestamp=1661969274, nsec=308543321) ``` `tarantool.Datetime` exposes `year`, `month`, `day`, `hour`, `minute`, `sec`, `nsec` and `timestamp` properties if you need to convert `tarantool.Datetime` to any other kind of datetime object: ``` pdt = pandas.Timestamp(year=dt.year, month=dt.month, day=dt.day, hour=dt.hour, minute=dt.minute, second=dt.sec, microsecond=(dt.nsec // 1000), nanosecond=(dt.nsec % 1000)) ``` `pandas.Timestamp` was chosen to store data because it could be used to store both nanoseconds and timezone information. In-build Python `datetime.datetime` supports microseconds at most, `numpy.datetime64` do not support timezones. Tarantool datetime interval type is planned to be stored in custom type `tarantool.Interval` and we'll need a way to support arithmetic between datetime and interval. This is the main reason we use custom class instead of plain `pandas.Timestamp`. It is also hard to implement Tarantool-compatible timezones with full conversion support without custom classes. This patch does not yet introduce the support of timezones in datetime. 1. tarantool/tarantool#5941 2. https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html Part of #204

Tarantool supports datetime interval type since version 2.10.0 [1]. This patch introduced the support of Tarantool interval type in msgpack decoders and encoders. Tarantool datetime interval objects are decoded to `tarantool.Interval` type. `tarantool.Interval` may be encoded to Tarantool interval objects. You can create `tarantool.Interval` objects either from msgpack data or by using the same API as in Tarantool: ``` di = tarantool.Interval(year=-1, month=2, day=3, hour=4, minute=-5, sec=6, nsec=308543321, adjust=tarantool.IntervalAdjust.NONE) ``` Its attributes (same as in init API) are exposed, so you can use them if needed. datetime, numpy and pandas tools doesn't seem to be sufficient to cover all adjust cases supported by Tarantool. This patch does not yet introduce the support of datetime interval arithmetic. 1. tarantool/tarantool#5941 Part of #229

Tarantool supports datetime type since version 2.10.0 [1]. This patch introduced the support of Tarantool datetime type in msgpack decoders and encoders. Tarantool datetime objects are decoded to `tarantool.Datetime` type. `tarantool.Datetime` may be encoded to Tarantool datetime objects. `tarantool.Datetime` stores data in a `pandas.Timestamp` object. You can create `tarantool.Datetime` objects either from msgpack data or by using the same API as in Tarantool: ``` dt1 = tarantool.Datetime(year=2022, month=8, day=31, hour=18, minute=7, sec=54, nsec=308543321) dt2 = tarantool.Datetime(timestamp=1661969274) dt3 = tarantool.Datetime(timestamp=1661969274, nsec=308543321) ``` `tarantool.Datetime` exposes `year`, `month`, `day`, `hour`, `minute`, `sec`, `nsec`, `timestamp` and `value` (integer epoch time with nanoseconds precision) properties if you need to convert `tarantool.Datetime` to any other kind of datetime object: ``` pdt = pandas.Timestamp(year=dt.year, month=dt.month, day=dt.day, hour=dt.hour, minute=dt.minute, second=dt.sec, microsecond=(dt.nsec // 1000), nanosecond=(dt.nsec % 1000)) ``` `pandas.Timestamp` was chosen to store data because it could be used to store both nanoseconds and timezone information. In-build Python `datetime.datetime` supports microseconds at most, `numpy.datetime64` do not support timezones. Tarantool datetime interval type is planned to be stored in custom type `tarantool.Interval` and we'll need a way to support arithmetic between datetime and interval. This is the main reason we use custom class instead of plain `pandas.Timestamp`. It is also hard to implement Tarantool-compatible timezones with full conversion support without custom classes. This patch does not yet introduce the support of timezones in datetime. 1. tarantool/tarantool#5941 2. https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html Part of #204

Tarantool supports datetime interval type since version 2.10.0 [1]. This patch introduced the support of Tarantool interval type in msgpack decoders and encoders. Tarantool datetime interval objects are decoded to `tarantool.Interval` type. `tarantool.Interval` may be encoded to Tarantool interval objects. You can create `tarantool.Interval` objects either from msgpack data or by using the same API as in Tarantool: ``` di = tarantool.Interval(year=-1, month=2, day=3, hour=4, minute=-5, sec=6, nsec=308543321, adjust=tarantool.IntervalAdjust.NONE) ``` Its attributes (same as in init API) are exposed, so you can use them if needed. datetime, numpy and pandas tools doesn't seem to be sufficient to cover all adjust cases supported by Tarantool. This patch does not yet introduce the support of datetime interval arithmetic. 1. tarantool/tarantool#5941 Part of #229

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

kyukhin added feature A new functionality teamL tmp Initiative labels Mar 26, 2021

kyukhin added this to the 2.9.1 milestone Mar 26, 2021

kyukhin assigned sergos Mar 26, 2021

sergos mentioned this issue Mar 26, 2021

Consistent types across Lua and SQL data models #5817

Closed

10 tasks

kyukhin added epic and removed incoming Initiative labels Mar 29, 2021

kyukhin changed the title ~~Introduce DATETIME type to Tarantool~~ Introduce TIMESTAMP type to Tarantool Apr 19, 2021

kyukhin removed the tmp label Apr 30, 2021

coveralls mentioned this issue Jul 1, 2021

lua: datetime support in box #6188

Closed

igormunkin assigned tsafin and unassigned sergos Jul 6, 2021

tsafin linked a pull request Jul 6, 2021 that will close this issue

lua: datetime support in box #6188

Closed

tsafin added a commit to tsafin/tarantool that referenced this issue Jul 27, 2021

lua, datetime: display datetime

f0f123f

* introduced output routine for converting datetime to their default output format. * use this routine for tostring() in datetime.lua Part of tarantool#5941

tsafin added a commit to tsafin/tarantool that referenced this issue Jul 27, 2021

box, datetime: datetime comparison for indices

46027f6

* storage hints implemented for datetime_t values; * proper comparison for indices of datetime type. Part of tarantool#5941 Part of tarantool#5946

tsafin added a commit to tsafin/tarantool that referenced this issue Jul 27, 2021

lua, datetime: calculated attributes for datetimes

84f652a

* introduced a set of calculated attributes to data object, e.g.: - timestamp, seconds, microseconds, minute, or hours Part of tarantool#5941

tsafin added a commit to tsafin/tarantool that referenced this issue Jul 27, 2021

datetime: changelog for datetime module

4b71a4f

Introduced new date/time/interval types support to lua and storage engines. Closes tarantool#5941 Closes tarantool#5946

tsafin added a commit to tsafin/tarantool that referenced this issue Jul 27, 2021

lua, datetime: display datetime

d0dcefe

* introduced output routine for converting datetime to their default output format. * use this routine for tostring() in datetime.lua Part of tarantool#5941

igormunkin removed the teamL label Oct 20, 2022

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 11, 2023

feature: support datetime type

c2744af

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 11, 2023

feature: support interval type

ae752bd

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 13, 2023

feature: support datetime type

7fdadb7

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 13, 2023

feature: support interval type

de12a1c

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange mentioned this issue Jan 13, 2023

New checks types tarantool/checks#44

Merged

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support datetime type

db78726

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support interval type

4fb6d0f

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support datetime type

d0235bf

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support interval type

d167608

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support datetime type

db56511

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support interval type

a501cc5

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support datetime type

09afdcc

Support datetime type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

DifferentialOrange added a commit to tarantool/checks that referenced this issue Jan 16, 2023

feature: support interval type

a11224f

Support interval type checks [1] (added in Tarantool 2.10.0). 1. tarantool/tarantool#5941 Part of tarantool/tarantool#7726

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Introduce TIMESTAMP type to Tarantool #5941

Introduce TIMESTAMP type to Tarantool #5941

kyukhin commented Mar 26, 2021 •

edited by tsafin

Loading

mumez commented Jul 14, 2021

Introduce TIMESTAMP type to Tarantool #5941

Introduce TIMESTAMP type to Tarantool #5941

Comments

kyukhin commented Mar 26, 2021 • edited by tsafin Loading

mumez commented Jul 14, 2021

kyukhin commented Mar 26, 2021 •

edited by tsafin

Loading