Using The Datetime Or Timestamp Data Type In MySQL

In MySQL, you can use the  Datetime Or Timestamp data types to store date and time values.

The Datetime data type is used to store a date and time combination. The format for a Datetimevalue is ‘YYYY-MM-DD HH:MM:SS‘, where YYYY represents the year, MM represents the month, DD represents the day, HH represents the hour (in 24-hour format), MM represents the minute, and SS represents the second. For example, ‘2022-01-01 12:30:00‘ represents January 1, 2022 at 12:30 PM.

Here’s an example of how you would create a table with a Datetime column:

CREATE TABLE events ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255) NOT NULL, 
    start_time DATETIME NOT NULL 
);

The Timestamp data type is similar to the Datetime data type, but it also includes automatic initialization and updating to the current date and time.

Here’s an example of how you would create a table with a Timestamp column:

CREATE TABLE events ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255) NOT NULL, 
    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
);

When inserting records into the table, you can use the NOW() function to insert the current date and time as the value for the Timestamp column.

You can use the DATE(), TIME(), and YEAR() functions to extract the date, time, and year from a Datetime Or Timestamp value, respectively.

Theres many other function to operate with timestamps, DATE_ADD(), DATE_SUB(), DATE_FORMAT() among others.

You can use the MySQL manual to get more information and examples.