Date & Time Functions

Almost every table in StreamHub carries a date somewhere - when a user signed up, when a review was posted, when a movie was released. MySQL's built-in date and time functions let you do arithmetic on those dates, extract specific parts of them, and format them for display, all without pulling raw values into application code first. This lesson works through the functions you'll reach for constantly, against StreamHub's users and reviews tables.

NOW(), CURDATE() and CURTIME() - The Current Date/Time

NOW() returns the current date and time together, CURDATE() returns just today's date, and CURTIME() returns just the current time. As covered in Constraints, these pair naturally with a DEFAULT so a column fills itself in automatically whenever a row is inserted without specifying one:

CREATE TABLE reviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  movie_id INT,
  rating INT,
  comment TEXT,
  review_date DATE DEFAULT (CURDATE())
);

-- review_date is filled in automatically as today's date
INSERT INTO reviews (user_id, movie_id, rating, comment)
VALUES (14, 7, 5, 'One of the best twists I have seen.');

CURDATE() is the right choice here rather than NOW(), since review_date is a plain DATE column with no time component to store.

DATE_ADD() and DATE_SUB() - Date Arithmetic

DATE_ADD() and DATE_SUB() add or subtract an interval of time from a date - days, weeks, months, years, and more. A very common pattern is finding rows within a recent rolling window, such as every review posted in the last 30 days:

SELECT id, movie_id, rating, review_date
FROM reviews
WHERE review_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

DATE_SUB(CURDATE(), INTERVAL 30 DAY) computes "today minus 30 days," so the query reads naturally as "reviews on or after that date." DATE_ADD() works the same way in the opposite direction - useful for things like calculating a subscription's renewal date.

DATEDIFF() - Days Between Two Dates

DATEDIFF() returns the whole number of days between two dates. A natural use in StreamHub is showing how long ago a user originally signed up:

SELECT username, DATEDIFF(CURDATE(), signup_date) AS days_as_member
FROM users;

DATEDIFF() always takes the first date minus the second, so putting CURDATE() first gives a positive number of days that have passed since signup.

YEAR(), MONTH() and DAY() - Extracting Part of a Date

YEAR(), MONTH() and DAY() pull out a single numeric piece of a date. This is exactly the pattern foreshadowed back in GROUP BY & HAVING - grouping reviews by the year they were posted, to see how activity has trended over time:

SELECT YEAR(review_date) AS review_year, COUNT(*) AS total_reviews
FROM reviews
GROUP BY YEAR(review_date)
ORDER BY review_year;

Because YEAR(review_date) returns a plain number, it can be used in GROUP BY, ORDER BY, and WHERE exactly like any other expression.

DATE_FORMAT() - Formatting a Date as Readable Text

Raw dates like 2025-01-15 are fine for storage and comparison but not always what you want to display. DATE_FORMAT() takes a date and a format string built from special placeholders, and returns a formatted string:

SELECT id, DATE_FORMAT(review_date, '%M %d, %Y') AS posted_on
FROM reviews
WHERE id = 203;

In the format string, %M means the full month name, %d the two-digit day, and %Y the four-digit year - MySQL supports many more placeholders for hours, minutes, weekday names, and abbreviated forms.

DATE vs DATETIME vs TIMESTAMP - Why It Still Matters

As introduced in Data Types, DATE stores just a calendar date, DATETIME stores a date and time with no awareness of timezone, and TIMESTAMP also stores a date and time but is stored internally in UTC and converted to the connection's timezone whenever it's read. That last part is the practical difference that actually bites people: a TIMESTAMP column shown to two users in different timezones will display differently for each of them, while a DATE or DATETIME value shows the exact same stored text no matter who's viewing it. For something like review_date, where StreamHub just wants "the calendar day this review was posted" recorded consistently, a plain DATE is the right choice - a TIMESTAMP would be more appropriate for something like a server-side "last modified" column where automatic timezone conversion for each viewer is actually useful.

When you need a difference measured in a specific unit rather than raw days, TIMESTAMPDIFF() fills that gap - it takes a unit (such as HOUR, MINUTE, or DAY) plus two datetime values, and returns the difference in exactly that unit, which DATEDIFF() can't do since it's always fixed to whole days.

-- Hours between two timestamps, instead of a raw day count
SELECT TIMESTAMPDIFF(HOUR, '2025-01-15 09:00:00', '2025-01-16 14:00:00') AS hours_between;