How to Separate Date and Time from a MySQL DATETIME Field?

Extracting Date and Time Separately from a DATETIME Field in MySQL

In a MySQL table, a DATETIME field often stores a timestamp that incorporates both date and time components. To retrieve these components individually while fetching data, employ the following techniques:

Using DATE_FORMAT() Function

The DATE_FORMAT() function enables you to extract specific date and time formats from a DATETIME field. Here's an example:

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

In this query, the DATEONLY expression extracts only the date component in 'YYYY-MM-DD' format, while the TIMEONLY expression extracts only the time component in 'HH:MM:SS' format.

For instance, if your DATETIME field contains the value "2012-09-09 06:57:12," the query result will be:

DATEONLY | TIMEONLY
---------------------------
2012-09-09 | 06:57:12

This method allows you to extract the date and time components separately from a DATETIME field using a single MySQL query.

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