SQL Date Differences: Calendar Days, Elapsed Time, and Dialects

Date differences start with a definition

“How many days?” can mean calendar-date boundaries or elapsed 24-hour periods. SQL engines expose different functions for those questions, and the same-looking timestamp type can mean different things across engines. This guide separates those semantics and supplies PostgreSQL 16 fixtures for midnight crossings, leap years, daylight-saving changes, age, and business calendars.

Choose the unit, direction, and time model

Write down start and end, whether the interval is half-open, the business timezone, and whether you need elapsed duration or calendar units. Fix the as-of value for reproducible reports. Never assume that a DATEDIFF expression can be copied unchanged between databases.

Function names are not a portable contract

The argument order differs. SQL Server DATEDIFF is not a general elapsed-duration measurement: a short interval spanning midnight crosses a day boundary. MySQL DATEDIFF similarly operates on dates, while TIMESTAMPDIFF is a separate function. These dialect descriptions come from Microsoft’s DATEDIFF reference and MySQL’s date/time reference. The executable examples below target PostgreSQL 16.

Know what the column stores

PostgreSQL DATE is a calendar date. TIMESTAMP WITHOUT TIME ZONE is a wall-clock value without a timezone or UTC offset. TIMESTAMP WITH TIME ZONE represents an instant, displayed according to the session timezone; it does not preserve the original named timezone. A timezone-less column only represents UTC by external convention. SQL Server timestamp is a synonym for rowversion, not a date/time column; use its actual date/time types. See PostgreSQL date/time types and Microsoft’s rowversion reference.

Compare calendar days with elapsed minutes

WITH x AS (
 SELECT TIMESTAMP '2026-01-01 23:59' AS start_ts,
        TIMESTAMP '2026-01-02 00:01' AS end_ts
)
SELECT end_ts::date-start_ts::date AS calendar_days,
 EXTRACT(EPOCH FROM end_ts-start_ts)/60 AS elapsed_minutes
FROM x;

calendar_days | elapsed_minutes
1 | 2.0000000000000000

Both numbers are correct under their respective definitions. Casting away the time before subtraction intentionally asks a different question. For real instants, calculate elapsed seconds from timezone-aware timestamps; do not subtract local wall-clock values and assume every day lasted 24 hours.

Test leap days and daylight-saving transitions

SELECT DATE '2024-03-01'-DATE '2024-02-28' AS forward_days,
       DATE '2024-02-28'-DATE '2024-03-01' AS reverse_days;

forward_days | reverse_days
2 | -2

A local calendar day can span 23 or 25 hours

WITH days(label,start_local,end_local) AS (
 VALUES ('spring',TIMESTAMP '2026-03-08 00:00',TIMESTAMP '2026-03-09 00:00'),
        ('fall',TIMESTAMP '2026-11-01 00:00',TIMESTAMP '2026-11-02 00:00')
)
SELECT label,end_local::date-start_local::date AS calendar_days,
 EXTRACT(EPOCH FROM (
  (end_local AT TIME ZONE 'America/Los_Angeles')-
  (start_local AT TIME ZONE 'America/Los_Angeles')))/3600 AS elapsed_hours
FROM days ORDER BY label;

label | calendar_days | elapsed_hours
fall | 1 | 25.0000000000000000
spring | 1 | 23.0000000000000000

The named zone interprets each local midnight using its applicable offset. These examples avoid ambiguous repeated times and nonexistent local times inside the transition. In ingestion pipelines, require a clear offset or ambiguity policy rather than silently guessing which occurrence of a repeated local clock time was meant. PostgreSQL documents AT TIME ZONE and interval arithmetic separately.

Age is not days divided by 365

SELECT FLOOR((DATE '2020-12-30'-DATE '1990-12-31')/365.0) AS naive_years,
 EXTRACT(YEAR FROM AGE(DATE '2020-12-30',DATE '1990-12-31')) AS calendar_years;

naive_years | calendar_years
30 | 29

The birthday has not occurred yet. AGE returns calendar-based components rather than a fixed number of seconds per year. Leap-day birthdays can require a business-specific anniversary rule; confirm it before using an age calculation for eligibility decisions.

Business days come from an explicit calendar

WITH calendar(day,is_business_day) AS (
 VALUES (DATE '2026-10-12',true),(DATE '2026-10-13',true),
        (DATE '2026-10-14',false),(DATE '2026-10-15',true),
        (DATE '2026-10-16',true),(DATE '2026-10-17',false)
)
SELECT COUNT(*) AS business_days FROM calendar
WHERE day>=DATE '2026-10-12' AND day<DATE '2026-10-17'
 AND is_business_day;

business_days
4

October 14 is a fictional company closure in this fixture, not a claim about a public holiday. A production calendar needs one row per date and the right region or operating schedule. Missing calendar rows are missing evidence, not automatically non-business days. The range includes the start date and excludes the end date.

Use explicit half-open report ranges

WITH events(id,event_ts) AS (
 VALUES (1,TIMESTAMP '2026-02-28 23:59:59'),
        (2,TIMESTAMP '2026-03-01 00:00:00'),
        (3,TIMESTAMP '2026-03-31 23:59:59.999999'),
        (4,TIMESTAMP '2026-04-01 00:00:00')
)
SELECT id FROM events
WHERE event_ts>=TIMESTAMP '2026-03-01 00:00:00'
 AND event_ts<TIMESTAMP '2026-04-01 00:00:00' ORDER BY id;

id
2
3

This avoids guessing a final representable instant of March. Applying a function to every stored timestamp, such as extracting its month, can also lose the year condition or complicate use of an ordinary index. Direct bounds are clear and can support index range access, but verify the actual plan; no expression guarantees a particular plan. Build local-month boundaries in the reporting timezone before comparing instant columns.

Parse once and keep typed values

Use typed values or bound date/time parameters instead of ambiguous strings such as 03/04/2026. Validate source formats at ingestion and retain errors for correction. Formatting a date as text is for display; it should not become the basis for chronological ordering or duration arithmetic. MySQL STR_TO_DATE and DATE_FORMAT are dialect-specific parsing and formatting tools, not PostgreSQL functions.

Explain the boundary cases

For an interview answer, name the engine first, then demonstrate a midnight crossing, a reversed interval, and a cutoff endpoint. Add timezone and holiday cases when the business definition needs them. A complete explanation identifies whether “seven days” means seven dates, 168 hours, or seven business days; these can produce different populations.

Make the definition visible in the query

Choose calendar or elapsed arithmetic intentionally, use a fixed reporting cutoff, and keep start/end inclusion explicit. Test the cases where time intuition fails instead of relying on an attractive function name.

FAQ

Does PostgreSQL have DATEDIFF? Not as the built-in function described for SQL Server or MySQL; use the appropriate subtraction or date/time expression. Is a timezone-aware result independent of timezone rules? No. Named-zone conversion depends on timezone data, while elapsed subtraction between already identified instants measures their actual difference.