TIMESTAMP

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.

TIMESTAMP vs TIMESTAMPTZ

• TIMESTAMP: stores date and time without timezone information. The stored value never changes regardless of the server or session timezone.
• TIMESTAMPTZ: converts inserted values to UTC for storage and converts back to the session timezone on retrieval. PostgreSQL does not store the timezone label itself — only the UTC value.

Demonstration: insert the same value into both columns and change the timezone:

CREATE TABLE timestamp_demo (
  ts   TIMESTAMP,
  tstz TIMESTAMPTZ
);

SET timezone = 'America/Los_Angeles';

INSERT INTO timestamp_demo (ts, tstz)
VALUES ('2016-06-22 19:10:25-07', '2016-06-22 19:10:25-07');

SELECT ts, tstz FROM timestamp_demo;

         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07

Now change the session timezone to America/New_York and query again:

SET timezone = 'America/New_York';
SELECT ts, tstz FROM timestamp_demo;

         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04

The ts (TIMESTAMP) value is unchanged; the tstz (TIMESTAMPTZ) value is adjusted to the new session timezone.

Getting the current timestamp

SELECT NOW();               -- 2024-01-31 21:01:58.985943-05
SELECT CURRENT_TIMESTAMP;   -- 2024-01-31 21:02:04.715486-05
SELECT CURRENT_TIME;        -- 21:02:13.648512-05
SELECT TIMEOFDAY();         -- Wed Jan 31 21:02:20.840159 2024 EST

Converting between timezones

SELECT timezone('America/Los_Angeles', '2016-06-01 00:00'::timestamptz);

      timezone
---------------------
 2016-05-31 21:00:00

Using TIMESTAMPTZ with default values

CREATE TABLE department (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO department (name) VALUES ('IT') RETURNING *;

id | name |          created_at           |          updated_at
----+------+-------------------------------+-------------------------------
 1 | IT   | 2024-01-31 21:25:31.162808-05 | 2024-01-31 21:25:31.162808-05

To auto-update updated_at on row changes, create a trigger:

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

CREATE TRIGGER department_updated_at_trigger
BEFORE UPDATE ON department
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

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 timezone consistently for all connections; inconsistent timezone settings are a common source of off-by-one-hour bugs.
• Use CURRENT_TIMESTAMP or now() as default values 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, always use explicit timezone-aware literals or cast strings to TIMESTAMPTZ to avoid implicit timezone conversion surprises.

Reference: PostgreSQL documentation — Date/Time Types.