Date, Time & Timestamptz

Always prefer timestamptz; timezone pitfalls. Practical PostgreSQL patterns for production queries.

Recipe

SHOW TimeZone;
SET TimeZone = 'UTC';
SELECT now(), current_timestamp;

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

SELECT ts AT TIME ZONE 'America/Chicago' AS local_wall,
       ts
FROM (SELECT timestamptz '2026-01-15 12:00:00+00' AS ts) s;

What this demonstrates:

Runnable SQL against sample schemas
Idiomatic PostgreSQL syntax
Patterns safe for EXPLAIN review

Deep Dive

How It Works

PostgreSQL parses SQL into a query tree.
The planner estimates costs using statistics.
Executor returns rows to the client protocol.

Notes

Prefer readable SQL; optimizers rewrite internally.
Test with production-like row counts.

Gotchas

SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

Data Types Basics - overview

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

Sql Data Types Jsonb Date Time And Timestamptz

Date, Time & Timestamptz

Always prefer timestamptz; timezone pitfalls. Practical PostgreSQL patterns for production queries.

Recipe

SHOW TimeZone;
SET TimeZone = 'UTC';
SELECT now(), current_timestamp;

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

SELECT ts AT TIME ZONE 'America/Chicago' AS local_wall,
       ts
FROM (SELECT timestamptz '2026-01-15 12:00:00+00' AS ts) s;

What this demonstrates:

Runnable SQL against sample schemas
Idiomatic PostgreSQL syntax
Patterns safe for EXPLAIN review

Deep Dive

How It Works

PostgreSQL parses SQL into a query tree.
The planner estimates costs using statistics.
Executor returns rows to the client protocol.

Notes

Prefer readable SQL; optimizers rewrite internally.
Test with production-like row counts.

Gotchas

SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

Data Types Basics - overview

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

Date, Time & Timestamptz

Search Documentation

Date, Time & Timestamptz