DATE vs TIMESTAMP data types

Concepts covered: Date vs Timestamp

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?

Questions each type answers

• DATE: which stays begin this week, and how many nights lie between check-in and check-out
• TIMESTAMP: whether a payment landed before the cancellation, and how many minutes a host took to reply

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.

Choosing between them

Store as DATE

• Check-in and check-out days
• Birthdays and due dates
• The day a daily report covers
• Anything a person writes on a calendar

Store as TIMESTAMP

• The moment a payment was captured
• When a row was last updated
• Events such as clicks or messages
• Anything measured at a finer grain than a day

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.

SELECT
  booking_id,
  check_in,
  created_at,
  CAST(created_at AS DATE) AS created_day
FROM bookings

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.

SELECT
  booking_id,
  created_at
FROM bookings
WHERE created_at = DATE '2024-03-14'

One row comes back, booking 2, which happens to carry a midnight stamp. Booking 3 was created at 18:45:10 the same day and fails the test, because 18:45:10 is later than 00:00:00. To ask about the whole day, bring the column down to the literal's type with a cast, so both sides are days. Play the animation and watch the comparison turn true for 2 rows and false for 3.

SELECT
  booking_id,
  created_at
FROM bookings
WHERE CAST(created_at AS DATE) = DATE '2024-03-14'

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.

Booking 2 has check_in 2024-03-15. What does this query return?

SELECT
  CAST(check_in AS TIMESTAMP) AS check_in_ts
FROM bookings
WHERE booking_id = 2

• 2024-03-15 00:00:00, because a DATE has no time of day and the engine supplies midnight
• 2024-03-15 14:30:45, because the engine fills the gap with the current time of day
• An error, because a DATE holds no time and cannot be widened to a TIMESTAMP
• 2024-03-15 12:00:00, because the engine picks the middle of the day as the least biased time

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.