📅

PostgreSQL Date & Time Functions

Complete reference for PostgreSQL date and time functions covering date arithmetic, timestamp extraction, formatting, interval arithmetic, and timezone handling. Every function includes syntax, real-world SQL examples, and common timezone pitfalls. Updated for PostgreSQL 16.

25 functions

What are PostgreSQL Date & Time Functions?

PostgreSQL date and time functions are built-in functions for working with date, time, timestamp, interval, and timestamptz values. They handle date arithmetic, extracting parts like year and month, formatting timestamps as strings, computing date differences in days, and converting between timezones. Key PostgreSQL date functions include date_trunc(), extract(), to_timestamp(), age(), now(), and make_interval().

now

PG 8.0+→ timestamp with time zone

Returns the current date and time at the start of the current transaction.

DeveloperData EngDBASecurity

Signature

now ( ) → timestamp with time zone

Examples

sql

SELECT now();

→2025-03-07 14:23:01.123456+00

sql

SELECT now()::date;

→2025-03-07

sql

SELECT now() - interval '7 days';

→7 days ago

sql

INSERT INTO audit_log (event, recorded_at) VALUES ('login', now());

→Row inserted with transaction start time

⚠Anti-Pattern— Confusing now() with clock_timestamp() for progress logging

Using `now()` inside a loop or long migration to record step timing is misleading — it always returns the same transaction start time, so all recorded times will be identical.

✓ Instead: Use `clock_timestamp()` when you need the actual wall-clock time at each point within a transaction.

`now()` returns the transaction start time and does NOT advance during a long transaction. Use `clock_timestamp()` if you need the actual wall-clock time at each function call — important for timing loops or progress logging.

example

SELECT clock_timestamp() - now() AS time_in_transaction;

→Elapsed time since transaction began

current_timestamp

PG 8.0+→ timestamp with time zone

Returns the current date and time (same as now()). SQL standard syntax — no parentheses.

DeveloperData EngDBASecurity

Signature

CURRENT_TIMESTAMP → timestamp with time zone

Examples

sql

SELECT CURRENT_TIMESTAMP;

→2025-03-07 14:23:01.123456+00

Optional precision argument truncates sub-seconds

sql

SELECT CURRENT_TIMESTAMP(0);

→2025-03-07 14:23:01+00

sql

CREATE TABLE events (id serial, created_at timestamptz DEFAULT CURRENT_TIMESTAMP);

→Table created with SQL-standard default

Both return the same transaction start time

sql

SELECT CURRENT_TIMESTAMP = now();

→true

⚠Anti-Pattern— Assuming CURRENT_TIMESTAMP and clock_timestamp() behave the same

`CURRENT_TIMESTAMP` is frozen at transaction start, just like `now()`. Developers sometimes expect it to reflect the real wall-clock time mid-transaction and get stale audit timestamps.

✓ Instead: Use `clock_timestamp()` if you need the actual time of each individual DML statement within a multi-statement transaction.

In DDL, `DEFAULT CURRENT_TIMESTAMP` is SQL-standard and portable. In queries, `now()` is shorter. Both freeze at transaction start — use `clock_timestamp()` for actual wall-clock recording.

example

CREATE TABLE events (id serial, created_at timestamptz DEFAULT CURRENT_TIMESTAMP);

→created_at auto-populated at transaction start

current_date

PG 8.0+→ date

Returns the current date in the server's local time zone (no time component). Equivalent to now()::date.

DeveloperData Eng

Signature

CURRENT_DATE → date

Examples

sql

SELECT CURRENT_DATE;

→2025-03-07

sql

SELECT CURRENT_DATE - 30;

→2025-02-05

sql

SELECT age(CURRENT_DATE, birth_date) FROM users;

→User's current age

sql

SELECT * FROM orders WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1;

→Today's orders — index scan eligible

⚠Anti-Pattern— Using to_char or DATE() in WHERE clauses for date filtering

Writing `WHERE DATE(created_at) = CURRENT_DATE` or `WHERE to_char(created_at, 'YYYY-MM-DD') = '2025-03-07'` wraps the indexed column in a function, preventing index use and causing full table scans.

✓ Instead: Use a range predicate: `WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1`.

Use `WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1` rather than `DATE(created_at) = CURRENT_DATE` — the former is index-friendly because it doesn't wrap the column in a function.

example

SELECT * FROM orders WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1;

→Today's orders — index scan eligible

clock_timestamp

PG 8.2+→ timestamp with time zone

Returns the current date and time — this advances during statement execution, unlike now().

DeveloperDBAData Eng

Signature

clock_timestamp ( ) → timestamp with time zone

Examples

sql

SELECT clock_timestamp();

→2025-03-07 14:23:01.456789+00

sql

SELECT clock_timestamp() - now() AS elapsed;

→Elapsed time in current transaction

sql

DO $ DECLARE t timestamptz := clock_timestamp(); BEGIN PERFORM pg_sleep(1); RAISE NOTICE 'elapsed: %', clock_timestamp() - t; END $;

→Notice: elapsed: 00:00:01.001

sql

UPDATE batch_jobs SET last_heartbeat = clock_timestamp() WHERE id = 42;

→Heartbeat updated to actual wall-clock time

⚠Anti-Pattern— Using now() to measure elapsed time within a transaction

`now()` is frozen at transaction start, so `clock_timestamp() - now()` grows over time but `now() - now()` is always zero. Relying on `now()` for within-transaction timing yields no useful duration data.

✓ Instead: Capture `clock_timestamp()` at the start of an operation and subtract it from a later `clock_timestamp()` call.

Record `clock_timestamp()` before and after an expensive operation inside a DO block to measure its real elapsed time. Unlike `now()`, it reflects actual wall-clock time even within a transaction.

example

DO $ DECLARE t timestamptz := clock_timestamp(); BEGIN PERFORM count(*) FROM huge_table; RAISE NOTICE 'elapsed: %', clock_timestamp() - t; END $;

→Notice: elapsed: 00:00:03.142

date_trunc

PG 8.0+; three-argument form (with time_zone) requires PG 12+→ same as input

Truncates a timestamp or interval to the specified precision (e.g., 'hour', 'day', 'month').

DeveloperData EngDBA

Signatures

date_trunc ( field text, source timestamp ) → timestamp

date_trunc ( field text, source timestamp with time zone ) → timestamp with time zone

date_trunc ( field text, source timestamp with time zone, time_zone text ) → timestamp with time zone

date_trunc ( field text, source interval ) → interval

Parameters

Parameter	Type	Description
field	text	Precision: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium
source	timestamp/interval	Timestamp or interval to truncate
time_zone	text	Optional time zone for day/week/month boundaries

Examples

sql

SELECT date_trunc('hour', now());

→2025-03-07 14:00:00+00

sql

SELECT date_trunc('month', '2025-03-15'::timestamp);

→2025-03-01 00:00:00

Week starts Monday per ISO

sql

SELECT date_trunc('week', '2025-03-07'::timestamp);

→2025-03-03 00:00:00

Midnight in Chicago, stored as UTC

sql

SELECT date_trunc('day', now(), 'America/Chicago') AS local_midnight;

→2025-03-07 06:00:00+00

⚠Anti-Pattern— Truncating timestamptz without specifying timezone for local-day boundaries

Calling `date_trunc('day', created_at)` on a `timestamptz` column truncates to midnight UTC, not midnight in the user's local timezone. This silently misaligns daily aggregates for non-UTC deployments.

✓ Instead: Use the three-argument form: `date_trunc('day', created_at, 'America/New_York')` to get local-day boundaries correctly.

For sub-hour buckets not supported by date_trunc, combine it with arithmetic: `date_trunc('hour', ts) + floor(extract(minute from ts) / 15) * interval '15 minutes'` produces 15-minute buckets.

example

SELECT date_trunc('day', created_at) AS day, count(*) FROM orders GROUP BY 1 ORDER BY 1;

→Daily order counts

date_part

PG 8.0+→ double precision

Extracts a subfield from a date/time value as a numeric value. Equivalent to the SQL EXTRACT function.

DeveloperData Eng

Signatures

date_part ( field text, source timestamp ) → double precision

date_part ( field text, source interval ) → double precision

Parameters

Parameter	Type	Description
field	text	The subfield to extract: century, day, decade, dow, doy, epoch, hour, isodow, isoyear, julian, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year
source	timestamp or interval	Date/time value to extract from

Examples

sql

SELECT date_part('year', now());

→2025

5 = Friday (0=Sunday)

sql

SELECT date_part('dow', '2025-03-07'::date);

→5

sql

SELECT date_part('epoch', interval '5 days 3 hours');

→442800

ISO day of week: 1=Monday, 7=Sunday

sql

SELECT date_part('isodow', '2025-03-09'::date);

→7

⚠Anti-Pattern— Preferring date_part over the SQL-standard EXTRACT

`date_part('year', ts)` is PostgreSQL-specific and returns `double precision`, which can cause unexpected coercion in comparisons. `EXTRACT` is SQL-standard, returns `numeric`, and is preferred for portable code.

✓ Instead: Use `EXTRACT(year FROM ts)` for standard SQL syntax that is portable across database systems.

`EXTRACT(year FROM now())` is the SQL-standard equivalent of `date_part('year', now())`. Both return the same value — prefer `EXTRACT` in new code for portability.

example

SELECT EXTRACT(hour FROM created_at) AS hour, count(*) FROM events GROUP BY 1 ORDER BY 1;

→Event count by hour of day

extract

PG 8.0+; isodow and isoyear fields available since PG 8.0+→ numeric

SQL-standard syntax to extract a subfield (year, month, day, hour, etc.) from a date/time or interval.

DeveloperData Eng

Signature

EXTRACT ( field FROM source ) → numeric

Parameters

Parameter	Type	Description
field	identifier	year, month, day, hour, minute, second, epoch, dow, doy, week, quarter, timezone, etc.
source	timestamp/date/interval	The value to extract from

Examples

sql

SELECT EXTRACT(year FROM now());

→2025

sql

SELECT EXTRACT(epoch FROM now());

→1741360981 (Unix timestamp)

sql

SELECT EXTRACT(quarter FROM '2025-08-15'::date);

→3

ISO Sunday = 7, not 0

sql

SELECT EXTRACT(isodow FROM '2025-03-09'::date);

→7

⚠Anti-Pattern— Computing age in years by subtracting EXTRACT(year) values

`EXTRACT(year FROM now()) - EXTRACT(year FROM birth_date)` gives the wrong answer for anyone whose birthday has not yet occurred in the current calendar year.

✓ Instead: Use `EXTRACT(year FROM age(birth_date))` which properly accounts for month and day.

Use `EXTRACT(epoch FROM ts)` to get a Unix timestamp (seconds since 1970-01-01). Cast to `bigint` for integer output. Multiply by 1000 for JavaScript Date constructor input.

example

SELECT EXTRACT(epoch FROM created_at)::bigint AS unix_ts FROM events;

→Unix timestamps for API consumers

age

PG 8.0+→ interval

Returns the interval between two timestamps, or between a timestamp and now(), with years and months included symbolically.

DeveloperData Eng

Signatures

age ( timestamp, timestamp ) → interval

age ( timestamp ) → interval

Parameters

Parameter	Type	Description
timestamp1	timestamp	Minuend (later date)
timestamp2	timestamp	Subtrahend (earlier date); defaults to now() if omitted

Examples

sql

SELECT age('2025-01-01', '2000-06-15');

→24 years 6 mons 16 days

sql

SELECT age(birth_date) FROM users;

→Age from birth_date to today

sql

SELECT EXTRACT(year FROM age(birth_date)) AS years_old FROM users;

→Integer age in years

sql

SELECT name, age(created_at) AS account_age FROM users ORDER BY created_at;

→Account ages as human-readable intervals

⚠Anti-Pattern— Subtracting timestamps directly instead of using age()

Direct subtraction (`ts1 - ts2`) returns an interval measured in raw days and seconds with no month or year component. This means '1 year' shows as '365 days' and loses meaningful calendar structure.

✓ Instead: Use `age(ts1, ts2)` when you need a human-readable breakdown of years, months, and days.

Use `EXTRACT(year FROM age(birth_date))` rather than `EXTRACT(year FROM now()) - EXTRACT(year FROM birth_date)`. The second approach can be off by 1 for birthdays not yet reached this year.

example

SELECT name, EXTRACT(year FROM age(birth_date))::int AS age FROM users ORDER BY age;

→Correct age in whole years

date_bin

PG 14+→ timestamp

Bins a timestamp into the nearest stride-sized interval boundary aligned to the given origin.

DeveloperData Eng

Signatures

date_bin ( stride interval, source timestamp, origin timestamp ) → timestamp

date_bin ( stride interval, source timestamptz, origin timestamptz ) → timestamptz

Parameters

Parameter	Type	Description
stride	interval	Bin width (e.g., '15 minutes', '1 hour')
source	timestamp	Timestamp to bin
origin	timestamp	Reference point for alignment (e.g., epoch)

Examples

sql

SELECT date_bin('15 minutes', '2025-03-07 14:23:00'::timestamp, '2025-01-01');

→2025-03-07 14:15:00

sql

SELECT date_bin('5 minutes', now(), '2020-01-01');

→Nearest 5-minute boundary

sql

SELECT date_bin('1 hour', event_ts, '2000-01-01') AS bucket, count(*) FROM events GROUP BY 1 ORDER BY 1;

→Hourly event counts aligned to a fixed origin

sql

SELECT date_bin('30 seconds', recorded_at, '2025-01-01') AS slot, avg(cpu_pct) FROM metrics GROUP BY 1;

→30-second average CPU by time slot

⚠Anti-Pattern— Using date_trunc for non-standard time buckets

`date_trunc` only understands named units like 'hour' or 'day'. Developers sometimes combine it with manual arithmetic (e.g., floor division on minutes) to approximate 15-minute buckets, which is fragile and hard to read.

✓ Instead: Use `date_bin('15 minutes', ts, origin)` (PG 14+) for clean, correct arbitrary-width bucketing.

`date_trunc` only supports named units (hour, day, etc.). Use `date_bin` when you need 5-minute, 15-minute, or other arbitrary-width time buckets aligned to a specific origin.

example

SELECT date_bin('5 min', event_ts, '2020-01-01') AS bucket, count(*) FROM events GROUP BY 1 ORDER BY 1;

→5-minute event buckets

make_date

PG 9.4+→ date

Creates a date from year, month, and day integer components.

DeveloperData Eng

Signature

make_date ( year int, month int, day int ) → date

Parameters

Parameter	Type	Description
year	int	Year (negative = BC)
month	int	Month (1–12)
day	int	Day of month (1–31)

Examples

sql

SELECT make_date(2025, 3, 7);

→2025-03-07

2024 is a leap year

sql

SELECT make_date(2024, 2, 29);

→2024-02-29

sql

SELECT make_date(year, month, 1) AS month_start FROM date_components;

→First day of each month

sql

SELECT make_date(fiscal_year, fiscal_month, fiscal_day) AS full_date FROM warehouse_dim WHERE make_date(fiscal_year, fiscal_month, fiscal_day) >= '2024-01-01';

→Full dates reconstructed from data warehouse dimension columns

⚠Anti-Pattern— Concatenating strings to build date literals

Building dates via string concatenation like `(year || '-' || month || '-' || day)::date` is fragile — single-digit months and days produce invalid ISO strings (e.g., '2025-3-7') that may fail casting.

✓ Instead: Use `make_date(year, month, day)` which handles component integers directly and raises a clear error on invalid combinations.

If your data stores year, month, day as separate integer columns (common in data warehouses), use `make_date(year, month, day)` to reconstruct a proper date for filtering and arithmetic.

example

SELECT make_date(year_col, month_col, day_col) AS full_date FROM fact_table WHERE make_date(year_col, month_col, day_col) >= '2024-01-01';

→Full dates from component columns

make_timestamp

PG 9.4+→ timestamp

Creates a timestamp (without time zone) from date and time components.

DeveloperData Eng

Signature

make_timestamp ( year int, month int, mday int, hour int, min int, sec double precision ) → timestamp

Parameters

Parameter	Type	Description
year	int	Year
month	int	Month (1–12)
mday	int	Day of month
hour	int	Hour (0–23)
min	int	Minute (0–59)
sec	double precision	Seconds including fractional part (0–60.999)

Examples

sql

SELECT make_timestamp(2025, 3, 7, 14, 30, 0);

→2025-03-07 14:30:00

sql

SELECT make_timestamp(2025, 1, 1, 0, 0, 0.5);

→2025-01-01 00:00:00.5

sql

SELECT make_timestamp(yr, mo, dy, hr, mi, sc) AS ts FROM log_components ORDER BY ts;

→Sortable timestamps assembled from split log fields

sql

SELECT make_timestamp(2025, 12, 31, 23, 59, 59.999) AS year_end;

→2025-12-31 23:59:59.999

⚠Anti-Pattern— Using make_timestamp instead of make_timestamptz for user-facing data

`make_timestamp` produces a timezone-naive `timestamp` type. If the components represent local time in a specific timezone, the result is ambiguous and will be interpreted differently depending on the session timezone.

✓ Instead: Use `make_timestamptz(year, month, day, hour, min, sec, timezone)` to produce an unambiguous `timestamptz` value.

Use `make_timestamp` when you have hour, minute, second as separate numeric columns and need a real timestamp for `date_trunc`, interval arithmetic, or indexing.

example

SELECT make_timestamp(yr, mo, dy, hr, mi, sc) AS ts FROM log_components;

→Proper timestamps from component columns

make_timestamptz

PG 9.4+→ timestamp with time zone

Creates a timestamp with time zone from components, using the session time zone unless explicitly specified.

DeveloperData EngDBA

Signature

make_timestamptz ( year int, month int, mday int, hour int, min int, sec double precision [, timezone text] ) → timestamp with time zone

Parameters

Parameter	Type	Description
year	int	Year
month	int	Month (1–12)
mday	int	Day of month
hour	int	Hour (0–23)
min	int	Minute (0–59)
sec	double precision	Seconds
timezone	text	Optional IANA time zone name (e.g., 'America/New_York')

Examples

sql

SELECT make_timestamptz(2025, 3, 7, 10, 0, 0, 'America/New_York');

→2025-03-07 15:00:00+00

sql

SELECT make_timestamptz(2025, 1, 1, 0, 0, 0);

→Uses session timezone

sql

SELECT make_timestamptz(year, month, day, hour, minute, 0, user_tz) AS utc_ts FROM user_schedules;

→Correct UTC timestamps from per-user timezone inputs

DST transition handled correctly; 2:30 AM ET does not exist, PostgreSQL resolves it

sql

SELECT make_timestamptz(2025, 3, 8, 2, 30, 0, 'America/New_York');

→2025-03-08 07:30:00+00

⚠Anti-Pattern— Omitting timezone argument and relying on session timezone

Calling `make_timestamptz(y, m, d, h, mi, s)` without the timezone argument silently uses the session's `TimeZone` setting. In multi-region setups or connection pools, this setting varies and produces incorrect UTC conversions.

✓ Instead: Always pass an explicit IANA timezone string as the seventh argument when the input represents local time.

Pass an explicit timezone to `make_timestamptz` when receiving local time data from users in various zones. This correctly handles DST transitions that would trip up manual arithmetic.

example

SELECT make_timestamptz(year, month, day, hour, minute, 0, user_tz) FROM user_schedules;

→Correct UTC timestamps from local time input

make_interval

PG 9.4+→ interval

Creates an interval from component parts, all of which default to zero.

DeveloperData Eng

Signature

make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval

Parameters

Parameter	Type	Description
years	int	Number of years
months	int	Number of months
weeks	int	Number of weeks
days	int	Number of days
hours	int	Number of hours
mins	int	Number of minutes
secs	double precision	Number of seconds

Examples

sql

SELECT make_interval(years => 1, months => 6);

→1 year 6 mons

sql

SELECT make_interval(hours => 3, mins => 30);

→03:30:00

sql

SELECT now() + make_interval(days => 90);

→90 days from now

sql

SELECT created_at + make_interval(days => grace_period) AS expiry FROM subscriptions;

→Expiry dates computed from integer grace period column

⚠Anti-Pattern— Casting integer-concatenated strings to interval

Writing `(n || ' days')::interval` to build an interval from an integer variable is common but fragile — SQL injection risks aside, it breaks if the unit name is parameterized and fails readability review.

✓ Instead: Use `make_interval(days => n)` for type-safe, readable interval construction from integer column values.

Use `make_interval(days => expiry_days)` to build intervals from integer columns without string concatenation hacks like `(days || ' days')::interval`.

example

SELECT created_at + make_interval(days => grace_period) AS expiry FROM subscriptions;

→Expiry date from integer grace period column

justify_days

PG 8.0+→ interval

Adjusts an interval so that 30-day periods are represented as months.

DeveloperData Eng

Signature

justify_days ( interval ) → interval

Parameters

Parameter	Type	Description
interval	interval	Interval to normalize

Examples

sql

SELECT justify_days(interval '35 days');

→1 mon 5 days

sql

SELECT justify_days(interval '90 days');

→3 mons

sql

SELECT justify_days(end_date - start_date) AS normalized FROM projects;

→Project durations expressed in months and days

sql

SELECT justify_days(interval '65 days');

→2 mons 5 days

⚠Anti-Pattern— Displaying raw subtraction intervals to end users

Showing a raw `end_ts - start_ts` result like '65 days 03:00:00' to users is less readable than '2 mons 5 days 03:00:00'. Skipping normalization produces technically correct but user-unfriendly output.

✓ Instead: Apply `justify_days()` (or `justify_interval()`) before surfacing duration data to end users or reports.

When subtracting dates you get a raw day count. Use `justify_days` (then `justify_hours`) to convert to a human-friendly interval like '2 mons 15 days' for display.

example

SELECT justify_days(justify_hours(end_ts - start_ts)) AS duration FROM tasks;

→"2 mons 5 days 3 hours"

justify_hours

PG 8.0+→ interval

Adjusts an interval so that 24-hour periods are represented as days.

DeveloperData Eng

Signature

justify_hours ( interval ) → interval

Parameters

Parameter	Type	Description
interval	interval	Interval to normalize

Examples

sql

SELECT justify_hours(interval '27 hours');

→1 day 03:00:00

sql

SELECT justify_hours(interval '48 hours 30 mins');

→2 days 00:30:00

sql

SELECT justify_hours(completed_at - started_at) AS duration FROM sessions WHERE completed_at - started_at > interval '24 hours';

→Multi-day session durations in days and hours

sql

SELECT justify_hours(interval '100 hours');

→4 days 04:00:00

⚠Anti-Pattern— Applying justify_hours after justify_days instead of before

Calling `justify_days(justify_hours(iv))` is correct, but reversing the order (`justify_hours(justify_days(iv))`) can produce unexpected results because justify_days converts days to months before justify_hours can promote hours to days.

✓ Instead: Always call `justify_hours` first, then `justify_days`, or use `justify_interval` which applies both in the correct order.

Apply `justify_hours` first (hours → days), then `justify_days` (days → months) for fully normalized interval output. Or use `justify_interval` which does both in one call.

example

SELECT justify_interval(end_ts - start_ts) AS duration FROM sessions;

→Fully normalized interval

justify_interval

PG 8.0+→ interval

Adjusts an interval using justify_days and justify_hours together, with additional sign adjustments.

DeveloperData Eng

Signature

justify_interval ( interval ) → interval

Parameters

Parameter	Type	Description
interval	interval	Interval to normalize

Examples

sql

SELECT justify_interval(interval '1 month -1 hour');

→29 days 23:00:00

sql

SELECT justify_interval(interval '1 year 25 hours');

→1 year 1 day 01:00:00

sql

SELECT name, justify_interval(completed_at - started_at) AS time_taken FROM tasks;

→Human-readable task durations

Sign normalization applied across components

sql

SELECT justify_interval(interval '-2 days 50 hours');

→2 days 02:00:00

⚠Anti-Pattern— Ignoring sign normalization for negative interval components

An interval like `'1 month -1 hour'` has mixed signs that can produce confusing comparisons and displays. Without `justify_interval`, arithmetic on such intervals can yield surprising results.

✓ Instead: Apply `justify_interval()` to any interval derived from subtraction or user input before using it in comparisons or displaying it.

`justify_interval` is the most thorough normalization. Always apply it before showing raw `end - start` intervals to users to ensure the output is readable.

example

SELECT name, justify_interval(completed_at - started_at) AS time_taken FROM tasks;

→"2 mons 3 days 04:30:00"

to_timestamp

PG 8.0+→ timestamp with time zone

Converts a Unix epoch (seconds since 1970-01-01 UTC) or a text string with a format pattern to a timestamp with time zone.

DeveloperData Eng

Signatures

to_timestamp ( double precision ) → timestamp with time zone

to_timestamp ( text, text ) → timestamp with time zone

Parameters

Parameter	Type	Description
epoch	double precision	Unix epoch seconds
string	text	Date/time string to parse
format	text	Format pattern (e.g., 'YYYY-MM-DD HH24:MI:SS')

Examples

sql

SELECT to_timestamp(0);

→1970-01-01 00:00:00+00

sql

SELECT to_timestamp(1741360981);

→2025-03-07 14:23:01+00

sql

SELECT to_timestamp('2025-03-07 14:30', 'YYYY-MM-DD HH24:MI');

→2025-03-07 14:30:00+00

sql

SELECT to_timestamp(log_date, 'DD/Mon/YYYY HH24:MI:SS') FROM apache_logs;

→Properly typed timestamps from Apache Combined Log Format

⚠Anti-Pattern— Storing timestamps as text and parsing on every query

Keeping timestamps as `VARCHAR` columns and calling `to_timestamp(text, format)` in every WHERE clause prevents index use, wastes CPU, and risks format inconsistency across rows.

✓ Instead: Convert text timestamps to `timestamptz` once at load time (e.g., during ETL) and store them in a proper `timestamptz` column.

Use `to_timestamp(text, format)` to parse date strings in non-standard formats (e.g., '07/Mar/2025' or '20250307'). See the `to_char` format patterns — they apply in reverse for parsing.

example

SELECT to_timestamp(log_date, 'DD/Mon/YYYY HH24:MI:SS') FROM apache_logs;

→Properly typed timestamps from Apache log format

to_char

PG 8.0+→ text

Formats a timestamp or interval as a string using a format pattern.

DeveloperData Eng

Signatures

to_char ( timestamp, text ) → text

to_char ( timestamp with time zone, text ) → text

to_char ( interval, text ) → text

Parameters

Parameter	Type	Description
timestamp	timestamp/interval	Value to format
format	text	Format pattern with tokens like YYYY, MM, DD, HH24, MI, SS, Dy, Month, etc.

Examples

sql

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');

→2025-03-07 14:23:01

sql

SELECT to_char(now(), 'Day, DD Month YYYY');

→Friday , 07 March 2025

sql

SELECT to_char(now(), 'YYYYMMDD');

→20250307

FM prefix suppresses padding

sql

SELECT to_char(now(), 'FMDay, FMDDth FMMonth YYYY');

→Friday, 7th March 2025

⚠Anti-Pattern— Using to_char in WHERE clauses for date filtering

Writing `WHERE to_char(created_at, 'YYYY-MM') = '2025-03'` prevents the query planner from using any index on `created_at` because the column is wrapped in a function call.

✓ Instead: Use a range filter: `WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01'` to stay index-friendly.

Format tokens like `Month` and `Day` pad to fixed width with spaces. Prefix with `FM` (fill mode) to suppress: `to_char(now(), 'FMDay, FMDDth FMMonth YYYY')` → 'Friday, 7th March 2025'.

example

SELECT to_char(order_date, 'FMMonth FMDD, YYYY') FROM orders;

→March 7, 2025 (no padding)

isfinite

PG 8.0+→ boolean

Tests if a date, timestamp, or interval is finite (not ±infinity).

DeveloperData EngDBA

Signatures

isfinite ( date ) → boolean

isfinite ( timestamp ) → boolean

isfinite ( interval ) → boolean

Parameters

Parameter	Type	Description
value	date, timestamp, or interval	Value to test

Examples

sql

SELECT isfinite('2025-03-07'::date);

→true

sql

SELECT isfinite('infinity'::date);

→false

sql

SELECT isfinite('-infinity'::timestamp);

→false

sql

SELECT * FROM subscriptions WHERE isfinite(expires_at) AND expires_at < now();

→Only subscriptions with real, past expiry dates — infinity sentinels excluded

⚠Anti-Pattern— Doing date arithmetic on columns that may contain infinity

Expressions like `expires_at - now()` or `date_trunc('day', expires_at)` silently return `infinity` or error when `expires_at` holds an infinity sentinel, skewing aggregates and reports.

✓ Instead: Always guard with `WHERE isfinite(expires_at)` or `CASE WHEN isfinite(expires_at) THEN ... END` before performing arithmetic on timestamp columns that use infinity as a sentinel.

PostgreSQL allows `'infinity'::date` and `'-infinity'::timestamptz` as sentinel values for open-ended ranges. Always filter with `isfinite()` before doing date arithmetic to avoid surprises in reports.

example

SELECT * FROM subscriptions WHERE isfinite(expires_at) AND expires_at < now();

→Only subscriptions with real, past expiry dates

timeofday

PG 8.0+→ text

Returns the current date and time as a formatted text string (advances during statement execution like clock_timestamp).

DeveloperDBA

Signature

timeofday ( ) → text

Examples

sql

SELECT timeofday();

→Fri Mar 7 14:23:01.456789 2025 UTC

sql

DO $ BEGIN RAISE NOTICE 'Checkpoint: %', timeofday(); END $;

→Checkpoint logged to server log with wall-clock text timestamp

Cast back to timestamptz if typed value is needed

sql

SELECT timeofday()::timestamptz;

→2025-03-07 14:23:01.456789+00

sql

SELECT timeofday() AS start_marker; SELECT pg_sleep(0.5); SELECT timeofday() AS end_marker;

→Two text timestamps bracketing a sleep — useful for quick manual timing in psql

⚠Anti-Pattern— Using timeofday() output for timestamp arithmetic

`timeofday()` returns a `text` type. Performing arithmetic or comparisons on it requires an explicit cast to `timestamptz`, which adds noise and can fail if the format changes or the locale differs.

✓ Instead: Use `clock_timestamp()` directly when you need a typed timestamp; reserve `timeofday()` for human-readable log messages only.

`timeofday()` returns a text string — useful for RAISE NOTICE messages but awkward for arithmetic. Use `clock_timestamp()` when you need an actual `timestamptz` value.

example

RAISE NOTICE 'Checkpoint: %', timeofday();

→Human-readable timestamp in pg_log

statement_timestamp

PG 8.2+→ timestamp with time zone

Returns the start time of the current SQL statement (advances between statements but not within one).

DeveloperDBASecurity

Signature

statement_timestamp ( ) → timestamp with time zone

Examples

sql

SELECT statement_timestamp();

→2025-03-07 14:23:01.123+00

sql

SELECT now(), statement_timestamp(), clock_timestamp();

→All three may differ inside a long-running transaction

sql

INSERT INTO audit_log (event, stmt_time) VALUES ('bulk_update', statement_timestamp());

→Each INSERT records the time its statement began

sql

SELECT statement_timestamp() - now() AS stmt_lag;

→Time elapsed since the transaction started, measured at this statement

⚠Anti-Pattern— Using statement_timestamp() as a surrogate for clock_timestamp() in performance measurement

`statement_timestamp()` does not advance within a single statement. If a stored function calls it multiple times during one statement, all calls return the same value — making it useless for intra-statement timing.

✓ Instead: Use `clock_timestamp()` for actual wall-clock elapsed time measurements within a single statement or PL/pgSQL block.

PostgreSQL has three levels of current time: `now()` / `current_timestamp` (frozen at transaction start), `statement_timestamp()` (advances per statement), and `clock_timestamp()` (advances per function call). Use the right one for your use case.

example

SELECT now(), statement_timestamp(), clock_timestamp();

→Three different timestamps if called inside a long transaction

transaction_timestamp

PG 8.2+→ timestamp with time zone

Returns the start time of the current transaction. Equivalent to now() and current_timestamp.

DeveloperDBASecurity

Signature

transaction_timestamp ( ) → timestamp with time zone

Examples

sql

SELECT transaction_timestamp();

→2025-03-07 14:23:01.123+00

Exact equivalents — both return transaction start time

sql

SELECT transaction_timestamp() = now();

→true

sql

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $ BEGIN NEW.updated_at := transaction_timestamp(); RETURN NEW; END $;

→Trigger function setting updated_at to transaction start time

sql

SELECT transaction_timestamp(), statement_timestamp(), clock_timestamp();

→Shows all three current-time granularities for comparison

⚠Anti-Pattern— Using clock_timestamp() for created_at/updated_at audit columns in triggers

Setting audit columns with `clock_timestamp()` means that rows inserted in the same transaction get slightly different timestamps depending on when within the transaction the trigger fires. This breaks the expectation that all rows in one transaction share a single consistent timestamp.

✓ Instead: Use `transaction_timestamp()` (or `now()`) in triggers so all rows touched by one transaction share an identical, consistent audit timestamp.

`transaction_timestamp()` is the most explicit name for the transaction start time — use it in triggers for `created_at`/`updated_at` columns to be crystal-clear about the semantics.

example

NEW.updated_at := transaction_timestamp();

→Audit column set to transaction start time

localtime

PG 8.0+→ time

Returns the current time of day without time zone (session local time at transaction start).

Developer

Signatures

LOCALTIME → time

LOCALTIME ( precision ) → time

Examples

sql

SELECT LOCALTIME;

→14:23:01.123456

sql

SELECT LOCALTIME(0);

→14:23:01

Precision rounds to milliseconds

sql

SELECT LOCALTIME(3);

→14:23:01.123

sql

SELECT CASE WHEN LOCALTIME BETWEEN '09:00' AND '17:00' THEN 'business hours' ELSE 'outside hours' END;

→business hours (server timezone-dependent)

⚠Anti-Pattern— Relying on LOCALTIME for business-hours logic in multi-timezone applications

`LOCALTIME` depends on the session's `TimeZone` setting, which varies per connection in connection-pooled or multi-region environments. Business-hours checks using `LOCALTIME` silently give wrong answers for sessions in unexpected timezones.

✓ Instead: Convert to a specific timezone explicitly: `(now() AT TIME ZONE 'America/New_York')::time` ensures consistent results regardless of session configuration.

`LOCALTIME` returns the time in the session timezone — which may differ per connection. Prefer `now()::time AT TIME ZONE 'UTC'` or explicit timezone handling to avoid inconsistencies.

example

SELECT now() AT TIME ZONE 'America/New_York' AS ny_time;

→Current time in New York, regardless of server timezone

localtimestamp

PG 8.0+→ timestamp (without time zone)

Returns the current date and time without time zone (session local time at transaction start).

Developer

Signatures

LOCALTIMESTAMP → timestamp

LOCALTIMESTAMP ( precision ) → timestamp

Examples

sql

SELECT LOCALTIMESTAMP;

→2025-03-07 14:23:01.123456

sql

SELECT LOCALTIMESTAMP(2);

→2025-03-07 14:23:01.12

Converts local timestamp to UTC timestamptz

sql

SELECT LOCALTIMESTAMP AT TIME ZONE 'UTC' AS utc_ts;

→2025-03-07 14:23:01.123456+00

sql

SELECT LOCALTIMESTAMP::date AS today, LOCALTIMESTAMP::time AS time_now;

→2025-03-07 | 14:23:01.123456

⚠Anti-Pattern— Storing LOCALTIMESTAMP values in timestamptz columns across timezones

`LOCALTIMESTAMP` produces a `timestamp without time zone`. When inserted into a `timestamptz` column, PostgreSQL interprets it using the session timezone. Different sessions in different zones will record different UTC times for what appears to be the same local timestamp.

✓ Instead: Use `CURRENT_TIMESTAMP` or `now()` (both return `timestamptz`) to record an unambiguous UTC-based timestamp regardless of session timezone.

Store timestamps as `timestamptz` (with time zone) to avoid ambiguity. Use `LOCALTIMESTAMP` only when your application guarantees a single-timezone deployment or when interfacing with systems that can't handle timezone offsets.

example

SELECT LOCALTIMESTAMP AT TIME ZONE 'UTC' AS utc_ts;

→Converts local timestamp to UTC

make_time

PG 9.4+→ time

Creates a time value from hour, minute, and seconds components.

DeveloperData Eng

Signature

make_time ( hour int, min int, sec double precision ) → time

Parameters

Parameter	Type	Description
hour	int	Hour (0–23)
min	int	Minute (0–59)
sec	double precision	Seconds (0–60.999)

Examples

sql

SELECT make_time(8, 30, 0);

→08:30:00

sql

SELECT make_time(23, 59, 59.99);

→23:59:59.99

sql

SELECT make_time(open_hour, open_minute, 0) AS opens_at FROM business_hours ORDER BY opens_at;

→Opening times as sortable time values

sql

SELECT id, make_time(hour_col, minute_col, second_col) AS event_time FROM schedule WHERE make_time(hour_col, minute_col, second_col) BETWEEN '08:00' AND '18:00';

→Events during daytime hours, filtered using make_time

⚠Anti-Pattern— Storing and comparing time-of-day as a formatted text string

Keeping time-of-day values as `VARCHAR` like '08:30 AM' requires string manipulation for any comparison or arithmetic, and breaks chronological sorting for 12-hour formats.

✓ Instead: Use the `time` type and construct values with `make_time(hour, min, sec)` for correct ordering and range queries.

When separate hour/minute columns exist in your schema, use `make_time(hour_col, minute_col, 0)` to construct a proper time type for comparisons and sorting.

example

SELECT make_time(open_hour, open_minute, 0) AS opens_at FROM business_hours;

→Opening times as proper time values

PostgreSQL Date & Time Functions

Jump to function

What are PostgreSQL Date & Time Functions?

now

Signature

Examples

current_timestamp

Signature

Examples

current_date

Signature

Examples

clock_timestamp

Signature

Examples

date_trunc

Signatures

Parameters

Examples

date_part

Signatures

Parameters

Examples

extract

Signature

Parameters

Examples

age

Signatures

Parameters

Examples

date_bin

Signatures

Parameters

Examples

make_date

Signature

Parameters

Examples

make_timestamp

Signature

Parameters

Examples

make_timestamptz

Signature

Parameters

Examples

make_interval

Signature

Parameters

Examples

justify_days

Signature

Parameters

Examples

justify_hours

Signature

Parameters

Examples

justify_interval

Signature

Parameters

Examples

to_timestamp

Signatures

Parameters

Examples

to_char

Signatures

Parameters

Examples

isfinite

Signatures

Parameters

Examples

timeofday

Signature

Examples

statement_timestamp

Signature