How to Separate Date and Time from MySQL DATETIME Columns?

Separating Date and Time from MySQL DATETIME

When storing timestamps in a DATETIME column, the values often combine date and time components, such as "2012-09-09 06:57:12." To extract these components separately, MySQL provides the DATE_FORMAT() function.

Query to Split Date and Time:

SELECT DATE_FORMAT(colName, '%Y-%m-%d') AS DATEONLY, 
       DATE_FORMAT(colName,'%H:%i:%s') AS TIMEONLY;

Explanation:

• DATEONLY: Uses the '%Y-%m-%d' format string to extract the date portion as "2012-09-09."
• TIMEONLY: Uses the '%H:%i:%s' format string to extract the time portion as "06:57:12."

The query returns separate columns for the date and time components, allowing you to access them independently.

Example:

Consider the following table with a DATETIME column:

id	timestamp
1	2012-09-09 06:57:12
2	2023-04-15 12:35:09

Executing the query:

SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS DATEONLY, 
       DATE_FORMAT(timestamp,'%H:%i:%s') AS TIMEONLY
FROM table_name;

Output:

id	DATEONLY	TIMEONLY
1	2012-09-09	06:57:12
2	2023-04-15	12:35:09

The above is the detailed content of How to Separate Date and Time from MySQL DATETIME Columns?. For more information, please follow other related articles on the PHP Chinese website!