Bug #85420 UNIX_TIMESTAMP function ignores optional seconds in number date.
Submitted: 12 Mar 2017 22:58 Modified: 13 Mar 2017 17:57
Reporter: monty solomon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2017 22:58] monty solomon
Description:
The documentation states that a date number can optionally include fractional seconds but that appears to be ignored.

12.7 Date and Time Functions
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_unix-timesta...

date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD, optionally including a fractional seconds part.

How to repeat:
mysql> select UNIX_TIMESTAMP(20170312), UNIX_TIMESTAMP(20170312.123)\G
*************************** 1. row ***************************
    UNIX_TIMESTAMP(20170312): 1489294800
UNIX_TIMESTAMP(20170312.123): 1489294800.000

Suggested fix:
Fix the UNIX_TIMESTAMP function to accept a date number with fractional seconds.
[12 Mar 2017 23:11] monty solomon
The fractional seconds are processed when the HHMMSS are also specified but that is not documented as either supported or required.

mysql> SELECT UNIX_TIMESTAMP(170312165917.123), UNIX_TIMESTAMP(20170312165917.123)\G
*************************** 1. row ***************************
  UNIX_TIMESTAMP(170312165917.123): 1489352357.123
UNIX_TIMESTAMP(20170312165917.123): 1489352357.123

mysql> SELECT NOW(3), NOW(3)+0, UNIX_TIMESTAMP(NOW(3)+0)\G
*************************** 1. row ***************************
                  NOW(3): 2017-03-12 19:11:01.881
                NOW(3)+0: 20170312191101.881
UNIX_TIMESTAMP(NOW(3)+0): 1489360261.881

See bug #85421.
[13 Mar 2017 14:33] MySQL Verification Team
Looks duplicate/related to https://bugs.mysql.com/bug.php?id=82070.
[13 Mar 2017 16:34] MySQL Verification Team
It turns out that this is a bug in our documentation. Our manual says:

"
date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD, optionally including a fractional seconds part. 

"

That is wrong. You can not have DATE with optional fractional seconds part. You can only have DATETIME/TIMESTAMP with optional fractional seconds. Like these examples:

mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.135');
+-------------------------------------------+
| UNIX_TIMESTAMP('2015-11-13 10:20:19.135') |
+-------------------------------------------+
|                            1447402819.135 |
+-------------------------------------------+
1 row in set (0.05 sec)

mysql> SELECT FROM_UNIXTIME(1111885200.135);
+-------------------------------+
| FROM_UNIXTIME(1111885200.135) |
+-------------------------------+
| 2005-03-27 04:00:00.135       |
+-------------------------------+
1 row in set (0.04 sec)

mysql> SELECT UNIX_TIMESTAMP(1111885200.135);
+--------------------------------+
| UNIX_TIMESTAMP(1111885200.135) |
+--------------------------------+
|                          0.000 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

Also, this bug is not duplicate of # 82070. This is a different problem.

Verified as a documentation bug.
[13 Mar 2017 17:51] Paul DuBois
Revised description in manual about acceptable argument values:

The date argument may be a DATE, DATETIME, or TIMESTAMP string, or a
number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format.
If the argument includes a time part, it may optionally include a
fractional seconds part.
[13 Mar 2017 17:57] Paul DuBois
Posted by developer:
 
Docs updated. Closing.