Date and Time Functions in SQL: PostgreSQL, MySQL, and SQL Server Compared

Why Date Functions Vary So Much by Database

Date and time handling is one of the least portable areas of SQL — every major database has its own function names and, sometimes, subtly different behavior for the same operation. This guide covers the core operations (extracting parts, adding/subtracting intervals, truncating to a period, formatting for display) with the exact syntax for PostgreSQL, MySQL, and SQL Server side by side.

Getting the Current Date/Time

-- PostgreSQL
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();

-- MySQL
SELECT CURDATE(), NOW(), CURRENT_TIMESTAMP();

-- SQL Server
SELECT GETDATE(), SYSDATETIME();

Extracting Parts of a Date

-- PostgreSQL / standard SQL
SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), EXTRACT(DOW FROM order_date)
FROM orders;
-- DOW = day of week, 0 (Sunday) through 6 (Saturday)

-- MySQL
SELECT YEAR(order_date), MONTH(order_date), DAYOFWEEK(order_date) FROM orders;
-- DAYOFWEEK returns 1 (Sunday) through 7 (Saturday) — a different numbering than PostgreSQL's DOW

-- SQL Server
SELECT YEAR(order_date), MONTH(order_date), DATEPART(WEEKDAY, order_date) FROM orders;

Watch the day-of-week numbering carefully — PostgreSQL's EXTRACT(DOW ...) starts at 0 for Sunday; MySQL's DAYOFWEEK() starts at 1 for Sunday; SQL Server's default DATEPART(WEEKDAY, ...) also starts at 1 but is affected by the session's DATEFIRST setting. Always verify against a known date before trusting weekday logic in production.

Truncating to a Period (Rounding Down)

One of the most common operations in reporting — collapsing a timestamp down to the start of its day, week, month, or year for grouping.

-- PostgreSQL: DATE_TRUNC
SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(total)
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- MySQL: no DATE_TRUNC — use DATE_FORMAT to truncate to month
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS order_month, SUM(total)
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m-01');

-- SQL Server: no DATE_TRUNC prior to 2022 — construct it manually
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS order_month, SUM(total)
FROM orders
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);
-- SQL Server 2022+ added a native DATE_TRUNC('month', order_date) function

This exact pattern — truncate to month, group, sum — is the foundation of virtually every monthly reporting query and the cohort analysis pattern covered in our product analytics guide.

Adding and Subtracting Time Intervals

-- PostgreSQL: INTERVAL literals
SELECT order_date + INTERVAL '7 days' FROM orders;
SELECT order_date - INTERVAL '1 month' FROM orders;

-- MySQL: DATE_ADD / DATE_SUB
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH) FROM orders;

-- SQL Server: DATEADD (positive or negative number for add/subtract)
SELECT DATEADD(DAY, 7, order_date) FROM orders;
SELECT DATEADD(MONTH, -1, order_date) FROM orders;

Calculating the Difference Between Two Dates

-- PostgreSQL: simple subtraction for days, AGE() for a full breakdown
SELECT delivered_at - ordered_at AS delivery_duration FROM orders;
SELECT AGE(delivered_at, ordered_at) FROM orders;   -- returns years/months/days breakdown

-- MySQL: DATEDIFF (days only) or TIMESTAMPDIFF for other units
SELECT DATEDIFF(delivered_at, ordered_at) AS days_to_deliver FROM orders;
SELECT TIMESTAMPDIFF(HOUR, ordered_at, delivered_at) AS hours_to_deliver FROM orders;

-- SQL Server: DATEDIFF requires an explicit unit as the first argument
SELECT DATEDIFF(DAY, ordered_at, delivered_at) AS days_to_deliver FROM orders;

A subtle but important gotcha: MySQL's and SQL Server's DATEDIFF count calendar-unit boundaries crossed, not full 24-hour periods — DATEDIFF(DAY, '2026-01-01 23:00', '2026-01-02 01:00') returns 1 (one calendar day boundary crossed), even though only 2 actual hours elapsed. Use a finer unit (HOUR, MINUTE) or subtract raw timestamps if you need actual elapsed duration rather than calendar-boundary count.

Formatting Dates for Display

-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(order_date, 'Mon DD, YYYY') FROM orders;   -- 'Jan 15, 2026'

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(order_date, '%b %d, %Y') FROM orders;

-- SQL Server: FORMAT (flexible but slower on large result sets) or CONVERT with a style code
SELECT FORMAT(order_date, 'MMM dd, yyyy') FROM orders;

Formatting is one of the least portable operations across databases — format codes differ entirely between engines (YYYY vs %Y vs yyyy). Where possible, do display formatting in the application layer instead of SQL, and reserve SQL date functions for filtering, grouping, and calculation.

Time Zones: The Most Common Source of Date Bugs

-- PostgreSQL: convert a timestamptz to a specific timezone for display/grouping
SELECT order_date AT TIME ZONE 'America/New_York' FROM orders;

-- Always store timestamps in UTC (TIMESTAMPTZ in Postgres, DATETIMEOFFSET in SQL Server)
-- and convert to local time only at the point of display or user-facing grouping

The single most common date-related production bug: grouping "daily" metrics using a naive timestamp column without accounting for timezone, so a user's 11pm local-time action gets silently attributed to the wrong calendar day in UTC. Always be explicit about which timezone a "day" boundary should use, especially for retention and cohort queries — see our product analytics guide for where this specifically matters.

Common Mistakes

• Wrapping a date column in a function inside WHERE (e.g. WHERE DATE(created_at) = '2026-01-15') — this disables any index on that column; use a range comparison instead (see our query optimization guide).
• Assuming DATEDIFF counts full elapsed time rather than calendar-boundary crossings — the 23:00→01:00 example above.
• Mixing naive and timezone-aware timestamps in the same comparison or JOIN — produces silently wrong results rather than an error.
• Hardcoding date format strings without checking the target database's specific format-code syntax — a format string copy-pasted from another database's documentation will often fail silently or produce garbled output.

Common Interview Questions

1. Write a query to group revenue by month. The truncate-then-group pattern shown above, adapted to your specific database.
2. How would you calculate the number of days between two dates? Simple subtraction (PostgreSQL) or DATEDIFF (MySQL/SQL Server) — mention the calendar-boundary caveat.
3. Why might a WHERE clause filtering on a date column ignore an index? Wrapping the column in a function like DATE() — explain the sargability concept.
4. How would you handle timezone consistency in a global application's date-based reporting? Store in UTC, convert only at display/grouping time, and always be explicit about which timezone defines a "day" boundary for business metrics.

Frequently Asked Questions

Should I always store dates in UTC?

Yes, as the default for any timestamp representing a real-world event — it avoids ambiguity and makes cross-timezone comparisons correct by default. Convert to local time only when displaying to a specific user or defining a business-specific "day" boundary.

Is DATE_TRUNC available on every database?

No — it's native to PostgreSQL and was added to SQL Server starting in 2022; MySQL has no direct equivalent and requires DATE_FORMAT or manual construction, as shown above.

Practice Date and Time Queries

Date logic is deceptively tricky and worth deliberate practice across timezone and boundary edge cases. Try it hands-on with our SQL practice questions, or apply it to time-series data in our case studies. For running totals and moving averages over date-based data, see our dedicated guide.