MySQL DATETIME Data Type

Introduction

In this chapter, we will learn about the DATETIME data type in MySQL. The DATETIME data type is used to store date and time values. It is widely used in applications where the precise recording of date and time is necessary, such as logging events, scheduling, and time-stamping records. We will cover the syntax for defining DATETIME columns, examples, and important considerations for using DATETIME in MySQL.

Syntax

The basic syntax for defining a DATETIME column in MySQL is:

DATETIME

The DATETIME data type stores both date and time information in the format YYYY-MM-DD HH:MM:SS.

Example

Here is an example of how to define a DATETIME column in a table:

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    event_date DATETIME
);

In this example, event_date is defined as a DATETIME column to store the date and time of the event.

Full Example

Let’s go through a complete example where we create a database and a table with DATETIME columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE company_events;

2. Select the Database:

USE company_events;

3. Create the Events Table with DATETIME Columns:

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    event_date DATETIME
);

4. Insert Data into the Events Table:

INSERT INTO events (event_name, event_date) VALUES
('Annual Meeting', '2023-08-15 09:00:00'),
('Project Deadline', '2023-09-30 17:00:00'),
('Product Launch', '2023-10-10 11:30:00'),
('Team Building', '2023-11-20 15:00:00'),
('Holiday Party', '2023-12-25 19:00:00');

5. Query the Events Table:

SELECT * FROM events;

Output

In this example, the event_date column is defined as DATETIME to store the date and time of various company events.

Using DATETIME Values in Queries

You can use DATETIME values in queries to filter, sort, and manipulate date and time information.

Example: Query Events After a Specific Date

SELECT * FROM events WHERE event_date > '2023-09-01 00:00:00';

Output

Example: Query Events on a Specific Date

SELECT * FROM events WHERE DATE(event_date) = '2023-10-10';

Output

Important Considerations

• Storage Requirements: The DATETIME data type uses 5 bytes of storage.

• Range: The DATETIME data type supports a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

• Time Zone: DATETIME values do not store time zone information. If time zone awareness is required, consider using the TIMESTAMP data type.

• Default Values: You can set default values for DATETIME columns, such as the current date and time using the CURRENT_TIMESTAMP function.

  CREATE TABLE logs (
      id INT PRIMARY KEY AUTO_INCREMENT,
      message VARCHAR(255),
      log_time DATETIME DEFAULT CURRENT_TIMESTAMP
  );
  

Conclusion

The DATETIME data type is essential for storing date and time information in MySQL. This chapter covered the syntax for defining DATETIME columns, provided a full example with different datetime attributes, and discussed important considerations.