How to Customize DATETIME Format in MySQL Table Creation?

How to Configure DATETIME Format During Table Creation in MySQL

Creating a table with a DATETIME column is a common task in MySQL. However, you may encounter situations where you want to specify a specific format for the DATETIME values. This article explains how to set the default format for a DATETIME column to 'DD-MM-YYYY HH:MM:SS' during table creation using MySQL commands.

Default MySQL DATETIME Format

By default, MySQL stores and retrieves DATETIME values in the 'YYYY-MM-DD HH:MM:SS' format. This is the standard format used by MySQL and is often appropriate for many applications.

Specifying a Custom Format

To specify a custom format for a DATETIME column during table creation, you can use the TIME_FORMAT function. The following MySQL command demonstrates how to create a table with a DATETIME column formatted as 'DD-MM-YYYY HH:MM:SS':

CREATE TABLE my_table (
  id INT NOT NULL AUTO_INCREMENT,
  datetime_column DATETIME FORMAT TIME_FORMAT('%d-%m-%Y %H:%i:%s')
);

In this command, the TIME_FORMAT specifier is used to define the custom 'DD-MM-YYYY HH:MM:SS' format.

Note: The TIME_FORMAT function only affects the representation of DATETIME values stored in the database. The actual DATETIME values are still stored in the 'YYYY-MM-DD HH:MM:SS' format.

Displaying the Formatted Value

Once the table is created, you can use the DATE_FORMAT function to display the DATETIME values in the custom format:

SELECT id, DATE_FORMAT(datetime_column, '%d-%m-%Y %H:%i:%s') AS formatted_datetime
FROM my_table;

This will retrieve the id and the DATETIME column in the 'DD-MM-YYYY HH:MM:SS' format.

The above is the detailed content of How to Customize DATETIME Format in MySQL Table Creation?. For more information, please follow other related articles on the PHP Chinese website!