MySQL Bugs: #78978: Add microseconds support to UNIX

Bug #78978	Add microseconds support to UNIX_TIMESTAMP
Submitted:	27 Oct 2015 12:39	Modified:	12 Feb 2018 7:17
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Please make it possible to get a UNIX timestamp with microsecond precision

How to repeat:
Try to get a UNIX timestamp with microsecond precision.

Suggested fix:
Maybe return a decimal or an int with the total number of msecs.

mysql> SELECT UNIX_TIMESTAMP_MSEC();
+-------------------------+
|   UNIX_TIMESTAMP_MSEC() |
+-------------------------+
|       1445949378.123456 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(NOW(),6);
+-------------------------+
|   UNIX_TIMESTAMP(NOW()) |
+-------------------------+
|       1445949378.123456 |
+-------------------------+
1 row in set (0.00 sec)

This does seem to work in 5.6:
SELECT UNIX_TIMESTAMP(NOW(6))

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

Hello Daniël,

Thank you for the feature request!

Thanks,
Umesh

UNIX_TIMESTAMP(NOW(6)) will be wrong for an hour at the end of daylight savings, if using a DST timezone.

There certainly should be a way of obtaining a UNIX_TIMESTAMP with a decimal part, WITHOUT referencing NOW() and the corresponding timezone headaches that creates.

UNIX_TIMESTAMP_MS() would be ideal,but you could perhaps also tweak the function so that UNIX_TIMESTAMP(n) returns n decimals, ONLY for the special values of n from 1 to 6?

In the meantime, you can use:

UNIX_TIMESTAMP()+MICROSECOND(UTC_TIME(6))*0.000001

...Which should work correctly, unless leap-seconds affect the microseconds part of a time.
(My assumption is that they don't, and that the :59 second repeats twice with the microseconds running from .000000 to .999999 on each of them, but I couldn't find clear documentation of this in the manual.  PLEASE CLARIFY *EXACTLY* WHAT HAPPENS DURING A LEAP-SECOND IN THE DOCS!!!)