CURRENT_TIMESTAMP function

Concepts covered: Current Date

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.

SELECT
  event_id,
  occurred_at,
  CURRENT_TIMESTAMP AS run_at,
  CAST(CURRENT_TIMESTAMP AS DATE) AS run_date
FROM events
WHERE event_id IN (1, 4)

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.

Catching events from the future

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.

SELECT
  event_id,
  event_type,
  occurred_at
FROM events
WHERE occurred_at > CURRENT_TIMESTAMP

Events 4 and 6 fail the sanity check. Event 3, stamped 14:29:59, passes by a single second, which shows how fine the test is: a TIMESTAMP comparison is decided at the millisecond, and a value one tick earlier than now belongs to the past.

Today's events, by day

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.

SELECT
  event_type,
  COUNT(*) AS events_today
FROM events
WHERE CAST(occurred_at AS DATE) = CURRENT_DATE
GROUP BY event_type

• loaded_at columns that record the moment a pipeline wrote each row
• Freshness checks that compare the newest row's stamp to now
• Future-stamp sanity filters on event streams
• Run logs that store when each job started and finished

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.

A query selects CURRENT_TIMESTAMP next to every row of a 50 million row table and takes 6 minutes to finish. How does the value in the last row differ from the first?

• The last row is about 6 minutes later, because each row reads the clock as it is produced
• The last row is later by 1 millisecond per row, because the engine advances the stamp to keep rows distinct
• The last row is NULL, because the clock is read only while the first row is produced
• They are identical, because the engine takes a single clock reading at the start and copies it to every row

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.