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:
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.
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:
2024-03-15.14:30:00. Rarely used in analytics.2024-03-15 14:30:00.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.
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');
Tip: Always prefer explicit casting over implicit conversion. Relying on the database to silently coerce
'2024-03-15'into a date works until it doesn't — and when it fails in a production pipeline, debugging is painful.
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;
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'.
Warning: In PostgreSQL,
DATE_TRUNC('week', ...)truncates to Monday, not Sunday. If your business week starts on Sunday, you'll need to adjust:DATE_TRUNC('week', date + INTERVAL '1 day') - INTERVAL '1 day'. Always verify your week boundaries.
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.
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?
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;
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;
Warning: MySQL's
DATEDIFF()takes arguments in(end_date, start_date)order. SQL Server takes them in(unit, start_date, end_date)order. Getting these backwards gives you negative numbers that are easy to miss. Always sanity-check a few rows manually.
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.
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);
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.
Tip: When adding months or years (as opposed to days), be careful with month-end dates. Adding one month to January 31st should give February 28th (or 29th in a leap year). All major databases handle this correctly, but it's worth knowing it's not always adding exactly 30 days.
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)
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:
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.
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.
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:
LAG() and LEAD() to compute time between consecutive events per userAT TIME ZONE, timezone-aware aggregation, and working with daylight saving time edge casesEach of those topics builds directly on what you learned here. The foundation is solid. Now go compute some durations.
Learning Path: Advanced SQL Queries