Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table

Learn mysql: setup the format of datetime to 'dd-mm-yyyy hh:mm:ss' when creating a table with practical examples, diagrams, and best practices. Covers mysql, datetime, command-line development tech...

MySQL: Setting DATETIME Format to 'DD-MM-YYYY HH:MM:SS' During Table Creation

Learn how to define the DATETIME column format to 'DD-MM-YYYY HH:MM:SS' directly when creating a MySQL table, ensuring consistent data representation.

When working with databases, especially MySQL, managing DATETIME formats is crucial for data consistency and readability. While MySQL stores DATETIME internally in a 'YYYY-MM-DD HH:MM:SS' format, you might have specific application requirements or reporting needs to display it as 'DD-MM-YYYY HH:MM:SS'. This article will guide you through the process of setting up a table and handling this specific DATETIME format.

Understanding MySQL's DATETIME Data Type

The DATETIME data type in MySQL is used to store date and time values. It has a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. By default, MySQL stores and retrieves DATETIME values in the 'YYYY-MM-DD HH:MM:SS' format. It's important to understand that you cannot directly change the storage format of the DATETIME data type itself to 'DD-MM-YYYY HH:MM:SS' during table creation. MySQL's internal representation is fixed. However, you can control how values are inserted and retrieved to match your desired display format.

A diagram illustrating MySQL's internal DATETIME storage vs. desired display format. Box 1: 'Application Input (DD-MM-YYYY HH:MM:SS)'. Arrow to Box 2: 'MySQL Conversion (Implicit/Explicit)'. Box 2: 'MySQL Internal Storage (YYYY-MM-DD HH:MM:SS)'. Arrow to Box 3: 'Application Output (DD-MM-YYYY HH:MM:SS)'. Use blue boxes for processes, green for data formats, arrows for flow.

MySQL DATETIME internal storage vs. application display format

Creating a Table with DATETIME Columns

To illustrate, let's create a simple table named events with a DATETIME column. You declare the column as DATETIME just like any other data type. The formatting comes into play when you insert data or select it.

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(255) NOT NULL,
    event_timestamp DATETIME NOT NULL
);

Creating the 'events' table with a DATETIME column.

💡

While you cannot change the internal storage format, you can use VARCHAR to store dates in any custom format. However, this is generally not recommended for date/time values as it prevents proper date arithmetic and indexing. Always prefer DATETIME or TIMESTAMP for date/time data.

Inserting and Retrieving Data in 'DD-MM-YYYY HH:MM:SS' Format

When inserting data, MySQL is flexible. You can provide the DATETIME string in 'YYYY-MM-DD HH:MM:SS' or even 'YYYYMMDDHHMMSS' format. To insert a value that looks like 'DD-MM-YYYY HH:MM:SS' but is correctly parsed by MySQL, you need to use the STR_TO_DATE() function. Similarly, to retrieve data in your desired 'DD-MM-YYYY HH:MM:SS' format, you'll use the DATE_FORMAT() function.

-- Inserting data using STR_TO_DATE()
INSERT INTO events (event_name, event_timestamp)
VALUES 
    ('Meeting', STR_TO_DATE('15-08-2023 10:30:00', '%d-%m-%Y %H:%i:%s')),
    ('Project Deadline', STR_TO_DATE('01-09-2023 17:00:00', '%d-%m-%Y %H:%i:%s'));

-- Retrieving data in 'DD-MM-YYYY HH:MM:SS' format using DATE_FORMAT()
SELECT 
    event_id,
    event_name,
    DATE_FORMAT(event_timestamp, '%d-%m-%Y %H:%i:%s') AS formatted_timestamp
FROM 
    events;

Inserting data with STR_TO_DATE() and retrieving with DATE_FORMAT().

⚠️

Always ensure the format string in STR_TO_DATE() exactly matches the input string's format. Mismatches will result in NULL values or incorrect parsing.

1. Step 1

Connect to your MySQL database using a client like the MySQL command-line client or MySQL Workbench.

2. Step 2

Execute the CREATE TABLE statement to set up your table with a DATETIME column.

3. Step 3

Use the STR_TO_DATE() function when inserting values if your input string is not in MySQL's default 'YYYY-MM-DD HH:MM:SS' format.

4. Step 4

Retrieve data using the DATE_FORMAT() function to display the DATETIME values in your preferred 'DD-MM-YYYY HH:MM:SS' format for reporting or application display.