| Bug #84403 | unix_timestamp not valid past 2038-01-19 03:14:07 | ||
|---|---|---|---|
| Submitted: | 4 Jan 2017 13:36 | Modified: | 19 Jan 2017 15:51 |
| Reporter: | Dennis Clarke | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | all | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Jan 2017 0:12]
MySQL Verification Team
Thank you for the bug report.
c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.18 Source distribution PULL: 2016-DEC-25
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------+
| innodb_version | 5.7.18 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.18 |
| version_comment | Source distribution PULL: 2016-DEC-25 |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+---------------------------------------+
8 rows in set (0.00 sec)
mysql 5.7 > select unix_timestamp('2038-01-19 03:14:07');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:07') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql 5.7 > select unix_timestamp('2038-01-19 03:14:08');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:08') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution PULL: 2016-DEC-25
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------+
| innodb_version | 5.6.36 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.36 |
| version_comment | Source distribution PULL: 2016-DEC-25 |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+---------------------------------------+
7 rows in set (0.04 sec)
mysql 5.6 > select unix_timestamp('2038-01-19 03:14:07');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:07') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql 5.6 > select unix_timestamp('2038-01-19 03:14:08');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:08') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.55 Source distributiona PULL: 2016-DEC-25
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 > SHOW VARIABLES LIKE "%version%";
+-------------------------+----------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------+
| innodb_version | 5.5.55 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.55 |
| version_comment | Source distributiona PULL: 2016-DEC-25 |
| version_compile_machine | AMD64 |
| version_compile_os | Win64 |
+-------------------------+----------------------------------------+
7 rows in set (0.04 sec)
mysql 5.5 > select unix_timestamp('2038-01-19 03:14:07');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:07') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql 5.5 > select unix_timestamp('2038-01-19 03:14:08');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:08') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
[5 Jan 2017 3:42]
Dennis Clarke
The status of this bug is wrong.
Whomever followed up demonstrated that unix_timestamp fails for
all dates that are tried and that is far far worse.
mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.33 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.33 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | sparc |
| version_compile_os | solaris10 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql>
mysql> select unix_timestamp('2038-01-19 03:14:07');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:07') |
+---------------------------------------+
| 2147483647 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2038-01-19 03:14:08');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:08') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
[5 Jan 2017 11:25]
MySQL Verification Team
Thank you for the feedback. Thanks, Umesh
[19 Jan 2017 15:51]
Paul DuBois
Posted by developer: Updated UNIX_TIMESTAMP() description: When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0. The valid range of values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.
Description: Minor item but the date function documentation should make a note that any date past 2038-01-19 03:14:07 will return a zero for unix_timestamp() function. Regardless if this is a 64 bit architecture or not. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.33 | +-----------+ 1 row in set (0.00 sec) mysql> select unix_timestamp('2038-01-19 03:14:07'); +---------------------------------------+ | unix_timestamp('2038-01-19 03:14:07') | +---------------------------------------+ | 2147483647 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select unix_timestamp('2038-01-19 03:14:08'); +---------------------------------------+ | unix_timestamp('2038-01-19 03:14:08') | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.01 sec) Suggested fix: Merely update the documentation with a note or fix the unix_timestamp() func for 64-bit architectures. I think the note is all that is needed.