Introduction

Calculating differences between dates in MySQL depends on what unit you need, such as days, months, seconds, or exact interval strings. The most important choice is selecting the right function for business semantics. Misusing date-difference functions often causes off-by-one errors and inconsistent reporting.

Core Sections

Use DATEDIFF for Day Differences

DATEDIFF returns the number of days between two dates, ignoring time components.

SELECT DATEDIFF('2026-03-10', '2026-03-01') AS days_diff;

This is ideal for date-only comparisons such as billing cycles by day count.

Use TIMESTAMPDIFF for Specific Units

If you need hours, minutes, months, or years, use TIMESTAMPDIFF.

SELECT TIMESTAMPDIFF(HOUR, '2026-03-01 08:00:00', '2026-03-02 14:30:00') AS hours_diff;
SELECT TIMESTAMPDIFF(MONTH, '2025-01-15', '2026-03-04') AS months_diff;

TIMESTAMPDIFF gives integer unit differences, which is useful for reporting rules.

Handle Signed Differences Intentionally

Both functions can return negative values when argument order is reversed.

SELECT DATEDIFF('2026-03-01', '2026-03-10') AS signed_days;
SELECT ABS(DATEDIFF('2026-03-01', '2026-03-10')) AS absolute_days;

Use signed differences when direction matters, or ABS when only distance matters.

Date and Datetime Type Considerations

DATEDIFF ignores time of day, while TIMESTAMPDIFF can consider exact timestamps. Be explicit about which behavior your business logic needs.

SELECT DATEDIFF('2026-03-04 23:59:59', '2026-03-04 00:00:00') AS date_only;
SELECT TIMESTAMPDIFF(SECOND, '2026-03-04 00:00:00', '2026-03-04 23:59:59') AS seconds_exact;

Incorrect function choice is a common source of subtle bugs.

Time Zone and UTC Practices

If your data spans time zones, store timestamps in UTC and convert only for presentation. Computing differences across mixed zones without normalization can produce misleading results.

Real Query Example in Tables

1SELECT
2    order_id,
3    created_at,
4    delivered_at,
5    TIMESTAMPDIFF(HOUR, created_at, delivered_at) AS delivery_hours
6FROM orders
7WHERE delivered_at IS NOT NULL;

Pair this with filters and indexes on date columns for performance.

Performance Notes

Date calculations on indexed columns are usually fast when functions are applied in select list, but applying functions to indexed columns in predicates can reduce index effectiveness. Keep predicates sargable when possible.

Business-rule Examples by Domain

Different products require different interpretations of date differences. Subscription billing may count whole days, while SLA monitoring may need minutes. Human resources tenure calculations might need month or year boundaries. Choosing one function globally can break domain-specific expectations.

1-- Billing example
2SELECT account_id, DATEDIFF(next_invoice_date, current_date) AS days_until_invoice
3FROM subscriptions;
4
5-- SLA example
6SELECT ticket_id, TIMESTAMPDIFF(MINUTE, opened_at, resolved_at) AS resolution_minutes
7FROM support_tickets;

Separate domain calculations in dedicated views so each metric stays explicit and maintainable.

Null-safe Date Difference Patterns

When one date can be null, guard calculations with CASE or COALESCE.

1SELECT
2    order_id,
3    CASE
4        WHEN delivered_at IS NULL THEN NULL
5        ELSE TIMESTAMPDIFF(HOUR, created_at, delivered_at)
6    END AS delivery_hours
7FROM orders;

Null-safe expressions prevent misleading zeros in reports and make incomplete process states visible for operational tracking.

For auditability, store both raw timestamps and computed interval metrics so downstream teams can recalculate differences with updated business rules when needed. This preserves analytical flexibility and reduces costly backfills.

Combining domain-specific views with clear naming conventions reduces confusion when similar metrics are calculated in different units such as days and minutes. It also improves maintainability as product requirements evolve.

Well-documented interval definitions improve trust in analytics outputs.

Common Pitfalls

• Using DATEDIFF when time-of-day precision is required.
• Forgetting function results can be negative depending on argument order.
• Ignoring timezone normalization in multi-region datasets.
• Comparing date strings without proper date types.
• Applying date functions in where clauses that block index usage.

Summary

• Use DATEDIFF for day-level differences.
• Use TIMESTAMPDIFF for unit-specific datetime differences.
• Handle signed and absolute differences deliberately.
• Normalize timestamps to UTC for cross-timezone correctness.
• Choose function semantics that match business rules.