|
A script demonstrating the problem with EXTRACT:
SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59.999999');
|
SELECT EXTRACT(MINUTE_MICROSECOND FROM NOW(6));
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM NOW(6));
|
SELECT * FROM t1;
|
SHOW CREATE TABLE t1;
|
SELECT EXTRACT() correctly returns 5959999999
+-----------------------------------------+
|
| EXTRACT(MINUTE_MICROSECOND FROM NOW(6)) |
|
+-----------------------------------------+
|
| 5959999999 |
|
+-----------------------------------------+
|
The value stored in the table is erroneously truncated to 2147483647
+-----------------------------------------+
|
| EXTRACT(MINUTE_MICROSECOND FROM NOW(6)) |
|
+-----------------------------------------+
|
| 2147483647 |
|
+-----------------------------------------+
|
And the created column data type is wrong:
+-------+-----------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`EXTRACT(MINUTE_MICROSECOND FROM NOW(6))` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------+
|
The expected data type is bigint.
|