Bug #96594 direct access to the numerical value of a timestamp
Submitted: 20 Aug 2019 14:55 Modified: 21 Nov 2019 13:21
Reporter: Ruud H.G. van Tol Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[20 Aug 2019 14:55] Ruud H.G. van Tol 
Description:
Feature request: provide some way of direct access/cast/convert to the numerical value of a timestamp, for example to have virtual columns for those.

This is a workaround, but that uses a layer of (brittle) datetime-functions, while all it wants is the stored raw value:

  `mysql_row_created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

  `mysql_row_created_epoch` double(18,6) GENERATED ALWAYS AS ((timestampdiff(MICROSECOND,'1970-01-01 00:00:00',`mysql_row_created_at`) / 1000000)) VIRTUAL,

How to repeat:

  `mysql_row_created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

  `mysql_row_created_epoch` double(18,6) GENERATED ALWAYS AS ((timestampdiff(MICROSECOND,'1970-01-01 00:00:00',`mysql_row_created_at`) / 1000000)) VIRTUAL,
[21 Aug 2019 8:58] Ruud H.G. van Tol 
To make the issue clearer:

ALTER TABLE ruud_test MODIFY COLUMN mysql_row_created_epoch double 
AS (UNIX_TIMESTAMP(mysql_row_created_at));

ERROR 3102 (HY000): Expression of generated column 'mysql_row_created_epoch' contains a disallowed function.
[21 Aug 2019 10:21] Ruud H.G. van Tol 
The documentation says: "When the date argument is a TIMESTAMP column, UNIX_TIMESTAMP() returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion."

So maybe I should rather report the "disallowed function" error forUNIX_TIMESTAMP(<timestamp-column>) as a bug?
[4 Sep 2019 11:46] MySQL Verification Team 
Hi Mr. van Tol,

How about trying your example on the full timestamp, not only the one that is 6 bytes long.
[4 Sep 2019 11:54] Ruud H.G. van Tol 
Hello Sinisa, not sure what you mean there.

See also https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
[21 Oct 2019 13:21] MySQL Verification Team 
Hi,

Simply, try using TIMESTAMP column, without limiting its width.
[22 Nov 2019 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[29 Jan 21:03] Daniel Liebner 
Having the same issue, and I'm using a plain old timestamp field.

8.0.32-0buntu0.20.04.1