CURRENT_DATE & CURRENT_TIME

Concepts covered: Current Date

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.

Why a literal goes stale

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.

Hardcoded

• WHERE check_in >= DATE '2024-03-15'
• Correct on March 15
• Off by 1 day on March 16, by 2 on March 17

Clock-driven

• WHERE check_in >= CURRENT_DATE
• Correct on every run
• Nothing to edit when the date changes

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.

SELECT
  booking_id,
  check_in,
  CURRENT_DATE AS today,
  check_in > CURRENT_DATE AS is_upcoming
FROM bookings

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 same comparison in a WHERE clause keeps only the rows that pass. The animation filters to stays that begin today or later and counts them per listing. Watch the 2 rows that fail fall away before the survivors fold into groups.

SELECT
  listing_id,
  COUNT(*) AS upcoming_stays
FROM bookings
WHERE check_in >= CURRENT_DATE
GROUP BY listing_id

One reading per query

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

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.

SELECT
  booking_id,
  CURRENT_DATE AS today,
  CURRENT_TIME AS time_now
FROM bookings
WHERE booking_id = 1

Do

• Write CURRENT_DATE with no parentheses
• Compare CURRENT_DATE against DATE columns
• Cast a TIMESTAMP column to DATE before comparing it to today

Avoid

• Hardcode today's date as a literal in a scheduled report
• Compare CURRENT_DATE to a TIMESTAMP column with =, which matches only midnight
• Reach for CURRENT_TIME when the report also needs the day

On 2024-03-15 this query returned bookings 3 and 4. It runs again, unchanged, on 2024-03-16. Which bookings come back?

SELECT
  booking_id
FROM bookings
WHERE check_in > CURRENT_DATE

• Bookings 3 and 4, because the query text has not changed between the 2 runs
• Bookings 2, 3 and 4, because today moved forward and more stays now count as upcoming
• Booking 4 only, because booking 3 starts on March 16 and March 16 is not greater than March 16
• No bookings, because CURRENT_DATE now points to a day on which nobody checks in

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.

About this lesson

This section is part of the Dates: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.