Converting string to datetime split into date and time

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:

How to get time as well?

>Solution :

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.