SQL Date & Time

Introduction

In this chapter, we will focus on handling date and time in SQL. Date and time are essential aspects of many databases, and SQL provides a wide range of functions and operators to work with them. This chapter will cover the various data types, functions, and examples to help you effectively manage and manipulate date and time values in SQL.

Date and Time Data Types

Different SQL databases support various date and time data types. The most common ones are:

1. DATE: Stores date values (year, month, and day).
2. TIME: Stores time values (hour, minute, second).
3. DATETIME: Stores both date and time values.
4. TIMESTAMP: Stores both date and time values, usually with time zone information.
5. YEAR: Stores year values.

Example

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP
);

Inserting Date and Time Values

You can insert date and time values using the appropriate format for each data type.

Example

INSERT INTO events (event_name, event_date, event_time, event_datetime, event_timestamp)
VALUES ('Meeting', '2024-06-30', '14:30:00', '2024-06-30 14:30:00', '2024-06-30 14:30:00');

Retrieving Date and Time Values

You can retrieve date and time values using a simple SELECT statement.

Example

SELECT event_name, event_date, event_time, event_datetime, event_timestamp
FROM events;

Output

event_name	event_date	event_time	event_datetime	event_timestamp
Meeting	2024-06-30	14:30:00	2024-06-30 14:30:00	2024-06-30 14:30:00

Date and Time Functions

SQL provides various functions to manipulate and format date and time values.

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

These functions return the current date, time, and timestamp.

Example

SELECT CURRENT_DATE AS current_date, CURRENT_TIME AS current_time, CURRENT_TIMESTAMP AS current_timestamp;

Output

current_date	current_time	current_timestamp
2024-06-30	14:45:00	2024-06-30 14:45:00.000

DATE_FORMAT

Formats a date value according to a specified format.

Example

SELECT DATE_FORMAT(event_date, '%W, %M %d, %Y') AS formatted_date
FROM events;

Output

formatted_date
Sunday, June 30, 2024

DATE_ADD, DATE_SUB

Adds or subtracts a specified time interval from a date.

Example

SELECT event_date, DATE_ADD(event_date, INTERVAL 7 DAY) AS next_week, DATE_SUB(event_date, INTERVAL 7 DAY) AS last_week
FROM events;

Output

event_date	next_week	last_week
2024-06-30	2024-07-07	2024-06-23

DATEDIFF

Returns the difference in days between two dates.

Example

SELECT DATEDIFF('2024-07-07', event_date) AS days_difference
FROM events;

Output

days_difference
7

EXTRACT

Extracts a part of a date (such as year, month, day).

Example

SELECT EXTRACT(YEAR FROM event_date) AS year, EXTRACT(MONTH FROM event_date) AS month, EXTRACT(DAY FROM event_date) AS day
FROM events;

Output

year	month	day
2024	6	30

Practical Examples

Example 1: Upcoming Events

To find all events happening in the next 30 days:

SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY);

Example 2: Events This Month

To find all events happening in the current month:

SELECT event_name, event_date
FROM events
WHERE MONTH(event_date) = MONTH(CURRENT_DATE) AND YEAR(event_date) = YEAR(CURRENT_DATE);

Example 3: Event Duration

To calculate the duration between the current time and event time:

SELECT event_name, TIMEDIFF(event_time, CURRENT_TIME) AS time_until_event
FROM events;

Output

event_name	time_until_event
Meeting	01:45:00

Conclusion

Managing date and time values is a crucial aspect of database operations. This chapter covered the different date and time data types, functions, and provided examples to illustrate their use. Understanding how to effectively handle date and time values will greatly enhance your ability to manage and manipulate data in a relational database.