How to Display DATETIME Values in 'DD-MM-YYYY HH:MM:SS' Format in MySQL?

Defining DATETIME Format in MySQL Table Creation

Question: How can I format a DATETIME column to 'DD-MM-YYYY HH:MM:SS' when creating a new MySQL table to ensure consistency in timestamps?

Answer:

While MySQL stores DATETIME values in 'YYYY-MM-DD HH:MM:SS' format by default, you cannot specify a specific default format when creating a table. However, by leveraging MySQL time format functions, you can easily retrieve and display timestamps in the desired format whenever necessary.

Using Time Format Functions:

To modify the display of DATETIME values:

SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM tablename;

In this example, the DATE_FORMAT function applies the specified '%m/%d/%Y %H:%i' format to the column_name column, displaying timestamps as 'MM/DD/YYYY HH:MM'.

Available Format Specifiers:

Numerous format specifiers are available for customization, including:

• %d: Day of the month with leading zeros (01-31)
• %m: Month of the year with leading zeros (01-12)
• %Y: Year in full (2023)
• %H: Hour (00-23)
• %i: Minute (00-59)

Example:

To create a new table with a DATETIME column and display timestamps in the 'DD-MM-YYYY HH:MM:SS' format:

CREATE TABLE my_table (
  datetime_column DATETIME,
  other_columns ...
);

SELECT DATE_FORMAT(datetime_column, '%d-%m-%Y %H:%M:%S') FROM my_table;

By utilizing MySQL time format functions, you can easily customize the display of DATETIME values without altering the underlying data storage format.

The above is the detailed content of How to Display DATETIME Values in 'DD-MM-YYYY HH:MM:SS' Format in MySQL?. For more information, please follow other related articles on the PHP Chinese website!