Date Functions

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
NOW()

SELECT
  CURRENT_DATE AS today,
  CURRENT_TIMESTAMP AS right_now,
  NOW() AS also_now;

-- today      | right_now                    | also_now
-- 2025-11-20 | 2025-11-20 14:35:22.123456+00 | 2025-11-20 14:35:22.123456+00

Note CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP are ANSI standard and work everywhere. NOW() is a function that does the same as CURRENT_TIMESTAMP but is not standard SQL. In a transaction, these return the time the transaction started, not the current wall clock time.

-- PostgreSQL
date + INTERVAL 'n unit'
-- MySQL
DATE_ADD(date, INTERVAL n unit)
-- ANSI
date + INTERVAL 'n' unit

-- PostgreSQL
SELECT
  order_date,
  order_date + INTERVAL '30 days' AS due_date,
  order_date - INTERVAL '1 year' AS year_ago
FROM orders;

-- MySQL
SELECT
  order_date,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date
FROM orders;

-- order_date  | due_date    | year_ago
-- 2025-10-15  | 2025-11-14  | 2024-10-15

Note Interval units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Adding months is tricky — Jan 31 + 1 month may yield Feb 28 or Mar 3 depending on the database. PostgreSQL truncates to end of month; MySQL may overflow.

-- PostgreSQL
date1 - date2  -- returns integer (days)
-- MySQL
DATEDIFF(date1, date2)  -- returns integer (days)
-- Standard: use EXTRACT on interval

-- PostgreSQL
SELECT
  order_date,
  shipped_date,
  shipped_date - order_date AS days_to_ship
FROM orders
WHERE shipped_date IS NOT NULL;

-- MySQL
SELECT
  order_date,
  shipped_date,
  DATEDIFF(shipped_date, order_date) AS days_to_ship
FROM orders;

-- order_date  | shipped_date | days_to_ship
-- 2025-10-01  | 2025-10-04   | 3

Note PostgreSQL subtracts dates directly. MySQL uses DATEDIFF(end, start) — note the argument order is end first. SQL Server's DATEDIFF takes a unit argument: DATEDIFF(DAY, start, end). Always check argument order in your database.

EXTRACT(part FROM date)
-- Parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY, EPOCH

SELECT
  order_date,
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  EXTRACT(DOW FROM order_date) AS day_of_week
FROM orders;

-- order_date  | order_year | order_month | day_of_week
-- 2025-10-15  | 2025       | 10          | 3

Note EXTRACT is ANSI standard. In PostgreSQL, DOW is 0 (Sunday) to 6 (Saturday). MySQL's DAYOFWEEK returns 1 (Sunday) to 7 (Saturday). EXTRACT(EPOCH FROM timestamp) gives Unix timestamp in PostgreSQL.

-- PostgreSQL
TO_CHAR(date, 'format')
-- MySQL
DATE_FORMAT(date, 'format')

-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_date,
       TO_CHAR(order_date, 'Mon DD, YYYY') AS pretty_date
FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_date,
       DATE_FORMAT(order_date, '%b %d, %Y') AS pretty_date
FROM orders;

-- iso_date    | pretty_date
-- 2025-10-15  | Oct 15, 2025

Note Format codes differ between databases. PostgreSQL uses YYYY, MM, DD, HH24, MI, SS. MySQL uses %Y, %m, %d, %H, %i, %s. Format dates in the application layer when possible to avoid DB-specific code.

-- PostgreSQL
DATE_TRUNC('unit', timestamp)
-- MySQL
DATE(timestamp)  -- truncate to date
DATE_FORMAT(timestamp, '%Y-%m-01')  -- truncate to month

-- PostgreSQL: group orders by month
SELECT
  DATE_TRUNC('month', order_date) AS order_month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

-- order_month           | order_count | revenue
-- 2025-10-01 00:00:00   | 342         | 52340.00
-- 2025-11-01 00:00:00   | 298         | 44890.50

Note DATE_TRUNC is extremely useful for time-series grouping. It rounds down to the start of the given period (year, quarter, month, week, day, hour). MySQL lacks DATE_TRUNC — use DATE_FORMAT or manual rounding.

INTERVAL 'quantity unit'
-- Can combine: INTERVAL '2 hours 30 minutes'

SELECT
  created_at,
  created_at + INTERVAL '7 days' AS expires_at,
  NOW() - created_at AS age
FROM sessions
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Sessions created in the last 24 hours, with expiry and age

Note PostgreSQL supports rich interval syntax: '1 year 2 months 3 days'. MySQL intervals are single-unit: INTERVAL 1 YEAR, INTERVAL 30 DAY. Subtracting two timestamps gives an interval in PostgreSQL but not in MySQL.

AGE(timestamp1, timestamp2)
AGE(timestamp)  -- shorthand for AGE(NOW(), timestamp)

SELECT
  first_name,
  birth_date,
  AGE(birth_date) AS age,
  EXTRACT(YEAR FROM AGE(birth_date)) AS years_old
FROM users;

-- first_name | birth_date | age                    | years_old
-- Alice      | 1990-05-15 | 35 years 6 mons 5 days | 35

Note AGE is PostgreSQL-specific. It returns a human-readable interval. MySQL has no direct equivalent — compute age manually with TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) and adjust for whether the birthday has passed this year.

-- PostgreSQL
timestamp AT TIME ZONE 'zone'
-- MySQL
CONVERT_TZ(datetime, from_tz, to_tz)

-- PostgreSQL
SELECT
  created_at AT TIME ZONE 'UTC' AS utc_time,
  created_at AT TIME ZONE 'America/New_York' AS eastern_time
FROM events;

-- MySQL
SELECT
  CONVERT_TZ(created_at, '+00:00', '-05:00') AS eastern_time
FROM events;

-- utc_time                  | eastern_time
-- 2025-11-20 14:30:00+00    | 2025-11-20 09:30:00-05

Note Always store timestamps in UTC (use TIMESTAMPTZ in PostgreSQL). Convert to local time only for display. Daylight saving time offsets change — use named zones ('America/New_York') instead of fixed offsets ('-05:00') when possible.