MySQL Bugs: #85420: UNIX_TIMESTAMP function ignores optional seconds in number date.

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

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.

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.

Looks duplicate/related to https://bugs.mysql.com/bug.php?id=82070.

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.

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.

Posted by developer:
 
Docs updated. Closing.