TIMESTAMP Data Type in PostgreSQL

PostgreSQL provides two timestamp data types: TIMESTAMP (without timezone) and TIMESTAMPTZ (with timezone). Both use 8 bytes of storage. The critical difference is how they handle timezone context when data is stored and retrieved.

Syntax

column_name TIMESTAMP               -- no timezone; stores value as-is
column_name TIMESTAMP WITH TIME ZONE -- alias for TIMESTAMPTZ
column_name TIMESTAMPTZ             -- recommended shorthand

TIMESTAMP vs. TIMESTAMPTZ

Demonstration — insert the same value into both columns, then change the session timezone:

CREATE TABLE timestamp_demo (
  ts   TIMESTAMP,
  tstz TIMESTAMPTZ
);

SET timezone = 'America/Los_Angeles';

INSERT INTO timestamp_demo (ts, tstz)
VALUES ('2026-04-07 12:00:00-07', '2026-04-07 12:00:00-07');

SELECT ts, tstz FROM timestamp_demo;

          ts           |          tstz
-----------------------+------------------------
 2026-04-07 12:00:00   | 2026-04-07 12:00:00-07

Switch the session timezone to UTC and query again:

SET timezone = 'UTC';
SELECT ts, tstz FROM timestamp_demo;

          ts           |          tstz
-----------------------+------------------------
 2026-04-07 12:00:00   | 2026-04-07 19:00:00+00

ts (TIMESTAMP) never changes; tstz (TIMESTAMPTZ) adjusts to the new session timezone.

Practical Example

Track order lifecycle with TIMESTAMPTZ columns:

CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  product_sku  VARCHAR(50)  NOT NULL,
  quantity     INTEGER      NOT NULL,
  placed_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
  confirmed_at TIMESTAMPTZ,
  shipped_at   TIMESTAMPTZ,
  updated_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

INSERT INTO orders (product_sku, quantity)
VALUES ('SKU-101', 3)
RETURNING id, placed_at;

Getting the Current Timestamp

SELECT now();                -- 2026-04-07 10:00:00.123456+00
SELECT CURRENT_TIMESTAMP;   -- same as now()
SELECT CURRENT_TIME;        -- time only with offset
SELECT LOCALTIME;           -- local time without offset

Timezone Conversion

-- Convert a UTC timestamp to a specific timezone
SELECT '2026-04-07 19:00:00+00'::timestamptz
  AT TIME ZONE 'America/New_York';

-- Or using the timezone() function
SELECT timezone('America/New_York', '2026-04-07 19:00:00+00'::timestamptz);

Auto-Updating updated_at with a Trigger

PostgreSQL does not have an ON UPDATE equivalent, so use a BEFORE UPDATE trigger:

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

CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Testing with Vela

Timezone-related bugs are notoriously hard to reproduce in development but appear immediately in production when servers are deployed across regions. With Vela’s database branching, you can test your timestamp-aware queries on a branch where the session timezone is set to match each of your target deployment regions, catching timezone edge cases before they reach production.

Production Tips

• Use TIMESTAMPTZ (not TIMESTAMP) for all new columns — it correctly handles applications running across multiple timezones or deployed in different regions.
• Set the database or session timezone consistently for all connections; inconsistent timezone settings are a common source of off-by-one-hour bugs.
• Use now() or CURRENT_TIMESTAMP as defaults for created_at columns; use a BEFORE UPDATE trigger for updated_at since PostgreSQL has no built-in auto-update for timestamps.
• When comparing timestamps across timezones, always use TIMESTAMPTZ literals with explicit offsets or AT TIME ZONE conversions.
• Index TIMESTAMPTZ columns on high-volume tables to support efficient range queries — B-tree indexes are fully supported for timestamp range scans.