mirror of
https://github.com/MariaDB/server.git
synced 2025-10-24 16:38:14 +02:00
dfaf7e2eb4
Changing the return type of the following functions:
- CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), NOW()
- SYSDATE()
- FROM_UNIXTIME()
from DATETIME to TIMESTAMP.
Note, the old function NOW() returning DATETIME is still available
as LOCALTIMESTAMP or LOCALTIMESTAMP(), e.g.:
SELECT
LOCALTIMESTAMP, -- DATETIME
CURRENT_TIMESTAMP; -- TIMESTAMP
The change in the functions return data type fixes some problems
that occurred near a DST change:
- Problem #1
INSERT INTO t1 (timestamp_field) VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 (timestamp_field) VALUES (COALESCE(CURRENT_TIMESTAMP));
could result into two different values inserted.
- Problem #2
INSERT INTO t1 (timestamp_field) VALUES (FROM_UNIXTIME(1288477526));
INSERT INTO t1 (timestamp_field) VALUES (FROM_UNIXTIME(1288477526+3600));
could result into two equal TIMESTAMP values near a DST change.
Additional changes:
- FROM_UNIXTIME(0) now returns SQL NULL instead of '1970-01-01 00:00:00'
(assuming time_zone='+00:00')
- UNIX_TIMESTAMP('1970-01-01 00:00:00') now returns SQL NULL instead of 0
(assuming time_zone='+00:00'
These additional changes are needed for consistency with TIMESTAMP fields,
which cannot store '1970-01-01 00:00:00 +00:00'
20 lines
539 B
Text
20 lines
539 B
Text
select from_unixtime(0);
|
|
from_unixtime(0)
|
|
NULL
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect unixtime value: '0.0'
|
|
select from_unixtime(0.000001);
|
|
from_unixtime(0.000001)
|
|
1969-12-31 14:00:00.000001
|
|
select from_unixtime(1);
|
|
from_unixtime(1)
|
|
1969-12-31 14:00:01
|
|
select unix_timestamp('1969-12-31 14:00:00');
|
|
unix_timestamp('1969-12-31 14:00:00')
|
|
NULL
|
|
select unix_timestamp('1969-12-31 14:00:00.000001');
|
|
unix_timestamp('1969-12-31 14:00:00.000001')
|
|
0.000001
|
|
select unix_timestamp('1969-12-31 14:00:01');
|
|
unix_timestamp('1969-12-31 14:00:01')
|
|
1
|