When you’re working with databases, dates don’t always behave. One of the most common questions is: “How do I calculate the difference between two dates in SQL?”. Whether you’re calculating someone’s age or figuring out how many days are left in a subscription, these time-based calculations are crucial.

TL;DR

SQL provides several ways to find the difference between dates. DATEDIFF() is the go-to for most SQL flavors, especially with days. But depending on the SQL dialect (like MySQL, PostgreSQL, or SQL Server), the syntax can change a bit. You can also extract specific units, like hours or minutes. This article shows you how to work with all of them!

Why would you ever want date intervals?

Lots of reasons!

• How long has a user been active?
• What’s the time between orders?
• When does this subscription expire?
• Are we overdue on a project?

Basically, if there’s a clock or a calendar involved, you’re going to need to calculate something. Let’s break it down with some real fun.

SQL and Time – Not Always a Perfect Match

SQL loves structured data. But date-time math? It can get quirky.

There are a few built-in SQL functions to help out. Depending on which SQL database you’re using, things might look slightly different. Here’s how it generally works across some popular platforms.

1. DATEDIFF() – The Star of the Show

This is the easiest and most-used function to get the difference between two dates. It tells you the count of the specified unit—usually days.

MySQL Syntax:

SELECT DATEDIFF('2024-06-10', '2024-06-01') AS days_difference;

SQL Server Syntax:

SELECT DATEDIFF(day, '2024-06-01', '2024-06-10') AS days_difference;

PostgreSQL? You do things a bit differently:

SELECT '2024-06-10'::date - '2024-06-01'::date AS days_difference;

Notice anything? The order of parameters can differ! SQL Server is more literal: “start, end”. MySQL and PostgreSQL just subtract like math class.

2. TIMESTAMPDIFF() – For Power Users

Want more than just days? Like months? Or minutes? Try TIMESTAMPDIFF(). It’s supported in MySQL and MariaDB mainly.

Example:

SELECT TIMESTAMPDIFF(MONTH, '2021-01-01', '2024-06-01') AS month_diff;

This gives you months between the two dates (in this case, 41!). You can also use:

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• YEAR

Just pass that unit as the first argument. It’s like ordering from a menu.

3. Interval Arithmetic – PostgreSQL’s Superpower

PostgreSQL gives you something wonderful: INTERVAL.

Here’s how it looks:

SELECT AGE('2024-06-01'::date, '2000-06-01'::date);

This returns something like: 24 years. Yep, PostgreSQL calculates age. Like, actual age!

You can also get wild with intervals:

SELECT NOW() + INTERVAL '2 months';

This adds 2 months to the current date and time.

4. Working with Time – Not Just Dates

Need the gap between timestamps, not dates? Time to break out seconds, minutes, and hours.

Calculate hours or minutes in MySQL:

SELECT TIMESTAMPDIFF(HOUR, '2024-06-01 10:00:00', '2024-06-02 15:30:00') AS hour_diff;

Or if you want something precise in PostgreSQL:

SELECT EXTRACT(EPOCH FROM ('2024-06-02 15:30:00'::timestamp - '2024-06-01 10:00:00'::timestamp)) / 3600 AS hour_diff;

This gives you fractional hours. Super handy for billing systems or work logs!

Fun With Functions

You can also create user-defined functions. Here’s a tiny one (in SQL Server) that returns how many years passed:

CREATE FUNCTION dbo.GetYearsPassed
(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @StartDate, @EndDate)
END

Then use it like this:

SELECT dbo.GetYearsPassed('1990-01-01', GETDATE());

Neat, right?

Edge Cases – Watch Out!

Dates snipe back when you’re not careful. A few things to keep in mind:

• Time zone: Stored time may be in UTC but shown in local time.
• Null values: Always check for NULLs before calculating anything.
• Leap years: February 29 isn’t just there to confuse you—it will mess with your math!
• Time portion: If using DATETIME or TIMESTAMP, don’t forget the hours and minutes. It’s not just about the date!

Real Life Examples

Subscription Ending

SELECT user_id, DATEDIFF(subscription_end, NOW()) AS days_left
FROM subscriptions
WHERE subscription_end > NOW();

Employee Anniversary Tracker

SELECT name,
       AGE(NOW(), hire_date) AS time_at_company
FROM employees;

Orders In Last 7 Days

SELECT order_id, order_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7' DAY;

Tip: Displaying Intervals Nicely

Sometimes intervals return weird things like “2 years 3 mons 5 days“. Want a pretty format?

Try concatenating like this in PostgreSQL:

SELECT EXTRACT(YEAR FROM AGE(NOW(), '2000-01-01')) || ' years, ' ||
       EXTRACT(MONTH FROM AGE(NOW(), '2000-01-01')) || ' months' AS pretty_age;

Now your boss thinks you’re a wizard 🧙‍♂️.

Recap

You’ve just time traveled through the world of SQL date differences! Here’s the rundown:

• DATEDIFF() is your day-counting pal 🎯
• TIMESTAMPDIFF() gives you granular control
• PostgreSQL’s INTERVAL can do wild stuff
• Edge cases are everywhere—be careful out there

Final Thought

Dates and times may look harmless, but in SQL they can trip you up. Thankfully, with a few functions and tricks, you’ve got the power to calculate any interval you want. Whether it’s years, months, days, or minutes—you’re now equipped to make every second count.