Converting string to datetime split into date and time-CodePudding

I am trying to convert string '2022-12-28T22:28:43.260781049Z' to datetime format.

I have such query:

SELECT  date(str_to_date('2022-12-28T22:28:43.260781049Z','%Y-%m-%d')) as date,
        hour(str_to_date('2022-12-28T22:28:43.260781049Z',"%H:%M:%S")) as hour
FROM transaction

And such output:

date	time
'2022-12-28'	NULL

How to get time as well?

CodePudding user response：

You can directly use a CAST on your string value to TIMESTAMP, then extract the date and the time with the hononimous DATE and TIME MySQL functions.

SELECT DATE(CAST(timestamp_ AS DATETIME)) AS date_,
       TIME(CAST(timestamp_ AS DATETIME)) AS time_
FROM transactions;

Check the demo here.

CodePudding user response：

Use timestamp instead of str_to_date:

SELECT hour(timestamp('2022-12-28T22:28:43.260781049Z')) as hour