PostgreSQL Date & Time Functions

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.

`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.

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`.

`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.

`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_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.

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.

`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.

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.

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.

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.

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.

`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.

`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.

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.

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.

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.

`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.

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.

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.

`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.

`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.

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.

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.

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.