When you need to record "when did this happen", most people reach for CURRENT_TIMESTAMP. But PostgreSQL ships a whole family of time functions, and the differences decide which type you get and how "fresh" that time really is. Let's make it concrete.
The two headliners: with zone and without
CURRENT_TIMESTAMP returns timestamp with time zone (aka timestamptz), while LOCALTIMESTAMP returns timestamp without time zone. This is not cosmetic: the first stores an absolute instant, the second stores "wall clock" time with no zone attached.
SELECT
CURRENT_TIMESTAMP AS with_zone,
LOCALTIMESTAMP AS without_zone;
The practical takeaway: for a global app you almost always want timestamptz. A users.created_at column should be timestamptz so that a user in Lisbon and one in Moscow record the same absolute instant, not two different "local" readings.
CREATE TABLE users (
id bigserial PRIMARY KEY,
email text NOT NULL UNIQUE,
name text,
country text,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Transaction time, not statement time
The key trait: CURRENT_TIMESTAMP and LOCALTIMESTAMP return the moment the transaction started, and that value stays frozen until commit. Inside one transaction every call returns an identical value, even if seconds passed between them.
BEGIN;
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP;
COMMIT;
This is genuinely useful: if a single transaction inserts an order and an audit row, both get the exact same created_at with no sub-second drift.
BEGIN;
INSERT INTO orders (user_id, amount, status, created_at)
VALUES (42, 99.90, 'paid', CURRENT_TIMESTAMP);
INSERT INTO order_audit (order_id, event, at)
VALUES (currval('orders_id_seq'), 'created', CURRENT_TIMESTAMP);
COMMIT;
When you need "the real now": clock_timestamp()
Sometimes the freeze gets in the way, for example when profiling or stamping rows individually within one query. PostgreSQL gives you a gradient:
transaction_timestamp() is a synonym for CURRENT_TIMESTAMP, the transaction-start moment.statement_timestamp() is the moment the current statement started.clock_timestamp() is the real wall-clock "now", changing on every call, even inside a single SELECT.
SELECT
clock_timestamp() AS row_time
FROM generate_series(1, 3);
Rule of thumb: for audit and business data use CURRENT_TIMESTAMP (consistency wins). For measuring how long steps take inside a function, use clock_timestamp().
Gotcha: now() in PostgreSQL is exactly transaction_timestamp(), so it is frozen too. If a PL/pgSQL loop expects now() to "tick", it will not. Use clock_timestamp() instead.
DEFAULT values for audit columns
The most common scenario is auto-filling create and update columns. created_at is solved with a DEFAULT, while updated_at needs a trigger, because a DEFAULT only fires on INSERT.
CREATE TABLE employees (
id bigserial PRIMARY KEY,
name text NOT NULL,
manager_id bigint REFERENCES employees(id),
dept text,
salary numeric(12,2),
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_touch
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
Differences in MySQL and ClickHouse
- MySQL:
CURRENT_TIMESTAMP returns a DATETIME (no zone) and, unlike PostgreSQL, reflects statement time rather than transaction time. On the upside, it offers the handy created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP plus updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for an automatic updated_at with no trigger.
- ClickHouse: use
now() (type DateTime) or now64() for millisecond precision; LOCALTIMESTAMP is supported as an alias. There is no transaction-level freeze like PostgreSQL's, since the execution model is entirely different.
Bottom line: CURRENT_TIMESTAMP for absolute, zone-aware time, LOCALTIMESTAMP when you do not need a zone, both stable within a transaction, and clock_timestamp() as your tool when you need a real, ticking clock.
When you need to record "when did this happen", most people reach for
CURRENT_TIMESTAMP. But PostgreSQL ships a whole family of time functions, and the differences decide which type you get and how "fresh" that time really is. Let's make it concrete.The two headliners: with zone and without
CURRENT_TIMESTAMPreturnstimestamp with time zone(akatimestamptz), whileLOCALTIMESTAMPreturnstimestamp without time zone. This is not cosmetic: the first stores an absolute instant, the second stores "wall clock" time with no zone attached.SELECT CURRENT_TIMESTAMP AS with_zone, -- timestamptz LOCALTIMESTAMP AS without_zone; -- timestampThe practical takeaway: for a global app you almost always want
timestamptz. Ausers.created_atcolumn should betimestamptzso that a user in Lisbon and one in Moscow record the same absolute instant, not two different "local" readings.CREATE TABLE users ( id bigserial PRIMARY KEY, email text NOT NULL UNIQUE, name text, country text, created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP );Transaction time, not statement time
The key trait:
CURRENT_TIMESTAMPandLOCALTIMESTAMPreturn the moment the transaction started, and that value stays frozen until commit. Inside one transaction every call returns an identical value, even if seconds passed between them.BEGIN; SELECT CURRENT_TIMESTAMP; -- e.g. 12:00:00.000 -- ... heavy query running for 3 seconds ... SELECT CURRENT_TIMESTAMP; -- STILL 12:00:00.000 COMMIT;This is genuinely useful: if a single transaction inserts an order and an audit row, both get the exact same
created_atwith no sub-second drift.BEGIN; INSERT INTO orders (user_id, amount, status, created_at) VALUES (42, 99.90, 'paid', CURRENT_TIMESTAMP); INSERT INTO order_audit (order_id, event, at) VALUES (currval('orders_id_seq'), 'created', CURRENT_TIMESTAMP); COMMIT;When you need "the real now": clock_timestamp()
Sometimes the freeze gets in the way, for example when profiling or stamping rows individually within one query. PostgreSQL gives you a gradient:
transaction_timestamp()is a synonym forCURRENT_TIMESTAMP, the transaction-start moment.statement_timestamp()is the moment the current statement started.clock_timestamp()is the real wall-clock "now", changing on every call, even inside a singleSELECT.SELECT clock_timestamp() AS row_time FROM generate_series(1, 3); -- three DIFFERENT time valuesRule of thumb: for audit and business data use
CURRENT_TIMESTAMP(consistency wins). For measuring how long steps take inside a function, useclock_timestamp().DEFAULT values for audit columns
The most common scenario is auto-filling create and update columns.
created_atis solved with aDEFAULT, whileupdated_atneeds a trigger, because aDEFAULTonly fires onINSERT.CREATE TABLE employees ( id bigserial PRIMARY KEY, name text NOT NULL, manager_id bigint REFERENCES employees(id), dept text, salary numeric(12,2), created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$ BEGIN NEW.updated_at := CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_touch BEFORE UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION touch_updated_at();Differences in MySQL and ClickHouse
CURRENT_TIMESTAMPreturns aDATETIME(no zone) and, unlike PostgreSQL, reflects statement time rather than transaction time. On the upside, it offers the handycreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMPplusupdated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPfor an automaticupdated_atwith no trigger.now()(typeDateTime) ornow64()for millisecond precision;LOCALTIMESTAMPis supported as an alias. There is no transaction-level freeze like PostgreSQL's, since the execution model is entirely different.Bottom line:
CURRENT_TIMESTAMPfor absolute, zone-aware time,LOCALTIMESTAMPwhen you do not need a zone, both stable within a transaction, andclock_timestamp()as your tool when you need a real, ticking clock.