mysqlfunctiondatetimetemporal

How to reverse the output of EXTRACT() in MySQL

I have the following query -

SELECT NOW(6) AS output1, CONCAT(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(DAY FROM NOW()), EXTRACT(HOUR_MICROSECOND FROM NOW(6))) AS output2;
+----------------------------+--------------------+
| output1                    | output2            |
+----------------------------+--------------------+
| 2021-12-07 02:06:28.783259 | 202112720628783259 |
+----------------------------+--------------------+

Now I want to reverse the result - 202112720628783259 to 2021-12-07 02:06:28.783259. But I can't think of a way to do it.

Solution

  • In the output2, the day, hour, and minute have one digit. So you can't reliably parse that back to YYYY-MM-DD HH:MM:SS.

    I suggest you instead use DATE_FORMAT() and its reverse, STR_TO_DATE().

    SELECT NOW(6) AS output1, DATE_FORMAT(NOW(6), '%Y%m%d%H%i%s%f') as output2
+----------------------------+----------------------+
| output1                    | output2              |
+----------------------------+----------------------+
| 2021-12-06 20:44:31.927978 | 20211206204431927978 |
+----------------------------+----------------------+