Datetime Arithmetic and Interval Calculations in SQL: Converting, Truncating, and Computing Durations Across Database Platforms

Introduction

Imagine you're a data analyst at a subscription software company. Your product manager walks over and asks: "How long does it typically take a new user to upgrade from a free trial to a paid plan? And how many users churned within the first 30 days last quarter?" These feel like natural business questions — but underneath them is a surprisingly complex layer of SQL work. You need to subtract dates, compare timestamps, group results by week, and handle the fact that your cloud data warehouse might behave differently than your colleague's local database. That's datetime arithmetic, and it's one of the most practically important skills in SQL.

The challenge with dates and times in SQL isn't that any single concept is hard — it's that the syntax varies significantly across database platforms, and the edge cases are subtle enough to produce wrong answers that look right. A duration calculation that's off by a few hours due to a timezone mismatch, or a cohort analysis that silently groups records incorrectly because of how truncation works — these are real bugs that reach real reports. Understanding what's happening under the hood is the only reliable defense.

By the end of this lesson, you'll be able to confidently work with dates and times in SQL across the major platforms. You'll know how to convert between date types, truncate timestamps to useful grains like week or month, subtract dates to compute durations, and use interval arithmetic to shift dates forward or backward in time.

What you'll learn:

• How SQL stores and represents date, time, and timestamp values
• How to cast and convert between datetime data types
• How to truncate a timestamp to a specific unit (day, week, month, year)
• How to compute durations between two dates or timestamps
• How to add and subtract intervals to shift dates in time
• Key syntax differences across PostgreSQL, MySQL, BigQuery, and SQL Server

---

Prerequisites

You should be comfortable writing basic SELECT statements with WHERE and GROUP BY clauses. You don't need prior experience with date functions, but you should understand what a data type is — the idea that a column storing phone numbers is fundamentally different from a column storing prices, even if both look like numbers.

---

How SQL Thinks About Dates and Times

Before you can do arithmetic with dates, you need to understand how a database actually stores them. Under the hood, most databases don't store a date as the string "2024-03-15" — they store it as a structured binary value that the display layer formats for you. This matters because it means arithmetic on dates isn't string manipulation; it's more like arithmetic on a specialized numeric type.

SQL has several distinct date/time data types, and confusing them is a common source of errors:

• DATE: Stores just a calendar date — year, month, and day. No time component. 2024-03-15.
• TIME: Stores just a time of day. 14:30:00. Rarely used in analytics.
• TIMESTAMP (or DATETIME in some systems): Stores both date and time. 2024-03-15 14:30:00.
• TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ in PostgreSQL): Stores a timestamp plus timezone offset information. Critical for global applications.

The distinction between TIMESTAMP and TIMESTAMP WITH TIME ZONE deserves emphasis. If your application records user activity in UTC but you're doing analysis in Eastern Time, using the wrong type means your "daily" counts can silently include the wrong hours. Always know whether your timestamps are timezone-aware.

---

Casting and Converting Between Date Types

Real-world data rarely arrives in the exact type you need. Log data might store timestamps as plain strings. A legacy system might use VARCHAR columns for dates. Before you can do any arithmetic, you need to convert those values into proper date types.

The standard SQL way to change a value's type is CAST():

-- Convert a string to a DATE
SELECT CAST('2024-03-15' AS DATE);

-- Convert a string to a TIMESTAMP
SELECT CAST('2024-03-15 14:30:00' AS TIMESTAMP);

Most databases also support a shorthand :: syntax (PostgreSQL and BigQuery):

-- PostgreSQL / BigQuery shorthand
SELECT '2024-03-15'::DATE;
SELECT '2024-03-15 14:30:00'::TIMESTAMP;

SQL Server uses CONVERT() with a style code, or the cleaner TRY_CAST() which returns NULL instead of throwing an error when the value doesn't parse:

-- SQL Server
SELECT CONVERT(DATE, '2024-03-15');
SELECT TRY_CAST('2024-03-15' AS DATE);  -- safer: returns NULL on failure

MySQL uses STR_TO_DATE() when the string format is non-standard:

-- MySQL: parsing a non-standard date format
SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y');

You'll also frequently need to extract just the date portion from a timestamp. Every platform handles this slightly differently:

-- PostgreSQL
SELECT created_at::DATE FROM users;

-- MySQL
SELECT DATE(created_at) FROM users;

-- SQL Server
SELECT CAST(created_at AS DATE) FROM users;

-- BigQuery
SELECT DATE(created_at) FROM users;

---

Truncating Timestamps to a Useful Grain

One of the most common operations in analytics is grouping events by time period — by day, week, month, or quarter. The function for this is called date truncation: you're chopping off the sub-period precision and snapping the timestamp back to the start of the period it falls in.

Think of it like rounding, but always rounding down. If a user signed up at 2024-03-15 14:32:07 and you truncate to month, you get 2024-03-01 00:00:00. The value now represents "March 2024" as a single anchor point, which makes grouping and sorting natural.

PostgreSQL and BigQuery use DATE_TRUNC():

-- PostgreSQL / BigQuery
SELECT
    DATE_TRUNC('month', created_at) AS signup_month,
    COUNT(*) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

This returns a row per calendar month showing how many users signed up. The first argument is a string specifying the truncation unit. Valid values include: 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'.

MySQL doesn't have DATE_TRUNC(). You simulate it with formatting and re-parsing, or with arithmetic:

-- MySQL: truncate to month
SELECT
    DATE_FORMAT(created_at, '%Y-%m-01') AS signup_month,
    COUNT(*) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

-- MySQL: truncate to week (Monday-based)
SELECT
    DATE(created_at) - INTERVAL (WEEKDAY(created_at)) DAY AS week_start,
    COUNT(*) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

SQL Server uses DATETRUNC() (available from SQL Server 2022) or the older pattern using DATEADD and DATEDIFF:

-- SQL Server 2022+
SELECT
    DATETRUNC(month, created_at) AS signup_month,
    COUNT(*) AS new_users
FROM users
GROUP BY DATETRUNC(month, created_at)
ORDER BY 1;

-- SQL Server (older versions): truncate to month
SELECT
    DATEADD(month, DATEDIFF(month, 0, created_at), 0) AS signup_month,
    COUNT(*) AS new_users
FROM users
GROUP BY DATEADD(month, DATEDIFF(month, 0, created_at), 0)
ORDER BY 1;

The older SQL Server pattern (DATEADD(month, DATEDIFF(month, 0, ...), 0)) looks cryptic but it's standard idiom: DATEDIFF counts the number of complete months since epoch (day 0), then DATEADD applies that many months back to epoch. The result is the first of the month at midnight.

---

Computing Durations Between Two Dates

Now for the core of datetime arithmetic: how long did something take? How many days between signup and first purchase? How many months has a subscription been active?

Simple Date Subtraction

In PostgreSQL, subtracting two DATE values returns an INTEGER representing the number of days:

-- Days between two dates in PostgreSQL
SELECT
    user_id,
    first_purchase_date - signup_date AS days_to_first_purchase
FROM user_conversions;

Subtracting two TIMESTAMP values in PostgreSQL returns an INTERVAL — a structured duration value rather than a plain number:

-- PostgreSQL: interval result from timestamp subtraction
SELECT
    session_end - session_start AS session_duration
FROM user_sessions;
-- Returns: 0:02:35 (2 minutes, 35 seconds)

To extract a numeric value from that interval, use EXTRACT() or EPOCH:

-- PostgreSQL: get total seconds from an interval
SELECT
    EXTRACT(EPOCH FROM (session_end - session_start)) AS duration_seconds
FROM user_sessions;

-- Convert to minutes
SELECT
    EXTRACT(EPOCH FROM (session_end - session_start)) / 60 AS duration_minutes
FROM user_sessions;

Using DATEDIFF

MySQL and SQL Server use DATEDIFF(), which always returns an integer and requires you to specify the unit:

-- MySQL: days between two dates
SELECT
    user_id,
    DATEDIFF(first_purchase_date, signup_date) AS days_to_first_purchase
FROM user_conversions;

-- SQL Server: days between two dates
SELECT
    user_id,
    DATEDIFF(day, signup_date, first_purchase_date) AS days_to_first_purchase
FROM user_conversions;

BigQuery uses DATE_DIFF() with the unit as the third argument:

-- BigQuery
SELECT
    user_id,
    DATE_DIFF(first_purchase_date, signup_date, DAY) AS days_to_first_purchase
FROM user_conversions;

Note that in BigQuery the unit is an unquoted keyword (DAY), not a string ('day'). This is a common syntax gotcha.

A Practical Duration Query

Here's a realistic query that computes trial-to-paid conversion time and buckets users into cohorts:

-- PostgreSQL: distribution of trial-to-paid conversion times
SELECT
    CASE
        WHEN days_to_upgrade <= 7  THEN '0-7 days'
        WHEN days_to_upgrade <= 14 THEN '8-14 days'
        WHEN days_to_upgrade <= 30 THEN '15-30 days'
        ELSE '30+ days'
    END AS conversion_bucket,
    COUNT(*) AS users
FROM (
    SELECT
        user_id,
        (upgraded_at::DATE - trial_start_date) AS days_to_upgrade
    FROM subscriptions
    WHERE upgraded_at IS NOT NULL
) sub
GROUP BY 1
ORDER BY
    MIN(days_to_upgrade);

---

Adding and Subtracting Intervals

Beyond measuring duration, you often need to shift a date — find the date 30 days from now, or one month before an event. This is interval arithmetic.

PostgreSQL has the most expressive syntax, using the INTERVAL keyword with natural language-like strings:

-- PostgreSQL interval arithmetic
SELECT
    NOW() + INTERVAL '30 days' AS thirty_days_from_now,
    NOW() - INTERVAL '3 months' AS three_months_ago,
    created_at + INTERVAL '1 year' AS one_year_anniversary
FROM users;

BigQuery uses DATE_ADD() and DATE_SUB():

-- BigQuery
SELECT
    DATE_ADD(signup_date, INTERVAL 30 DAY) AS trial_end_date,
    DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH) AS three_months_ago
FROM users;

MySQL uses the same DATE_ADD() / DATE_SUB() pattern as BigQuery:

-- MySQL
SELECT
    DATE_ADD(signup_date, INTERVAL 30 DAY) AS trial_end_date,
    DATE_SUB(NOW(), INTERVAL 3 MONTH) AS three_months_ago
FROM users;

SQL Server uses DATEADD():

-- SQL Server
SELECT
    DATEADD(day, 30, signup_date) AS trial_end_date,
    DATEADD(month, -3, GETDATE()) AS three_months_ago
FROM users;

In SQL Server, you subtract by using a negative integer, which is less readable but functionally correct.

---

Getting the Current Date and Time

Every platform provides functions to get the current date or timestamp, and they all differ slightly:

Platform	Current Date	Current Timestamp
PostgreSQL	CURRENT_DATE	NOW() or CURRENT_TIMESTAMP
MySQL	CURDATE()	NOW()
SQL Server	CAST(GETDATE() AS DATE)	GETDATE() or SYSDATETIME()
BigQuery	CURRENT_DATE()	CURRENT_TIMESTAMP()

A practical use: filtering to records from the past 30 days.

-- PostgreSQL
WHERE created_at >= NOW() - INTERVAL '30 days'

-- MySQL
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)

-- SQL Server
WHERE created_at >= DATEADD(day, -30, GETDATE())

-- BigQuery
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

---

Hands-On Exercise

Work through these exercises using a database platform of your choice. If you don't have a local database, both DB Fiddle (dbfiddle.uk) and BigQuery Sandbox (free with a Google account) let you run SQL in a browser.

Setup: Create and populate a sample table with this DDL and data (shown in PostgreSQL syntax — adapt as needed):

CREATE TABLE subscriptions (
    user_id      INT,
    plan_type    VARCHAR(20),
    trial_start  TIMESTAMP,
    upgraded_at  TIMESTAMP,
    churned_at   TIMESTAMP
);

INSERT INTO subscriptions VALUES
(1, 'trial', '2024-01-05 09:00:00', '2024-01-12 14:30:00', NULL),
(2, 'trial', '2024-01-08 11:00:00', '2024-01-25 10:00:00', NULL),
(3, 'trial', '2024-01-10 08:00:00', NULL,                  '2024-01-28 00:00:00'),
(4, 'trial', '2024-02-01 12:00:00', '2024-02-05 16:00:00', NULL),
(5, 'trial', '2024-02-14 09:30:00', NULL,                  '2024-03-01 00:00:00');

Exercise 1: For users who upgraded, calculate the number of days between trial start and upgrade. Order by conversion speed, fastest first.

Exercise 2: Find all users who churned within 30 days of starting their trial.

Exercise 3: Show the count of trial starts per month. Use date truncation so January and February appear as 2024-01-01 and 2024-02-01 respectively.

Exercise 4: Add a column showing each user's trial expiry date, defined as 14 days after trial_start.

Expected results for Exercise 1:

• User 4: 4 days
• User 1: 7 days
• User 2: 17 days

Take a pass at each exercise before reading on. If you're stuck on Exercise 2, remember that you can use interval arithmetic inside a WHERE clause.

---

Common Mistakes & Troubleshooting

Comparing timestamps to dates without explicit casting A WHERE created_at = '2024-03-15' clause may silently return zero rows if created_at is a timestamp and the database doesn't implicitly cast the string to TIMESTAMP. The timestamp 2024-03-15 09:00:00 doesn't equal the date 2024-03-15. Use created_at::DATE = '2024-03-15' or created_at BETWEEN '2024-03-15 00:00:00' AND '2024-03-15 23:59:59'.

DATEDIFF argument order varies by platform MySQL: DATEDIFF(end, start). SQL Server: DATEDIFF(unit, start, end). Getting this backwards produces negative numbers that are easy to miss in a large result set. Always spot-check a few known rows.

DATE_TRUNC on weeks starts on Monday in PostgreSQL If your downstream reporting uses Sunday as the week start (common in US business contexts), your cohorts will be silently misaligned. Test explicitly: SELECT DATE_TRUNC('week', '2024-03-17'::DATE) — March 17 2024 is a Sunday, so this returns March 11 (the Monday), not March 17. Adjust with a day offset if needed.

Timezone blindness with TIMESTAMP vs TIMESTAMPTZ If your application writes timestamps in UTC but your analysis session is running in a different timezone, NOW() returns your local time. This means a filter like WHERE created_at > NOW() - INTERVAL '1 day' may exclude or include an extra hour's worth of data. Use CURRENT_TIMESTAMP AT TIME ZONE 'UTC' explicitly when timezone correctness matters.

Integer division in duration calculations In some contexts you may write EXTRACT(EPOCH FROM duration) / 60 / 60 to convert seconds to hours. If the data types resolve to integer division, you'll get truncated results (e.g., 90 minutes becomes 1 hour instead of 1.5). Cast to float explicitly: EXTRACT(EPOCH FROM duration) / 3600.0.

---

Summary & Next Steps

Let's take stock of what you can now do. You understand how SQL stores dates and times as distinct types, and why the difference between DATE, TIMESTAMP, and TIMESTAMPTZ matters. You can cast string values into proper date types. You can truncate timestamps to any time grain for grouping and cohort analysis. You can compute durations between two points in time and shift dates forward or backward using interval arithmetic. And you know the key syntax differences across PostgreSQL, MySQL, BigQuery, and SQL Server well enough to adapt when the platform changes.

The skill gap between analysts who know SQL and analysts who are dangerous with SQL often comes down to exactly this territory — temporal calculations, cohort construction, and time-series aggregation. These are the operations that turn raw event logs into business intelligence.

From here, good next topics to explore include:

• Window functions with time ordering — using LAG() and LEAD() to compute time between consecutive events per user
• Cohort retention analysis — combining date truncation, duration calculation, and window functions to build a full retention table
• Timezone handling in depth — AT TIME ZONE, timezone-aware aggregation, and working with daylight saving time edge cases
• Calendar tables — a pattern for robust date-spine generation that sidesteps many platform-specific date function quirks

Each of those topics builds directly on what you learned here. The foundation is solid. Now go compute some durations.