Dates: Beginner

A DATE holds a calendar day and nothing smaller. A TIMESTAMP holds a calendar day plus a time of day, down to a fraction of a second. Printed side by side the 2 values look like cousins, which is how a column of the wrong type survives unnoticed for months. 2024-03-15 is a DATE. 2024-03-15 14:30:45 is a TIMESTAMP. The second answers a question the first cannot: at which moment?

A literal is the type name followed by the value in single quotes. The type name tells the engine to parse the quoted text as a day or a moment rather than keep it as text, so DATE '2024-03-15' compares as a day while '2024-03-15' on its own is a VARCHAR of 10 characters that compares letter by letter. Write the year first, then the month, then the day, each with its leading zero.

The bookings table in the next example mixes both. check_in and check_out are DATE columns because a guest arrives on a day and the hour is between the guest and the host. created_at is a TIMESTAMP because the moment a booking was made feeds fraud checks and lead-time reports. Run it and compare the last 2 columns: casting a TIMESTAMP to DATE keeps the day and drops the clock.

Casting a DATE up to a TIMESTAMP has to invent a time of day, and the engine chooses midnight. That midnight sits behind the most common date mistake in production queries. When a TIMESTAMP column is compared to a DATE literal, the literal is widened to midnight of its day before the comparison runs, so created_at = DATE '2024-03-14' asks for bookings created at exactly 00:00:00.000 on March 14, and almost nothing is created at that instant.

Both wrong choices cost something. A team that stores check-in as a TIMESTAMP pays 8 bytes a row where 4 would do and writes a cast into every day comparison. The column also accepts a clock reading. Once one source starts writing arrivals at 15:00 or 16:30, check_in = DATE '2024-03-15' silently drops exactly those rows while the midnight ones still match. A team that stores payment time as a DATE loses the hour for good and can never say which of 2 payments on the same day arrived first.

Every report that says 'as of today' needs the engine's clock. CURRENT_DATE returns the current day as a DATE and CURRENT_TIME returns the current time of day. Both are written without parentheses: the SQL standard defines them as keywords that stand for a value, and CURRENT_DATE() with parentheses is a syntax error. Across this lesson the clock is frozen at March 15, 2024, 14:30:45, so CURRENT_DATE is 2024-03-15 and every result below stays the same whichever day you run it.

A report written on March 15 with WHERE check_in >= DATE '2024-03-15' returns the right rows that day and the wrong rows every day after, because the literal never moves while the calendar does. Swapping the literal for CURRENT_DATE makes the same query correct on every run. Scheduled pipelines depend on this: the morning job that lists today's arrivals carries no date in its text, and the clock supplies one each time it starts.

CURRENT_DATE can also appear in the SELECT list, and so can a comparison against it, since check_in > CURRENT_DATE is a BOOLEAN expression like any other. Run the query and read the last column against the second.

Bookings 3 and 4 come back true: their stays begin after today. Booking 2 starts today and comes back false, because March 15 equals today rather than following it. Bookings 1 and 5 are already past. Swap > for >= and today's arrival joins the upcoming group; which of the 2 you want depends on whether the report runs before or after guests arrive. Today also has an end: booking 2 passes >= for any query started on March 15, and fails it for any query started on March 16, from midnight on.

The engine reads the clock once, when the query starts, and uses that value in every row and every clause. A query that scans 400 million rows over 10 minutes sees the same CURRENT_DATE in its last row as in its first, even if midnight passes while it runs. The day a query sees is the day it began: a job that starts at 23:59:30 on March 15 is a March 15 job all the way to its final row.

CURRENT_TIME returns the time of day with no date attached: here 14:30:45, followed by the zone it was read in. On its own it answers questions such as whether a support line is open right now. It cannot say which day it is, and most data work wants the day and the time together in one value. Run the query and see the 2 values side by side.

Flip the operator and the filter looks backward. check_in <= CURRENT_DATE keeps every stay that has already begun, today's included: on March 15 that keeps booking 2, which starts today, along with bookings 1 and 5. Drop the equals sign and booking 2 falls out, because a stay starting today does not start before today. The keyword is the same in both filters and is written bare, since CURRENT_DATE() fails to parse.

CURRENT_TIMESTAMP returns the date and the time of day in a single value: 2024-03-15 14:30:45.000 in this lesson, with the session's time zone attached. NOW(), parentheses included, is an alias for the same value. You will see both spellings in production code. Where CURRENT_DATE answers which day, CURRENT_TIMESTAMP answers which moment, which is what you need to stamp a row as it is written, to measure how long ago something happened, or to catch data that claims to come from the future.

run_at is identical in both rows because the clock is read once per query, exactly as with CURRENT_DATE. run_date casts the moment down to its day and lands on 2024-03-15, the value CURRENT_DATE would give; the cast is how you move from one to the other when a table stores moments and a report wants days.

An event table should never hold a row stamped later than now. When one appears, a device clock was wrong, a zone was mishandled upstream, or a test record leaked into production. The check is a single comparison against CURRENT_TIMESTAMP. In the events table, 2 rows carry an occurred_at past 14:30:45 on March 15. Step through the animation to see each row tested against the clock.

The question 'what happened today' is a day question asked of a moment column, so the column is cast to DATE and compared to CURRENT_DATE. Look at what this does with event 4: its timestamp is in the future, but its day is today, so it counts. A query that filters on the day and a query that filters on the moment can disagree about the same row, and each is right about what it asked.

Compare CURRENT_TIMESTAMP only to TIMESTAMP columns. Against a DATE column such as check_in, check_in = CURRENT_TIMESTAMP widens the date to midnight and tests it against 14:30:45, which is never equal; check_in > CURRENT_TIMESTAMP does work, but it reads as though the author lost track of which type they held.

DATE_ADD moves a date or a timestamp forward or back by a whole number of units. It takes 3 arguments in a fixed order: the unit as a quoted string, the amount as an integer, and the value to move. DATE_ADD('day', 14, check_out) is 14 days after check-out. A negative amount moves backward, so DATE_ADD('day', -7, CURRENT_DATE) is 2024-03-08, a week before today. The result keeps the type of the value you pass in: a DATE comes back a DATE and a TIMESTAMP comes back a TIMESTAMP, clock intact.

Airbnb gives guests 14 days after checkout to write a review, so every booking's deadline is a single call on check_out. Play the animation: each date walks into the new column with 14 days added, and 3 of the 5 land in April though no row mentions April. The engine handles the month rollover, including the 31 days in March.

The most common DATE_ADD in analytics never shows its result. It sits in a WHERE clause and defines a window that moves with the date: the last 7 days, the last 30 days, the last 24 hours. DATE_ADD('day', -7, CURRENT_DATE) evaluates to 2024-03-08, and >= against it keeps everything on or after that day. Because the cutoff is built from CURRENT_DATE, the same query run next week keeps next week's rows without an edit.

Of the 5 bookings, numbers 2 through 4 were created on or after March 8. Booking 1, created March 1, misses the cutoff by a week, and booking 5 by over a month. Two cutoffs that sound alike behave differently. DATE_ADD('day', -1, CURRENT_DATE) is 2024-03-14, so >= keeps all of yesterday and all of today, up to 2 full calendar days. DATE_ADD('hour', -24, CURRENT_TIMESTAMP) is 2024-03-14 14:30:45, the start of the 24 hours ending now. A row stamped yesterday at 09:00 passes the first and fails the second.

A 30-day window is written in days. DATE_ADD('day', -30, CURRENT_DATE) is 2024-02-14, exactly 30 days back. A month back looks similar and drifts: DATE_ADD('month', -1, CURRENT_DATE) is 2024-02-15, which spans 29 days this March, while from April 15 the same call reaches March 15, 31 days back. Use the day unit when a report promises a fixed count of days, and pair a DATE column with CURRENT_DATE so both sides of the comparison are days.

DATE_DIFF measures the distance between 2 moments in the unit you name. After the unit come the start and then the end, and the answer is end minus start: positive when the end is later, negative when the arguments arrive swapped. DATE_DIFF('day', DATE '2024-03-15', DATE '2024-03-18') is 3. Reverse the 2 dates and it is -3. The result is always a whole number of units, so a span of 5 hours and 30 minutes reports as 5 when asked in hours and as 330 when asked in minutes; the half hour is dropped, never rounded up.

Home-stay nights are the day difference between check-in and check-out. A guest who arrives March 15 and leaves March 18 sleeps there 3 nights, and DATE_DIFF('day', check_in, check_out) returns 3. Counting the calendar days the guest touched would give 4, the wrong number to bill. The animation computes the column for every booking; booking 5 crosses from February into March and still comes out at 3, because 2024 gives February a 29th day.

DATE_DIFF returns a number, so it works anywhere a number does. You can sum or average it, and filter on it in WHERE or carry it across a join. Booking lead time is the day gap between the moment a booking was created and the day the stay starts, a calendar question, so created_at is cast to DATE first. Predict the 3 rows before running: the nights booked per listing and the average lead in days.

Averages carry the sign of every difference inside them. Over the 5 bookings, DATE_DIFF('day', check_in, check_out) returns one night count per booking: 3, 3, 4, 1, 3. Swap the 2 columns and each value turns negative, so the Lisbon loft averages -3.0 nights instead of 3.0 and the Tahoe cabin -2.5, a report no host could read. Keep the earlier date second and the later date third inside the aggregate, as the SUM above does.