Lesson 3.4: Essential Date and Time Functions in SQL

Date and time functions in SQL allow you to extract, modify, and format date and time values. These functions are widely used for analyzing temporal data, filtering by date, calculating intervals, and formatting output. This lesson covers the most commonly used functions with examples based on the Sakila database.

Important: CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP are, in many DBMSs, special SQL expressions (or aliases of corresponding functions), not "regular" functions in the form NAME(arg1, arg2, ...). Because of this, syntax and behavior details can differ across DBMSs.

Common Date and Time Functions

CURRENT_DATE — A special expression that returns the current date (without time).

Syntax:

CURRENT_DATE

Example:

SELECT CURRENT_DATE AS today;

Result: The current date, e.g.: 2025-06-03

CURRENT_TIME — A special expression/alias that returns the current time (without date).

Syntax:

CURRENT_TIME
CURRENT_TIME()
CURRENT_TIME(precision)

Example:

SELECT CURRENT_TIME AS now_time;

Result: The current time. With precision specified (for example, CURRENT_TIME(3)), the value includes fractional seconds.

CURRENT_TIMESTAMP / NOW() — Returns the current date and time (often as a special expression/alias).

Syntax:

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP(precision)
NOW()

Example:

SELECT CURRENT_TIMESTAMP AS now_datetime;
SELECT NOW() AS now_datetime;

Result: The current date and time, e.g.: 2025-06-03 14:25:30

Important: in most DBMSs, CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP are fixed at the start of statement execution (and in some modes, at transaction start). So within one query, all rows usually get the same value.

If you need a "current timestamp" at the moment a function is evaluated for a specific row, use DBMS-specific alternatives (for example, SYSDATE() in MySQL/MariaDB, clock_timestamp() in PostgreSQL).

DATE() — Extracts only the date from a datetime value.

Syntax:

DATE(datetime_value)

Example:

SELECT DATE(rental_date) AS rental_only_date
FROM rental
LIMIT 3;

Result: Returns only the date from the rental_date column.

TIME() — Extracts only the time from a datetime value.

Syntax:

TIME(datetime_value)

Example:

SELECT TIME(rental_date) AS rental_only_time
FROM rental
LIMIT 3;

Result: Returns only the time from the rental_date column.

YEAR() — Extracts the year from a date value.

Syntax:

YEAR(date_value)

Example:

SELECT YEAR(rental_date) AS rental_year
FROM rental
LIMIT 3;

Result: Returns the year from the rental date.

MONTH() — Extracts the month from a date value.

Syntax:

MONTH(date_value)

Example:

SELECT MONTH(rental_date) AS rental_month
FROM rental
LIMIT 3;

Result: Returns the month from the rental date.

DAY() — Extracts the day of the month from a date value.

Syntax:

DAY(date_value)

Example:

SELECT DAY(rental_date) AS rental_day
FROM rental
LIMIT 3;

Result: Returns the day of the month from the rental date.

DATE_ADD() — Adds a specified interval to a date.

Syntax:

DATE_ADD(date, INTERVAL value unit)

Example:

SELECT DATE_ADD(rental_date, INTERVAL 7 DAY) AS return_due
FROM rental
LIMIT 3;

Result: Returns the date increased by 7 days.

DATE_SUB() — Subtracts a specified interval from a date.

Syntax:

DATE_SUB(date, INTERVAL value unit)

Example:

SELECT DATE_SUB(rental_date, INTERVAL 3 DAY) AS three_days_before
FROM rental
LIMIT 3;

Result: Returns the date decreased by 3 days.

DATEDIFF() — Returns the number of days between two dates.

Syntax:

DATEDIFF(date1, date2)

Example:

SELECT DATEDIFF(return_date, rental_date) AS rental_duration
FROM rental
WHERE return_date IS NOT NULL
LIMIT 3;

Result: The number of days between the return date and the rental date.

DATE_FORMAT() — Formats a date in a specified format (MySQL).

Syntax:

DATE_FORMAT(date, format)

Example:

SELECT DATE_FORMAT(rental_date, '%d.%m.%Y') AS formatted_date
FROM rental
LIMIT 3;

Result: Date in the format dd.mm.yyyy, e.g.: 03.06.2025

Common format specifiers:

• %Y: Year (4 digits)
• %m: Month (2 digits)
• %d: Day of the month (2 digits)
• %H: Hour (24-hour format)
• %i: Minutes
• %s: Seconds

STRFTIME() — Formats date/time (SQLite, PostgreSQL).

Syntax:

STRFTIME(format, date)

Example:

SELECT STRFTIME('%Y-%m-%d', rental_date) AS formatted_date
FROM rental
LIMIT 3;

Result: Date in the format yyyy-mm-dd.

TIMESTAMPDIFF() — Difference between two dates/times in specified units (MySQL).

Syntax:

TIMESTAMPDIFF(unit, datetime1, datetime2)

Example:

SELECT TIMESTAMPDIFF(DAY, rental_date, return_date) AS days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 3;

Result: The number of days between the rental and return dates.

EXTRACT() — Extracts a part of a date or time (year, month, day, etc.).

Syntax:

EXTRACT(part FROM date)

Example:

SELECT EXTRACT(YEAR FROM rental_date) AS rental_year
FROM rental
LIMIT 3;

Result: Extracts the year from the rental date.

Practical Usage

1. Finding movies rented in the last 30 days:

   SELECT *
   FROM rental
   WHERE rental_date > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);
   

2. Counting rentals per month:

   SELECT YEAR(rental_date) AS year, MONTH(rental_date) AS month, COUNT(*) AS rentals
   FROM rental
   GROUP BY year, month
   ORDER BY year DESC, month DESC;
   

3. Formatting rental date for a report:

   SELECT DATE_FORMAT(rental_date, '%d.%m.%Y') AS formatted_rental
   FROM rental
   LIMIT 5;
   

Key Takeaways from This Lesson

Date and time functions allow you to flexibly analyze and transform temporal data in SQL. Use them for filtering, grouping, calculating intervals, and formatting dates in reports. Practice these functions with examples from the Sakila database to reinforce your skills.