[MDEV-32496] TIMESTAMP variant to support YEAR range -4713 and +9999 - Jira

Details

Type: Task
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: None
Component/s: Data types, Temporal Types
Labels:
None

Description

MariaDB TIMESTAMP supports year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

Data type	Min	Max
Oracle TIMESTAMP	-4713	9999
DB2 TIMESTAP	0001	9999
PostgreSQL TIMESTAMP	-4713	294276
SQL Server DATETIME2	0001	9999
SQL Server DATETIME	1753	9999

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of struct timeval) to transfer values inside the server.

struct {

  longlong tv_sec; -- signed, to store negative years

  uint32 tv_usec;

};

This task is also a dependency for

MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Attachments

Issue Links

blocks

MDEV-10764 PL/SQL parser - Phase 2

Open

MDEV-11070 Providing compatibility to other databases - Phase 2

Open

MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics

Stalled

relates to

MDEV-341 64-bit support in FROM_UNIXTIME()

Open

MDEV-17389 sql_mode=ORACLE: Incompatibility in datetime arithmetic

Open

MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range

Closed

MDEV-10018 Timestamp with time zone

Open

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2023-10-17 11:22

Alexander Barkov made changes - 2023-10-17 11:22

Field	Original Value	New Value
Link		This issue blocks MDEV-15750 [ MDEV-15750 ]

Alexander Barkov made changes - 2023-10-17 11:22

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|col 1 row 1|col 2 row 1|col 3 row 1|
|Oracle TIMESTAMP|-4713||9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

This task is also a dependency for:

- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetics for the current TIMESTAMP implementation is not very meaningful because of a small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|col 1 row 1|col 2 row 1|col 3 row 1|
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

This task is also a dependency for:

- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetics for the current TIMESTAMP implementation is not very meaningful because of a small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 11:25

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|col 1 row 1|col 2 row 1|col 3 row 1|
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

This task is also a dependency for:

- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetics for the current TIMESTAMP implementation is not very meaningful because of a small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|col 1 row 1|col 2 row 1|col 3 row 1|
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
ulong tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 11:25

Link

This issue relates to ~~MDEV-32188~~ [ ~~MDEV-32188~~ ]

Alexander Barkov made changes - 2023-10-17 11:27

Link

This issue blocks MDEV-11070 [ MDEV-11070 ]

Alexander Barkov made changes - 2023-10-17 11:27

Link

This issue blocks MDEV-10764 [ MDEV-10764 ]

Alexander Barkov made changes - 2023-10-17 11:27

Link

This issue relates to MDEV-17389 [ MDEV-17389 ]

Sergei Golubchik made changes - 2023-10-17 11:50

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|col 1 row 1|col 2 row 1|col 3 row 1|
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
ulong tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
ulong tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Sergei Golubchik made changes - 2023-10-17 11:51

Fix Version/s

11.4 [ 29301 ]

Vladislav Vaintroub added a comment - 2023-10-17 12:08

bar, if possible, do not use ulong. It is a very badly portable type. for usec, int32_t is more than enough.

Vladislav Vaintroub added a comment - 2023-10-17 12:08 bar , if possible, do not use ulong. It is a very badly portable type. for usec, int32_t is more than enough.

Alexander Barkov made changes - 2023-10-17 12:49

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
ulong tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov added a comment - 2023-10-17 12:49

wlad, thanks for your note. I agree. Changed to uint32.

Alexander Barkov added a comment - 2023-10-17 12:49 wlad , thanks for your note. I agree. Changed to uint32.

Alexander Barkov made changes - 2023-10-17 12:51

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 12:51

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of the system {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 12:52

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of the system {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of the system {{struct timeval}}) to transfer values inside the server, because on most 32-bit systems {{timeval::tv_sec}} is 32 bit, which is not enough.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 12:52

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of the system {{struct timeval}}) to transfer values inside the server, because on most 32-bit systems {{timeval::tv_sec}} is 32 bit, which is not enough.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of the system {{struct timeval}}) to transfer values inside the server, because on most 32-bit systems {{timeval::tv_sec}} is 32 bit, which is not enough.

A structure like this should be ok:
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 12:56

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instread of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of the system {{struct timeval}}) to transfer values inside the server, because on most 32-bit systems {{timeval::tv_sec}} is 32 bit, which is not enough.

A structure like this should be ok:
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 12:56

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}} to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-17 12:57

Description

MariaDB TIMESTAMP support year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

MariaDB TIMESTAMP supports year values in the range 1970..2038, and after ~~MDEV-32188~~ it's going to support year values in the range 1970..2106.

This range is still narrower than most other databases support:

||Data type||Min||Max||
|Oracle TIMESTAMP|-4713|9999|
|DB2 TIMESTAP|0001|9999|
|PostgreSQL TIMESTAMP|-4713|294276|
|SQL Server DATETIME2|0001|9999|
|SQL Server DATETIME|1753|9999|

For migration purposes, we need a data type which will cover at least the range -4713..9999.

It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

Also, it will need a new C++ data type (instead of {{struct timeval}}) to transfer values inside the server.
{code:cpp}
struct {
longlong tv_sec; -- signed, to store negative years
uint32 tv_usec;
};
{code}

This task is also a dependency for
- MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

Alexander Barkov made changes - 2023-10-18 11:49

Link

This issue relates to MDEV-341 [ MDEV-341 ]

Alexander Barkov made changes - 2023-10-23 21:03

Link

This issue relates to MDEV-10018 [ MDEV-10018 ]

People

Assignee:: Unassigned

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2023-10-17 11:22

Updated:: 2023-10-23 21:03

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration