[MDEV-17448] Support DATETIME(6) for ROW START, ROW END Created: 2018-10-14  Updated: 2023-02-11

Status: Open
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 3
Labels: system_versioned_tables

Issue Links:
Relates
relates to MDEV-10018 Timestamp with time zone Open
relates to MDEV-11829 Please add support for datetime with ... Open
relates to MDEV-17798 System variable system_versioning_aso... Closed
relates to MDEV-18512 using DATETIME(6) as row_start/row_en... Closed

 Description   

If I try to create a system-versioned table with ROW START and ROW END columns of type DATETIME(6), the table is created. This is different from what happens if I try, for example, VARCHAR, in which case I get a clear error message. But then, the table is unusable.

MariaDB [test]> CREATE OR REPLACE TABLE t (
 
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 
    ->     label VARCHAR(50) NOT NULL,
 
    ->
 
    ->         PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
 
    ->     valid_from DATETIME(6)
 
    ->         GENERATED ALWAYS AS ROW START,
 
    ->     valid_to DATETIME(6)
 
    ->         GENERATED ALWAYS AS ROW END,
 
    ->
 
    ->     PRIMARY KEY (id)
 
    -> )
 
    ->     WITH SYSTEM VERSIONING,
 
    ->     ENGINE InnoDB
 
    -> ;
 
Query OK, 0 rows affected (0.010 sec)
 
 
 
MariaDB [test]> INSERT INTO t (label) VALUES ('blah blah');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> SELECT * FROM t;
 
Empty set (0.000 sec)

And the reason is easy to find out:

MariaDB [test]> SELECT * FROM t FOR SYSTEM_TIME ALL;
 
+----+-----------+----------------------------+----------------------------+
 
| id | label     | valid_from                 | valid_to                   |
 
+----+-----------+----------------------------+----------------------------+
 
|  1 | blah blah | 2018-10-14 19:56:08.057624 | 0000-00-00 00:00:00.000000 |
 
+----+-----------+----------------------------+----------------------------+

If DATETIME is not supposed to work, it shouldn't be allowed.

 Comments   
Comment by Aleksey Midenkov [ 2018-10-26 ]

It is already disallowed in 10.3. But `DATETIME` is not prohibited by standard, so it should be supported in 10.4.

Comment by Alexander Barkov [ 2018-12-27 ]

4.41.3 Properties of SQL-transactions says:

An SQL-transaction has a transaction timestamp, a value of an implementation-defined timestamp type that is
used to set the values of system-time period start and system-time period end columns of rows, if any, modified
by the execution of an SQL-data change statement in this SQL-transaction.

We use MariaDB's TIMESTAMP data type (which is timestamp with local time zone) as this implementation defined type.
Translation from TIMESTAMP to DATETIME is lossy:

  • DATETIME preserves the YYYYMMDDhhmmss.ff part of the TIMESTAMP
  • but it looses the timezone information (i.e. the value of the @@time_zone system variable which was set at the conversion time)

It's not possible to restore the original TIMESTAMP value once it was converted to DATETIME, because the reverse conversion can already use a different @@time_zone value.

For me it does not seem to be useful storing ROW START / ROW END data in DATETIME or DATE columns.

We'll be adding TIMESTAMP WITH TIME ZONE soon. This new data type will support the full datetime range from '0001-01-01 00:00:00' to '9999-12-31 23:59:59' and will preserve both TIMESTAMP parts:

  • the YYYYMMDDhhmmss.ff part
  • the time zone part

so round trip conversion TIMESTAMP -> TIMESTAMP WITH TIME ZONE -> TIMESTAMP will be non-lossy.
It's a better candidate for ROW START and ROW END columns that DATETIME / DATE.

Comment by Aleksey Midenkov [ 2018-12-28 ]

Possible solution for DATETIME row_start, row_end might be to store in UTC. System fields independent from time_zone can be an advantage for certain deployments.

Comment by Robert Palm [ 2023-01-10 ]

Hi,

I w'd love to see a time range extension.

When is it going to happen?

Thank you.

Comment by Rick James [ 2023-02-11 ]

@Alexander – "TIMESTAMP data type (which is timestamp with local time zone)" – I thought that `TIMESTAMP` was stored in UTC, without any timezone. (When viewing (SELECTing) it, the current timezone is used to modify what you see.)

That is, `TIMESTAMP` should be adequate, as is, for use in ROW_START, etc.

Generated at Thu Feb 08 08:36:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.