How to Calculate Date Difference in MariaDB

Galaxy Glossary

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

DATEDIFF and TIMESTAMPDIFF return the number of days or custom time units between two dates in MariaDB.

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

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does DATEDIFF do in MariaDB?

DATEDIFF(start_date,end_date) returns the integer count of days from start_date to end_date. Negative values mean end_date precedes start_date. Use it for quick day-level gaps, such as customer lifetime or shipping delays.

When should I use TIMESTAMPDIFF instead?

TIMESTAMPDIFF(unit,start_date,end_date) lets you measure gaps in SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Choose it when you need granular control, like hours until an order ships or months since customer signup.

How do I calculate days between two order dates?

Join Orders to itself or another table, then call DATEDIFF(o2.order_date,o1.order_date). Alias the result as days_between to improve readability and reuse in reporting views.

Can I get months between signup and first order?

Yes. TIMESTAMPDIFF(MONTH,c.created_at,o.order_date) computes whole months. Combine with MIN(o.order_date) per customer to capture the first purchase accurately.

What’s the exact syntax of DATEDIFF and TIMESTAMPDIFF?

See the full syntax section below for parameter order, supported units, and null behavior. Copy-paste the ecommerce examples to test in your Galaxy SQL editor.

Best practices for date difference queries?

Cast strings to DATE before comparison, index date columns for speed, and always specify an alias. Wrap complex logic in a CTE to keep the main SELECT clean and maintainable.

How to avoid off-by-one errors?

Remember DATEDIFF counts full midnight boundaries. Add 1 day if your business logic treats the start date as an active day (e.g., hotel nights). Use TIMESTAMPDIFF for exact hours or minutes.

Common mistakes and fixes

See the end of the article for frequent pitfalls like parameter order mix-ups and mismatched data types, plus quick solutions.

FAQs on MariaDB date differences

Scroll to the FAQ section for answers on performance, timezone handling, and partial month calculations.

Why How to Calculate Date Difference in MariaDB is important

How to Calculate Date Difference in MariaDB Example Usage


-- How many days passed between consecutive orders for each customer?
WITH ranked_orders AS (
  SELECT id,
         customer_id,
         order_date,
         LAG(order_date) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
  FROM Orders
)
SELECT customer_id,
       id AS current_order_id,
       DATEDIFF(order_date, prev_order_date) AS days_since_last_order
FROM ranked_orders
WHERE prev_order_date IS NOT NULL;

How to Calculate Date Difference in MariaDB Syntax


-- Days between two dates
DATEDIFF(end_date, start_date)

-- Flexible unit difference
TIMESTAMPDIFF(unit, start_date, end_date)
-- unit: MICROSECOND | SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR

-- Example: Days between order placement and shipment
SELECT o.id,
       DATEDIFF(sh.shipped_at, o.order_date) AS days_to_ship
FROM Orders o
JOIN Shipments sh ON sh.order_id = o.id;

-- Example: Months between customer signup and first order
WITH first_order AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM Orders
  GROUP BY customer_id
)
SELECT c.id,
       TIMESTAMPDIFF(MONTH, c.created_at, f.first_order_date) AS months_to_first_purchase
FROM Customers c
JOIN first_order f ON f.customer_id = c.id;

Common Mistakes

Mixing up parameter order. DATEDIFF(end,start) subtracts start from end. Reversing parameters flips the sign, causing negative day counts. Always pass end_date first.
Comparing VARCHAR dates. String columns force implicit conversion, slow scans, and wrong results if format varies. CAST to DATE or store as DATE/TIMESTAMP for reliable math.

Frequently Asked Questions (FAQs)

Does DATEDIFF consider time zones?

No. DATEDIFF and TIMESTAMPDIFF work on native date/time values without timezone conversion. Convert to UTC before storing for consistent results.

Which is faster: DATEDIFF or TIMESTAMPDIFF?

Both are lightweight functions. Performance depends on indexed date columns and query plan, not the function itself.

How do I include the start day in the count?

Add 1 to the DATEDIFF result or use TIMESTAMPDIFF with HOUR and divide/round as needed to match business rules.

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.