PostgreSQL - Date/Time Functions & Operators

PostgreSQL - Modern Date/Time Functions and Operators

Handling dates and times is a critical task in nearly every application. PostgreSQL offers a powerful and comprehensive set of data types, functions, and operators for this purpose. This guide covers the modern best practices for working with temporal data.

Best Practice: Use Timezone-Aware Types

PostgreSQL has two primary timestamp types: timestamp (or timestamp without time zone) and timestamptz (or timestamp with time zone).

• timestamp: Stores a date and time, but has no timezone information. Its value is ambiguous without external context.
• timestamptz: This is the recommended type for most applications. It stores a UTC timestamp internally. When you insert a value, it’s converted to UTC based on the session’s timezone. When you select it, it’s converted back to the session’s timezone. This makes it robust for global applications.

Getting the Current Time

PostgreSQL provides several functions to get the current time, each with a specific scope:

Function	Description	Return Type
NOW()	Returns the start time of the current transaction. The value is stable within the transaction.	timestamptz
CURRENT_TIMESTAMP	Standard SQL equivalent of NOW().	timestamptz
CURRENT_DATE	Returns only the current date.	date
CURRENT_TIME	Returns only the current time with timezone.	timetz
clock_timestamp()	Returns the actual current time (wall clock time). Its value changes even within a single SQL command.	timestamptz

Example: The difference between NOW() and clock_timestamp().

SELECT NOW(), pg_sleep(2), NOW();
-- Both NOW() calls will return the exact same timestamp.

SELECT clock_timestamp(), pg_sleep(2), clock_timestamp();
-- The second clock_timestamp() will be ~2 seconds after the first.

Date/Time Arithmetic with INTERVAL

You can perform arithmetic on date/time values using the INTERVAL type. This provides a clear and readable way to add or subtract periods of time.

-- Get the timestamp for 7 days from now
SELECT NOW() + INTERVAL '7 days';

-- Get the date for 3 months ago
SELECT CURRENT_DATE - INTERVAL '3 months';

-- A more complex interval
SELECT NOW() + INTERVAL '1 year 2 months 3 days 4 hours 5 minutes';

You can also subtract two timestamp values to get an INTERVAL representing the difference.

SELECT timestamp '2023-10-27 10:00:00' - timestamp '2023-10-26 08:30:00';

-- Result:
--      interval
-- -------------------
--  1 day 01:30:00

Extracting Information: EXTRACT and DATE_PART

The EXTRACT function lets you pull specific fields (like year, month, day, hour) from a date/time value. DATE_PART is a historical PostgreSQL equivalent.

Syntax: EXTRACT(field FROM source)

SELECT
    EXTRACT(YEAR FROM NOW()) AS current_year,
    EXTRACT(MONTH FROM NOW()) AS current_month,
    EXTRACT(DAY FROM NOW()) AS current_day,
    EXTRACT(DOW FROM NOW()) AS day_of_week; -- 0=Sunday, 6=Saturday

This is extremely useful for aggregation, for instance, grouping sales data by the month of the year.

-- Hypothetical sales table
-- SELECT EXTRACT(MONTH FROM sale_date) as sale_month, SUM(amount)
-- FROM sales
-- GROUP BY sale_month;

Truncating Dates: DATE_TRUNC

DATE_TRUNC is a powerful function for ‘snapping’ a timestamp down to a specific precision. This is invaluable for grouping data into time buckets.

Syntax: DATE_TRUNC('field', source)

SELECT
    '2023-10-27 14:45:10'::timestamptz AS original_ts,
    DATE_TRUNC('hour', '2023-10-27 14:45:10'::timestamptz) AS truncated_to_hour,
    DATE_TRUNC('day', '2023-10-27 14:45:10'::timestamptz) AS truncated_to_day;

Result:

       original_ts       |   truncated_to_hour    |    truncated_to_day
-------------------------+------------------------+------------------------
 2023-10-27 14:45:10+XX | 2023-10-27 14:00:00+XX | 2023-10-27 00:00:00+XX

A common use case is creating a daily time series of user signups.

-- Hypothetical users table
-- SELECT DATE_TRUNC('day', created_at) AS signup_date, COUNT(*)
-- FROM users
-- GROUP BY signup_date
-- ORDER BY signup_date;

Formatting Dates for Display: TO_CHAR

While you should always store dates in native date/time types, you’ll often need to format them as strings for reports or UIs. TO_CHAR is the function for this, using a template pattern.

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS iso_format;
-- Result: '2023-10-27 14:50:30'

SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY') AS readable_format;
-- Result: 'Friday   , 27 October 2023'

Other Useful Functions

• AGE(timestamp, timestamp): Calculates the interval between two timestamps, expressed in a human-readable mix of years, months, and days.
• AGE(timestamp): Calculates the interval between the given timestamp and the current transaction’s start time (NOW()).

SELECT AGE(timestamp '2023-10-27', timestamp '1990-04-10');
-- Result: 33 years 6 mons 17 days