DAYS_BETWEEN

Return the number of whole days between two dates as date1 minus date2.

Category: datetimeReturns: INTEGERDialect: PostgreSql

Syntax

DAYS_BETWEEN(date1, date2)

Description

## Overview Returns the number of whole days between two dates, computed as date1 minus date2. Positive when date1 is later than date2, negative when date1 is earlier, zero for identical dates. Functionally equivalent to DATEDIFF and to DATE_DIFF('day', date2, date1); exposed as a dedicated function for readability in code that distinguishes day-based age metrics from other unit-based differences. ## Behavior - Returns an INTEGER number of days. - Returns NULL if either argument is NULL. - Argument order is (later, earlier); reversing the order negates the result. - Counts whole calendar days. - DATE inputs only; TIMESTAMP is cast to DATE, dropping time-of-day. - Works across year and month boundaries naturally. ## Compatibility - Day-based DATE difference helper. Equivalent to (date1 - date2) in the standard 'date minus date = INTEGER days' semantics.

Parameters

Name	Type	Description
`date1`		Specifies the first (later) date. The result is computed as date1 minus date2.
`date2`		Specifies the second (earlier) date.

Examples

-- Days between two fixed dates
SELECT DAYS_BETWEEN(DATE '2025-03-15', DATE '2025-01-01') AS days;

-- Negative result when date1 is earlier
SELECT DAYS_BETWEEN(DATE '2025-01-01', DATE '2025-03-15') AS days;

-- Days since each event
SELECT event_name, DAYS_BETWEEN(CURRENT_DATE(), event_date) AS days_since
FROM analytics.events.user_activity;

-- Days until project deadline
SELECT project_id, DAYS_BETWEEN(deadline, CURRENT_DATE()) AS days_remaining
FROM project_mgmt.core.projects;

-- Group customers by days since last purchase
SELECT FLOOR(DAYS_BETWEEN(CURRENT_DATE(), last_purchase_date) / 7) AS weeks_ago,
       COUNT(*) AS customers
FROM commerce.customers.segments
GROUP BY weeks_ago
ORDER BY weeks_ago;

Pitfalls

DAYS_BETWEEN takes (later, earlier), not (earlier, later). Reversing the arguments negates the result.
TIMESTAMP inputs are cast to DATE before differencing, so a gap of 23 hours within the same calendar day yields 0 days. Use DATE_DIFF('hour', ...) when time-of-day matters.
The result is signed. Wrap with ABS() if you only care about magnitude.