Current Date and Time Functions

Learning Focus

Use this lesson to choose the right "current time" function in PostgreSQL, understand transaction-time vs statement-time vs wall-clock time, and avoid timezone bugs.

Concept Overview

PostgreSQL provides multiple functions that sound like "the current time", but they do not all mean the same thing.

The biggest source of confusion is that some functions are stable within a transaction, while others reflect the actual wall clock.

Why is it important?

• Correctness: long transactions can record confusing timestamps if you choose the wrong function
• Auditability: consistent created_at / updated_at behavior
• Cross-timezone safety: avoid storing ambiguous times
• Debugging: understand what time a query thinks it is

Where does it fit?

These functions appear everywhere:

• default timestamps (DEFAULT now())
• filtering ("today", "last 7 days")
• generating time windows for reports
• converting between UTC and local time with AT TIME ZONE

---

Syntax & Rules

Core Functions

Function	Returns	Meaning in PostgreSQL
CURRENT_DATE	date	current date in session timezone
CURRENT_TIME	time with time zone	current time (rarely used in schemas)
CURRENT_TIMESTAMP	timestamptz	transaction start timestamp (same as now())
now()	timestamptz	transaction start timestamp
transaction_timestamp()	timestamptz	same as now()
statement_timestamp()	timestamptz	start time of the current statement
clock_timestamp()	timestamptz	actual wall-clock time (changes during a statement/transaction)
LOCALTIME / LOCALTIMESTAMP	time / timestamp	without time zone

Time Zone Basics (PostgreSQL)

• timestamptz is stored in UTC internally and displayed in the session timezone.
• timestamp (without time zone) has no timezone information and is easy to misinterpret.

Check and set the session time zone:

SHOW TIME ZONE;
SET TIME ZONE 'UTC';

Convert display time zones:

SELECT now() AT TIME ZONE 'Asia/Singapore' AS singapore_time;

Key Rules and Considerations

• In autocommit mode (the default), each statement is its own transaction, so now() typically changes per statement.
• Inside an explicit BEGIN ... COMMIT, now() stays constant for the entire transaction.
• Use clock_timestamp() only when you truly need wall-clock time.
• Prefer timestamptz for event times.

---

Step-by-Step Examples

Example 1: Compare Transaction vs Statement vs Wall Clock (Beginner)

SELECT
  now() AS txn_time,
  statement_timestamp() AS stmt_time,
  clock_timestamp() AS wall_time,
  (statement_timestamp() - now()) AS stmt_minus_txn;

Expected output (example):

           txn_time            |           stmt_time           |           wall_time           | stmt_minus_txn
------------------------------+-------------------------------+-------------------------------+----------------
 2026-03-05 09:00:00.123+00   | 2026-03-05 09:00:00.123+00    | 2026-03-05 09:00:00.124+00    | 00:00:00
(1 row)

Explanation:

• now() (transaction time) and statement_timestamp() are often equal in a simple autocommit statement.
• clock_timestamp() can differ because it is evaluated at call time.

---

Example 2: now() Stays Constant Within a Transaction (Intermediate)

BEGIN;

SELECT now() AS t1, clock_timestamp() AS c1;
SELECT pg_sleep(1);
SELECT now() AS t2, clock_timestamp() AS c2;

COMMIT;

Expected output (example):

BEGIN
              t1              |              c1
------------------------------+------------------------------
 2026-03-05 09:00:10.000+00   | 2026-03-05 09:00:10.001+00
(1 row)

 pg_sleep
----------

              t2              |              c2
------------------------------+------------------------------
 2026-03-05 09:00:10.000+00   | 2026-03-05 09:00:11.002+00
(1 row)

COMMIT

Explanation:

• t1 and t2 are identical: now() is transaction start time.
• c1 and c2 change: clock_timestamp() reflects wall time.

---

Example 3: Use Current Date for "Today" Filters (Beginner)

WITH sample AS (
  SELECT * FROM (VALUES
    (1, TIMESTAMPTZ '2026-03-05 01:00+00'),
    (2, TIMESTAMPTZ '2026-03-06 01:00+00')
  ) AS v(id, created_at)
)
SELECT id
FROM sample
WHERE created_at::date = DATE '2026-03-05'
ORDER BY id;

Expected output:

 id
----
 1
(1 row)

Explanation:

• For deterministic examples, we compare to a fixed date.
• In real queries, replace DATE '2026-03-05' with CURRENT_DATE.

---

Example 4: Convert Display Time With AT TIME ZONE (Intermediate)

SELECT
  TIMESTAMPTZ '2026-03-05 12:00+00' AS utc_time,
  (TIMESTAMPTZ '2026-03-05 12:00+00' AT TIME ZONE 'Asia/Singapore') AS singapore_local;

Expected output:

        utc_time        |   singapore_local
------------------------+------------------------
 2026-03-05 12:00:00+00 | 2026-03-05 20:00:00
(1 row)

Explanation:

• AT TIME ZONE converts a timestamptz into a local timestamp in the requested zone.

---

Practical Use Cases

1) created_at Defaults

Context: record when a row was created.

CREATE TABLE audit_events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  action text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

2) updated_at in PostgreSQL (Trigger Pattern)

Context: PostgreSQL does not have MySQL's ON UPDATE CURRENT_TIMESTAMP column clause.

CREATE TABLE items (
  item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Typically implemented with a trigger in production schemas.

3) "Today" Reports

Context: daily metrics.

SELECT COUNT(*)
FROM audit_events
WHERE created_at >= CURRENT_DATE
  AND created_at <  CURRENT_DATE + INTERVAL '1 day';

4) Store in UTC, Convert at the Edge

Context: multi-timezone apps.

SET TIME ZONE 'UTC';
SELECT now();

---

Common Mistakes & Troubleshooting

Mistake 1: Expecting now() to Change Inside a Transaction

Wrong expectation:

BEGIN;
SELECT now();
SELECT pg_sleep(2);
SELECT now();
COMMIT;

What happens: both now() results are the same.

Fix: use clock_timestamp() if you truly need wall-clock time.

---

Mistake 2: Using timestamp for Global Event Time

What happens: you lose timezone context and can display the wrong time.

Fix: use timestamptz.

---

Mistake 3: Mixing Server Time Zone and Application Assumptions

What happens: "today" boundaries shift unexpectedly.

Fix:

SHOW TIME ZONE;
SET TIME ZONE 'UTC';

Debugging tips:

1. Confirm what "now" means: SELECT now(), statement_timestamp(), clock_timestamp();.
2. Confirm the session timezone: SHOW TIME ZONE;.
3. Use timestamptz for event times.
4. When filtering by day, use a half-open interval [day, day+1).

---

Best Practices

• ✅ Use timestamptz for event times; ❌ store global time in timestamp.
• ✅ Use now()/CURRENT_TIMESTAMP for consistent transaction timestamps; ❌ expect it to advance inside a transaction.
• ✅ Use clock_timestamp() only when you need real wall time; ❌ use it everywhere.
• ✅ Standardize on UTC at the DB boundary; ❌ let each session pick a random timezone.
• ✅ Filter "today" using [CURRENT_DATE, CURRENT_DATE + INTERVAL '1 day'); ❌ use ::date = CURRENT_DATE on large tables (can block index usage).

---

Hands-On Practice

Exercise 1 (Easy): Get the current date

Task: Return the current date.

-- Your SQL here

Solution:

SELECT CURRENT_DATE;

---

Exercise 2 (Medium): Compare transaction time to wall time

Task: In a transaction, show that now() stays constant but clock_timestamp() changes.

-- Your SQL here

Solution:

BEGIN;
SELECT now() AS t1, clock_timestamp() AS c1;
SELECT pg_sleep(1);
SELECT now() AS t2, clock_timestamp() AS c2;
COMMIT;

---

Exercise 3 (Advanced): Convert UTC to a local time

Task: Convert TIMESTAMPTZ '2026-03-05 12:00+00' to Asia/Singapore local time.

-- Your SQL here

Solution:

SELECT TIMESTAMPTZ '2026-03-05 12:00+00' AT TIME ZONE 'Asia/Singapore' AS singapore_local;

---

Connection to Other Concepts

Concept	Why it matters
Data types (timestamp vs timestamptz)	determines whether time zone is preserved
Date arithmetic	intervals and date_trunc depend on correct types
Indexing	day filters should preserve index usage
Triggers	typical way to implement updated_at in PostgreSQL
Transactions	explains why now() is stable inside a transaction

---

Visual Learning Diagram

flowchart TD
  A[Transaction begins] --> B[now() / CURRENT_TIMESTAMP\n= transaction start]
  B --> C[statement_timestamp()\n= statement start]
  C --> D[clock_timestamp()\n= wall clock]
  D --> E[COMMIT]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
now() in long transactions	timestamps look "stuck"	use clock_timestamp() if needed
Storing event time as timestamp	timezone ambiguity	use timestamptz
::date filters on large tables	index not used	filter with a range [day, day+1)
Session timezone drift	inconsistent reports	standardize timezone (often UTC)
Misusing AT TIME ZONE	wrong conversions	verify input type (timestamp vs timestamptz)

---

Quick Reference

SELECT CURRENT_DATE
SELECT now()
SELECT statement_timestamp()
SELECT clock_timestamp()
SELECT now() AT TIME ZONE 'UTC'

What's Next

• Next: Formatting and Parsing Dates - Format timestamps for output and parse strings safely.
• Module Overview - Return to the Date and Time Functions index.