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.
- Create a Database:
CREATE DATABASE company_events;
- Select the Database:
USE company_events;
- 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
);
- 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');
- Query the Events Table:
SELECT * FROM events;
Output
id | event_name | event_date |
---|---|---|
1 | Annual Meeting | 2023-08-15 09:00:00 |
2 | Project Deadline | 2023-09-30 17:00:00 |
3 | Product Launch | 2023-10-10 11:30:00 |
4 | Team Building | 2023-11-20 15:00:00 |
5 | Holiday Party | 2023-12-25 19:00:00 |
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
id | event_name | event_date |
---|---|---|
2 | Project Deadline | 2023-09-30 17:00:00 |
3 | Product Launch | 2023-10-10 11:30:00 |
4 | Team Building | 2023-11-20 15:00:00 |
5 | Holiday Party | 2023-12-25 19:00:00 |
Example: Query Events on a Specific Date
SELECT * FROM events WHERE DATE(event_date) = '2023-10-10';
Output
id | event_name | event_date |
---|---|---|
3 | Product Launch | 2023-10-10 11:30:00 |
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 theTIMESTAMP
data type. -
Default Values: You can set default values for
DATETIME
columns, such as the current date and time using theCURRENT_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.