How to reverse the output of EXTRACT() in MySQL-CodePudding

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.

CodePudding user response：

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 |
 ---------------------------- ----------------------