In SQL, how do CURRENT_DATE and CURRENT_TIMESTAMP differ, and why does PostgreSQL's CURRENT_TIMESTAMP stay frozen through a long transaction?
answer
- a day versus a moment
- which session zone says today
- start of what, exactly
- same stamp on every row
- statement_timestamp and clock_timestamp
basics
~10 sCURRENT_DATE gives today's date and CURRENT_TIMESTAMP adds the time of day. PostgreSQL fixes both at the transaction's start, so they do not advance inside a transaction; clock_timestamp() reads the real clock.
solid answer
~50 s`CURRENT_DATE` gives today's date and `CURRENT_TIMESTAMP` gives the date plus the time of day, as a `timestamp with time zone` on PostgreSQL. On PostgreSQL, MySQL and Oracle, "today" comes from the session's time zone, so two sessions can disagree at the same instant, and on Oracle `CURRENT_DATE` still carries a time of day. PostgreSQL returns the start of the current transaction for both, deliberately, so that every row a transaction writes bears the same time stamp. The consequence is that an hour-long batch stamps everything with its start time, and elapsed time measured with two calls comes back as zero. When you need a moving value, use `statement_timestamp()` for the statement's start or `clock_timestamp()` for the real clock. Other engines advance more often: MySQL fixes `CURRENT_TIMESTAMP` per statement, and its `SYSDATE()` reads the clock when it executes.
code
sql · 6 linesBEGIN;
SELECT CURRENT_TIMESTAMP, clock_timestamp();
SELECT pg_sleep(2);
-- CURRENT_TIMESTAMP is unchanged; clock_timestamp() is about 2 seconds later
SELECT CURRENT_TIMESTAMP, clock_timestamp();
COMMIT;go deeper
Recall that CURRENT_DATE is a day and CURRENT_TIMESTAMP also has the time of day. Be ready to use each one in a filter, and to say that the date comes from the session's time zone.
Explain that PostgreSQL fixes these values at the transaction's start, and name statement_timestamp() and clock_timestamp() as the moving alternatives. Say that other engines advance more often, with MySQL fixing the value per statement.
Show where the freeze hurts: batch stamps that all look alike, polling readers that miss rows committed late, and midnight reports reading the wrong day. Then decide per column whether it records the run or the write.
Decide what a timestamp column means across a team, and whether the database or the application supplies the clock. A written convention costs little, and it spares every later consumer from guessing what created_at actually records.
## Two values, two types `CURRENT_DATE` answers "what day is it?" and `CURRENT_TIMESTAMP` answers "what moment is it?". Both can be written without parentheses and both are widely available, but their types and their timing belong to the engine: | Engine | `CURRENT_DATE` | `CURRENT_TIMESTAMP` | When the value is fixed | |---|---|---|---| | PostgreSQL | `date` | `timestamp with time zone` | at the start of the transaction | | MySQL | a date | a date and time with no zone in the value | at the start of the statement | | Oracle | `DATE`, which includes a time of day | `TIMESTAMP WITH TIME ZONE` | advances during a transaction | Two consequences follow from the table: - **Oracle's `CURRENT_DATE` is not a midnight value**, so `WHERE d = CURRENT_DATE` rarely matches unless `d` happens to hold that exact second. - **A MySQL value carries no zone**, so the instant it refers to depends on knowing the session's zone when it was read. SQLite returns both as UTC text, so its "today" can differ from the date on the user's wall. ## Why PostgreSQL freezes the value The PostgreSQL manual calls this a feature. `CURRENT_TIMESTAMP`, `CURRENT_DATE`, `LOCALTIMESTAMP`, `now()` and `transaction_timestamp()` all return the **start time of the current transaction**, so a transaction has one consistent notion of "current", and several modifications inside it bear the same time stamp. In autocommit mode each statement is its own transaction, so the value appears to move with the clock. That appearance is why the behaviour surprises people the first time they run a long `BEGIN ... COMMIT` block. ## What the freeze breaks 1. **Long batches.** A job that runs for an hour stamps its last row with the same time as its first, so `created_at` no longer says when a row was written. 2. **Elapsed-time measurement.** Subtracting two `CURRENT_TIMESTAMP` calls in one transaction always gives zero. 3. **Polling consumers.** A reader that fetches `created_at > last_seen` can miss rows, because a stamp predates its transaction's commit. A row may become visible long after a later stamp has already been read. 4. **Midnight.** A transaction that opens at 23:59 still reports yesterday through `CURRENT_DATE` after midnight has passed. A connection left open inside a transaction has the same problem at larger scale: it keeps returning the time at which that transaction began. ## Choosing the right clock on PostgreSQL | Need | Use | What it returns | |---|---|---| | One stamp for a whole transaction | `CURRENT_TIMESTAMP`, `now()` | transaction start, with zone | | A local reading with no zone | `LOCALTIMESTAMP` | transaction start, no zone | | The start of this statement | `statement_timestamp()` | when the latest command arrived | | The real time | `clock_timestamp()` | changes even within one statement | For a batch, pick by what the column means. A column that says "this run produced these rows" wants the frozen value, and a column that says "this row was written then" wants `clock_timestamp()`. Neither is right by default, so write the choice down in the column's comment or the job's code. Be aware that `clock_timestamp()` in a multi-row insert gives each row a slightly different value, which is correct and can also surprise a test. ## "Today" depends on a time zone `CURRENT_DATE` has to convert an instant into a calendar day, and it uses the **session's time zone** to do so. On PostgreSQL, two sessions with `TimeZone` set to `Asia/Tokyo` and `Pacific/Pago_Pago` see different dates at the same instant. This matters for scheduled reports: - A report whose connection inherits the server's zone shows "yesterday" by that zone, whatever the business uses. - A connection pool can hand out sessions with different settings, so the same query can change its answer. - Naming the zone in the query removes the dependence. On PostgreSQL, `(CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date` returns New York's date in any session. ## A yesterday filter that reads correctly ```sql -- PostgreSQL, created_at is a timestamp column SELECT count(*) FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '1' DAY AND created_at < CURRENT_DATE; ``` The upper bound stops before today's midnight, and `CURRENT_DATE` compared with a timestamp acts as midnight at the start of the day. If `created_at` has a time zone, that midnight is taken in the session's zone, so the query is only as stable as the session setting.
- How do you stamp each row of a long PostgreSQL batch with its real write time?Use `clock_timestamp()` in the insert or as the column's value, since it reads the actual time even within one statement. A `CURRENT_TIMESTAMP` default stays at the transaction's start. Committing in small batches also helps, because each new transaction gets a fresh start time, though rows within one batch still share a stamp.
- On PostgreSQL, how do CURRENT_TIMESTAMP and LOCALTIMESTAMP differ?Both are fixed at the transaction's start, but `CURRENT_TIMESTAMP` returns a `timestamp with time zone`, which pins an instant, and `LOCALTIMESTAMP` returns a plain `timestamp`, which is the session's wall-clock reading with no zone. Storing the plain form makes the value's meaning depend on a session setting nobody recorded.
- Why can a report scheduled just after midnight show the wrong day when it uses CURRENT_DATE?`CURRENT_DATE` uses the session's zone, so a connection in UTC reaches the next day at a different moment than the business does. On PostgreSQL, a transaction opened before midnight also keeps the earlier date. Name the zone in the query and start a fresh transaction for the report.
- CURRENT_TIMESTAMP in PostgreSQL returns the moment each statement runs, even inside a transaction.
- The transaction freeze is portable, because CURRENT_TIMESTAMP behaves the same on every engine.
- On PostgreSQL, CURRENT_DATE is a global value that does not depend on the session's time zone.
- Two CURRENT_TIMESTAMP calls in one PostgreSQL transaction measure the time elapsed between them.
- now() always reads the real clock on PostgreSQL, unlike CURRENT_TIMESTAMP.