Dates: Beginner

Choose the right type for time data

SQL provides two core temporal types: DATE for calendar days and TIMESTAMP for precise moments. Choosing between them affects storage, query complexity, and what questions you can answer with your data.

DATE stores only the calendar date in YYYY-MM-DD format. No time component exists. It uses less storage and is simpler to compare. TIMESTAMP stores both date and time, providing the precision needed for event sequencing and duration calculations.

Write date values using the DATE keyword followed by a quoted string:

Write timestamp values using the TIMESTAMP keyword:

Choosing between DATE and TIMESTAMP affects storage, query complexity, and what questions you can answer with your data.

SELECT
  order_id,
  ___(order_time AS ___) AS order_date
FROM orders

DATE stores only year, month, and day. If you need to compare timestamps to CURRENT_DATE, cast the timestamp to DATE first so the time component does not prevent matches.

TIMESTAMP has more storage overhead than DATE. For columns that only ever need day-level precision, using the DATE type saves space and simplifies comparisons.

Choosing the right type at table creation prevents subtle bugs later. A TIMESTAMP column used for dates will cause every date comparison to include implicit time boundaries.

Reference today and right now in queries

CURRENT_DATE returns today's date according to the database server. It has no time component, giving you just the calendar date. This function takes no arguments and returns a date value that changes daily.

The syntax is simply CURRENT_DATE with no parentheses. It evaluates to the current date at the moment the query runs. The value remains constant throughout a single query execution.

These patterns show the most common ways to use CURRENT_DATE in real queries.

CURRENT_DATE enables dynamic queries that always reference today:

CURRENT_DATE is most commonly used in WHERE clauses to filter for today's records.

You can include CURRENT_DATE in the SELECT clause to label reports with their generation date.

CURRENT_DATE appears in many common data workflows.

Daily reports

Filtering today's records in dashboards and automated reports

Data pipelines

Setting default date values and creating partition keys for daily loads

Age calculations

Building age calculations from birth dates and event distances

While less common than CURRENT_DATE, this function is useful for time-of-day logic.

CURRENT_TIME returns the current time of day without the date component. It provides just the hours, minutes, and seconds according to the database server's clock.

When do you need CURRENT_DATE versus CURRENT_TIMESTAMP?

SELECT
  order_id,
  total
FROM orders
WHERE order_date ___ ___

CURRENT_DATE is evaluated once at the start of a query and remains constant throughout. Even if a query runs across midnight, every row sees the same date value.

CURRENT_TIME without a date is rarely used in practice. Most time-of-day filtering pairs the time with a date, making CURRENT_TIMESTAMP the more common choice for dynamic queries.

The database server timezone determines what CURRENT_DATE returns. For applications with global users, configure the server to UTC so all queries have a consistent reference point.

Capture exact moments with timestamps

CURRENT_TIMESTAMP returns the current date and time as a single timestamp value. It provides the complete moment, combining both calendar date and precise time. This is the most commonly used function for capturing "right now" in data pipelines.

CURRENT_TIMESTAMP requires no arguments and returns the complete date-time value.

These examples show how CURRENT_TIMESTAMP appears in practical queries.

CURRENT_TIMESTAMP is essential for audit trails, logging, and calculating time-based windows.

Combine CURRENT_TIMESTAMP with interval arithmetic to find records within a time window.

SELECT
  user_id,
  last_login
FROM users
WHERE last_login >= ___ - ___

INTERVAL arithmetic is portable across most SQL engines. The CURRENT_TIMESTAMP - INTERVAL pattern is safer than hardcoding a cutoff timestamp that would require updating manually.

For very large tables, filtering with CURRENT_TIMESTAMP can prevent partition pruning if the column is not a partition key. Check your query plan to ensure the database is applying the filter efficiently.

CURRENT_TIMESTAMP is evaluated once per query. Subtracting an interval from it inside a WHERE clause creates a fixed window for that query execution, ensuring consistent results across all returned rows.

Calculate future and past dates

DATE_ADD adds a specified time interval to a date or timestamp. It allows you to calculate future dates, deadlines, expiration times, and any temporal offset from a starting point.

The syntax is DATE_ADD(unit, amount, date). The unit specifies what to add (day, month, year, hour, etc.). The amount is how many units to add. Negative amounts subtract instead of add.

DATE_ADD handles the complexity of calendar math, including month lengths and leap years.

DATE_ADD supports multiple time units, each suited to different scheduling needs.

DATE_ADD('day', 30, invoice_date) adds 30 days to each invoice date. The result crosses month boundaries automatically. March 15 plus 30 days correctly becomes April 14.

DATE_ADD supports various time units, letting you add days, months, or years depending on your needs.

DATE_ADD works for subtraction too by using negative numbers.

DATE_ADD is essential for business logic involving deadlines, schedules, and time-based calculations.

SELECT
  ___(___, ___, ___) AS due_date

DATE_ADD with CURRENT_DATE creates self-updating deadlines. A query for "30 days from today" always reflects the current date, eliminating the need to update hardcoded deadline values.

When adding months, DATE_ADD handles varying month lengths automatically. Adding one month to January 31 gives February 28 or 29, not an error or an overflow into March.

Combine DATE_ADD with CURRENT_DATE in WHERE clauses to build rolling windows. For example, filtering where due_date between today and today plus 7 days finds items due this week.

Measure time between two events

DATE_DIFF calculates the difference between two dates or timestamps in a specified unit. It returns an integer representing how many complete units separate the two points in time.

The syntax is DATE_DIFF(unit, start_date, end_date). The unit specifies the measurement (day, month, year, hour, etc.). Positive values mean end_date is later; negative values mean end_date is earlier.

DATE_DIFF answers questions about time elapsed between events or from a reference point.

DATE_DIFF returns the number of complete units between two dates, with the result sign indicating direction.

DATE_DIFF('day', order_date, ship_date) counts complete days between ordering and shipping. Order 1001 took 3 days. Order 1003 took 8 days, potentially flagging a fulfillment issue.

DATE_DIFF works with various time units, letting you measure differences in days, months, or hours.

DATE_DIFF powers many business metrics including age calculations, tenure tracking, and SLA monitoring.

DATE_DIFF measures operational performance. An SLA defines the maximum acceptable response time that a service promises to meet:

DATE_DIFF is fundamental to analytics and operational reporting.

SELECT
  order_id,
  ___(
    ___,
    ___,
    ___
    ) AS days_to_ship
FROM orders

DATE_DIFF counts only complete units. If an order was placed at 11 PM and shipped at 1 AM the next day, DATE_DIFF in days returns 1 even though only 2 hours elapsed.

DATE_DIFF is the foundation for SLA tracking, cohort analysis, and retention calculations. Mastering the argument order and unit selection unlocks a wide range of business metrics.