Home >Database >Mysql Tutorial >How to Separate Date and Time from MySQL DATETIME Columns?

How to Separate Date and Time from MySQL DATETIME Columns?

Linda Hamilton
Linda HamiltonOriginal
2025-01-04 15:46:431010browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn