How to Calculate Date Difference in ParadeDB (PostgreSQL)

Galaxy Glossary

How do I calculate the difference between two dates in ParadeDB?

Return the interval or exact number of days between two date-time values using built-in arithmetic or AGE().

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why does ParadeDB use subtraction instead of DATEDIFF?

PostgreSQL—on which ParadeDB is built—lets you subtract one date or timestamp from another. The result is an interval, eliminating the need for a separate DATEDIFF function.

How do I get the number of full days between two dates?

Use the date type or cast to ::date before subtraction, then apply EXTRACT(day FROM ...) or interval '1 day' division.

Example

SELECT (o.order_date::date - c.created_at::date) AS days_gap FROM Customers c JOIN Orders o ON o.customer_id = c.id;

When should I use AGE() instead?

AGE(later, earlier) normalizes the interval into years, months, and days—helpful for customer anniversaries or product warranty checks.

What is the exact syntax?

See the next section for full syntax options, including interval extraction and epoch conversion.

Why How to Calculate Date Difference in ParadeDB (PostgreSQL) is important

How to Calculate Date Difference in ParadeDB (PostgreSQL) Example Usage


-- How many hours passed between order placement and first item being shipped?
SELECT o.id,
       EXTRACT(epoch FROM (s.shipped_at - o.order_date)) / 3600 AS hours_to_ship
FROM Orders o
JOIN (
    SELECT order_id, MIN(updated_at) AS shipped_at
    FROM OrderItems
    WHERE status = 'shipped'
    GROUP BY order_id
) s ON s.order_id = o.id
WHERE o.id = 101;

How to Calculate Date Difference in ParadeDB (PostgreSQL) Syntax


-- Basic subtraction (interval result)
SELECT later_timestamp - earlier_timestamp AS diff;

-- Full-days difference
SELECT EXTRACT(day FROM (later_date::date - earlier_date::date)) AS days;

-- Using AGE() for y/m/d breakdown
SELECT AGE(later_timestamp, earlier_timestamp) AS age_interval;

-- Convert to seconds
SELECT EXTRACT(epoch FROM (later_timestamp - earlier_timestamp)) AS seconds_diff;

-- E-commerce context: Days from signup to first order
SELECT o.id,
       (o.order_date::date - c.created_at::date) AS days_to_first_order
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE o.id = 101;

Common Mistakes

Using a non-existent DATEDIFF function. Fix: subtract dates directly or call AGE().
Forgetting to cast timestamps to DATE, which returns partial days. Fix: use ::date for whole-day counts.

Frequently Asked Questions (FAQs)

Does ParadeDB support DATEDIFF?

No. Use date arithmetic or the AGE() function instead.

How do I ignore time zones?

Cast both values to ::date before subtraction, or convert them to the same time zone with AT TIME ZONE.

Can I get the result in months?

Yes. Use AGE() and extract year and month parts, or divide the day count by 30 if approximate values are acceptable.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie