What Is a PostgreSQL Timestamp?
PostgreSQL features two primary timestamp data types to store combined dates and times: TIMESTAMP (without time zone) and TIMESTAMPTZ (with time zone). Both data types require 8 bytes of storage and offer a precision of up to 1 microsecond.
The main differences:
| Feature | TIMESTAMP (without time zone) |
TIMESTAMPTZ (with time zone) |
|---|---|---|
| How it’s Stored | Stores the exact text/literal date and time input. | Converts input to UTC internally for storage. |
| Time zone awareness | Completely ignores time zone offsets or session contexts. | Dynamically shifts based on the client session time zone. |
| Best used for | Abstract or local generic wall-clock times (e.g., “Store opens at 9:00 AM everywhere”). | Specific, real-world chronological instants (e.g., “Order placed at 14:32:01 UTC”). |
Note: Writing just timestamp defaults to timestamp without time zone. To build global applications, the community universally recommends using TIMESTAMPTZ.
Timestamps are essential for tracking events, recording history, and enabling time-based queries within databases. They allow applications to store when a record was created, modified, or when an event occurred, making them fundamental for auditing, logging, and scheduling tasks.
PostgreSQL Timestamp Data Types
timestamp without time zone
The timestamp without time zone data type stores a date and time value without any time zone information. PostgreSQL records the exact date and time provided and does not perform any time zone conversion when the value is stored or retrieved.
This type is useful when the stored value represents a local time that should remain unchanged regardless of the user’s location. Common examples include business hours, appointment times in a specific location, or historical records where time zone context is not required.
Example:
|
1 2 3 4 5 6 |
CREATE TABLE meetings ( scheduled_start TIMESTAMP WITHOUT TIME ZONE ); INSERT INTO meetings (scheduled_start) VALUES ('2026-08-20 09:15:00'); |
timestamp with time zone / timestamptz
The timestamp with time zone data type, commonly abbreviated as timestamptz, stores a point in time and normalizes it to UTC internally. When a value is inserted, PostgreSQL converts it based on the specified time zone or the current session time zone.
When queried, PostgreSQL converts the stored value to the session’s configured time zone for display. This ensures that the same moment in time can be viewed correctly by users in different regions.
Example:
|
1 2 3 4 5 6 |
CREATE TABLE audit_entries ( recorded_at TIMESTAMP WITH TIME ZONE ); INSERT INTO audit_entries (recorded_at) VALUES ('2026-07-20 09:15:00+01'); |
timestamp vs. timestamptz
The main difference between timestamp and timestamptz is how PostgreSQL handles time zones. A timestamp stores only the date and time as entered, while a timestamptz stores an absolute point in time and automatically manages time zone conversions.
Use timestamp when the value should always represent a local time without conversion. Use timestamptz when recording events, transactions, logs, or any data that may be accessed across different time zones.
For most applications that track real-world events, timestamptz is the recommended choice because it prevents ambiguity and ensures consistent time calculations across systems and locations.
Related content: Read our guide to PostgreSQL data types and how they map to SQL, JDBC, and Java.
Creating and Storing Timestamp Columns
Creating timestamp columns
Timestamp columns are created by specifying either the timestamp or timestamptz data type when defining a table. The choice depends on whether the application needs time zone awareness.
Example using timestamp:
|
1 |
CREATE TABLE clinic_visits ( visit_id SERIAL PRIMARY KEY, scheduled_for TIMESTAMP ); |
Example using timestamptz:
|
1 |
CREATE TABLE system_activity ( activity_id SERIAL PRIMARY KEY, occurred_at TIMESTAMPTZ ); |
Both data types support date and time values with microsecond precision. PostgreSQL also allows an optional precision value, such as TIMESTAMP(3), to control the number of fractional second digits stored.
Using DEFAULT now()
A common practice is to automatically populate timestamp columns with the current date and time when a row is inserted. This is typically done using the DEFAULT now() expression.
Example:
|
1 2 |
CREATE TABLE customer_accounts ( account_id SERIAL PRIMARY KEY, registered_at TIMESTAMPTZ DEFAULT now() ); |
When a new row is inserted without specifying created_at, PostgreSQL automatically uses the current timestamp:
|
1 |
INSERT INTO customer_accounts DEFAULT VALUES; |
The now() function returns the current transaction timestamp. Other functions such as CURRENT_TIMESTAMP provide equivalent behavior and are commonly used for the same purpose.
created_at and updated_at Patterns
Many applications include created_at and updated_at columns to track when records are created and last modified. These columns simplify auditing, debugging, and synchronization tasks.
A typical table definition looks like this:
|
1 2 3 4 5 6 |
CREATE TABLE inventory_items ( item_id SERIAL PRIMARY KEY, item_name TEXT NOT NULL, inserted_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, modified_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); |
The created_at value is usually set once when the row is inserted. The updated_at value should be refreshed whenever the row changes. This is commonly implemented with an UPDATE statement or a database trigger that automatically sets updated_at = now() before each update.
|
1 2 |
UPDATE inventory_items SET item_name = 'Wireless Keyboard', modified_at = now() WHERE item_id = 1; |
Using this pattern provides a reliable history of record creation and modification times without requiring application code to manage timestamps manually.
Tips from the expert
Perry Clark
Professional Services Consultant
Perry Clark is a seasoned open source consultant with NetApp. Perry is passionate about delivering high-quality solutions and has a strong background in various open source technologies and methodologies, making him a valuable asset to any project.
In my experience, here are tips that can help you better work with PostgreSQL timestamps:
- Use
clock_timestamp()when measuring query durations:now()andCURRENT_TIMESTAMPare fixed at the start of the transaction. If you need the actual wall-clock time during long-running operations, useclock_timestamp(), otherwise duration calculations inside a transaction can be misleading. - Store the user’s IANA time zone separately: Even when using
TIMESTAMPTZ, store values likeAmerica/New_YorkorEurope/Berlinin a separate column when user-local rendering matters. UTC alone cannot reconstruct future daylight-saving behavior if business rules depend on the user’s locale. - Partition large event tables by timestamp: For billions of rows, native range partitioning on
created_atoften provides larger gains than indexing alone. Monthly or daily partitions allow PostgreSQL to eliminate entire partitions during scans. - Be careful with daylight-saving transitions: Local times such as
2026-11-01 01:30may occur twice in some regions. When processing financial transactions, audit logs, or scheduling systems, always convert to UTC before performing comparisons or duration calculations. - Use generated columns for reporting periods: If analysts frequently group by date, week, or month, consider generated columns such as
created_date DATE GENERATED ALWAYS AS (...) STORED. This can reduce repeated computation and simplify indexing strategies.
Inserting and Updating Timestamp Values
Inserting timestamp literals
Timestamp values can be inserted directly using string literals in a format that PostgreSQL recognizes. The most common format is YYYY-MM-DD HH:MI:SS.
Example:
|
1 2 |
INSERT INTO events (event_time) VALUES ('2026-06-15 14:30:00'); |
You can also use the TIMESTAMP keyword to make the data type explicit:
|
1 2 |
INSERT INTO events (event_time) VALUES (TIMESTAMP '2026-06-15 14:30:00'); |
PostgreSQL supports fractional seconds as well:
|
1 2 |
INSERT INTO events (event_time) VALUES ('2026-06-15 14:30:00.123456'); |
Inserting Timestamps with Time Zone Offsets
When working with timestamptz columns, you can include a time zone offset in the inserted value. PostgreSQL converts the value to UTC internally while preserving the exact point in time.
Example:
|
1 |
INSERT INTO appointments (appointment_time) VALUES ('2026-08-10 09:45:00'); |
You can also specify the offset using hours and minutes:
|
1 |
INSERT INTO audit_logs (created_at) VALUES ('2026-08-10 09:45:00+04:00'); |
Named time zones are supported as well:
|
1 |
INSERT INTO audit_logs (created_at) VALUES ('2026-08-10 09:45:00 Europe/London'); |
Regardless of the format used, PostgreSQL stores the same absolute moment and converts it to the session time zone when queried.
Updating Timestamp Columns
Timestamp columns can be updated using standard UPDATE statements. This is often used to record when an event occurred or when data was modified.
Example:
|
1 |
UPDATE shipments SET delivered_at = CURRENT_TIMESTAMP WHERE shipment_id = 501; |
You can also assign a specific timestamp value:
|
1 2 |
UPDATE shipments SET delivered_at = TIMESTAMP '2025-09-22 18:30:00' WHERE shipment_id = 501; |
Updates can target multiple rows based on conditions, making it easy to maintain time-related data across a table.
Auto-Updating updated_at
A common requirement is to automatically update an updated_at column whenever a row changes. PostgreSQL does not provide this behavior automatically, but it can be implemented with a trigger.
First, create a trigger function:
|
1 2 3 |
CREATE OR REPLACE FUNCTION set_row_modified_time() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; |
Then create a trigger that runs before each update:
|
1 2 |
CREATE TRIGGER shipments_updated_at_trigger BEFORE UPDATE ON shipments FOR EACH ROW EXECUTE FUNCTION set_row_modified_time(); |
With this setup, the updated_at column is automatically refreshed whenever a row is modified, ensuring that the timestamp always reflects the most recent change.
Common PostgreSQL Timestamp Operations and Functions
Fetching the Current Time
PostgreSQL provides several functions for retrieving the current date and time. These functions are commonly used when inserting records, generating reports, or performing time-based calculations.
Get the current timestamp:
|
1 |
SELECT now(); |
Using the SQL-standard equivalent:
|
1 |
SELECT CURRENT_TIMESTAMP; |
Get only the current date:
|
1 |
SELECT CURRENT_DATE; |
Get only the current time:
|
1 |
SELECT CURRENT_TIME; |
For most use cases, now() and CURRENT_TIMESTAMP are interchangeable and return the current date and time with time zone information.
Shifting Time Zones (AT TIME ZONE)
The AT TIME ZONE operator converts timestamps between time zones. It is useful when displaying data for users in different regions or when converting stored values to a specific local time.
Convert a timestamptz value to a local time zone:
|
1 |
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London'; |
Interpret a timestamp as belonging to a specific time zone:
|
1 |
SELECT TIMESTAMP '2025-09-22 18:45:00' AT TIME ZONE 'Asia/Dubai'; |
This operator helps ensure that timestamps are displayed correctly while preserving the underlying point in time.
Truncating Timestamps
The date_trunc() function rounds timestamps down to a specified unit such as hour, day, month, or year. It is frequently used for grouping and reporting.
Truncate to the hour:
|
1 |
SELECT date_trunc('hour', now()); |
Truncate to the day:
|
1 |
SELECT date_trunc('day', now()); |
Truncate to the month:
|
1 |
SELECT date_trunc('month', now()); |
A common use case is aggregating records by day or month:
|
1 2 |
SELECT date_trunc('month', paid_at) AS billing_month, COUNT(*) AS total_payments FROM payments GROUP BY billing_month; |
Formatting and Parsing
PostgreSQL provides the to_char() function for formatting timestamps as strings and to_timestamp() for converting strings into timestamp values.
Format a timestamp:
|
1 |
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'); |
Example output:
|
1 |
2026-06-15 14:30:00 |
Convert a string to a timestamp:
|
1 2 3 4 |
SELECT to_timestamp( '22-09-2026 18:45:30', 'DD-MM-YYYY HH24:MI:SS' ); |
These functions are useful when importing data, generating reports, or creating custom date and time formats.
Extracting Date Parts
The extract() function retrieves individual components from a timestamp, such as the year, month, day, or hour.
Extract the year:
|
1 |
SELECT extract(year FROM now()); |
Extract the month:
|
1 |
SELECT extract(month FROM now()); |
Extract the day of the week:
|
1 |
SELECT extract(dow FROM now()); |
Extract the hour:
|
1 |
SELECT extract(hour FROM now()); |
This function is commonly used for filtering, grouping, and analyzing time-based data. For example, you can count events by month, identify activity by hour, or generate reports based on specific date components.
Querying and Comparing PostgreSQL Timestamps
PostgreSQL supports a range of operators for filtering, comparing, and calculating timestamp values. These operations are commonly used in reporting, auditing, monitoring, and time-based application logic.
Filter records after a specific timestamp:
|
1 |
SELECT * FROM payments WHERE paid_at > TIMESTAMP '2026-07-01 00:00:00'; |
Filter records within a date range:
|
1 2 |
SELECT * FROM payments WHERE paid_at BETWEEN TIMESTAMP '2026-07-01 00:00:00' AND TIMESTAMP '2026-07-31 23:59:59'; |
Find records created in the last 24 hours:
|
1 2 |
SELECT * FROM user_sessions WHERE started_at >= CURRENT_TIMESTAMP - INTERVAL '24 hours'; |
Find records older than 30 days:
|
1 2 |
SELECT * FROM user_sessions WHERE started_at < CURRENT_TIMESTAMP - INTERVAL '30 days'; |
Timestamp values can also be compared directly using standard comparison operators:
| Operator | Description |
|---|---|
= |
Equal to |
!= or <> |
Not equal to |
< |
Earlier than |
> |
Later than |
<= |
Earlier than or equal to |
>= |
Later than or equal to |
For example:
|
1 |
SELECT * FROM webinars WHERE scheduled_at >= CURRENT_TIMESTAMP; |
PostgreSQL allows arithmetic operations with timestamps and intervals. Subtracting two timestamps returns the time difference between them:
|
1 |
SELECT completed_at - queued_at AS processing_time FROM background_tasks; |
Example result:
|
1 |
02:15:34 |
Adding or subtracting intervals shifts a timestamp by a specified amount of time:
|
1 2 3 |
SELECT now() + INTERVAL '15 days'; SELECT now() - INTERVAL '23 minutes'; |
When querying large tables, timestamp columns are often indexed to improve performance:
|
1 |
CREATE INDEX idx_payments_paid_at ON payments (paid_at); |
Indexes can significantly speed up range queries and time-based filtering, especially for tables that store large volumes of historical data.
PostgreSQL Timestamp Performance Tips
Here are some tips to consider when using timestamps in PostgreSQL.
1. Indexing Timestamp Columns
Timestamp columns used in filters, joins, or sorting should usually be indexed. This is especially important for tables that store events, logs, orders, or audit records.
Example:
|
1 |
CREATE INDEX idx_payments_paid_at ON payments (paid_at); |
This index helps queries that search by time range:
|
1 2 |
SELECT * FROM payments WHERE paid_at >= TIMESTAMP '2026-07-01 00:00:00' AND paid_at < TIMESTAMP '2026-08-01 00:00:00'; |
B-tree indexes are the default and work well for most timestamp comparisons, including =, <, >, <=, >=, and ORDER BY.
2. Writing Efficient Timestamp Range Queries
Use half-open ranges when filtering timestamp values. A half-open range includes the start time and excludes the end time.
Instead of this:
|
1 |
WHERE created_at BETWEEN '2025-05-01' AND '2025-05-31' |
Use this:
|
1 2 |
WHERE created_at >= '2025-05-01' AND created_at < '2025-06-01' |
This avoids missing rows that occur later on the final day, such as 2025-05-31 18:45:00. It also avoids relying on artificial end values like 23:59:59.999999.
3. Avoiding Functions on Indexed Timestamp Columns
Applying a function to an indexed timestamp column can prevent PostgreSQL from using a normal index efficiently.
Avoid this pattern:
|
1 |
WHERE date_trunc('month', paid_at) = TIMESTAMP '2026-07-01'; |
Use a range condition instead:
|
1 2 |
WHERE paid_at >= TIMESTAMP '2026-07-01 00:00:00' AND paid_at < TIMESTAMP '2026-08-01 00:00:00'; |
The second query allows PostgreSQL to use an index on created_at directly. This pattern is usually faster on large tables.
4. Using Partial Indexes for Recent Data
Partial indexes index only rows that match a condition. They are useful when most queries target recent data, such as the last few days or months.
Example:
|
1 2 |
CREATE INDEX idx_invoices_recent_due_at ON invoices (due_at) WHERE due_at >= DATE '2026-04-01'; |
Queries that match the same condition can use the smaller index:
|
1 2 |
SELECT * FROM invoices WHERE due_at >= DATE '2026-04-01' AND payment_status = 'unpaid'; |
Partial indexes reduce index size and maintenance cost, but the condition should match real query patterns. Avoid using moving expressions such as now() in the index predicate, because the index definition does not automatically move forward over time.
5. Using BRIN Indexes for Append-Only Time-Series Tables
BRIN indexes are useful for very large tables where rows are inserted in timestamp order. They store summaries of page ranges instead of indexing every row, so they are much smaller than B-tree indexes.
Example:
|
1 2 |
CREATE INDEX idx_app_events_occurred_at_brin ON app_events USING BRIN (occurred_at); |
BRIN indexes work well for append-only tables such as logs, metrics, and event streams:
|
1 2 |
SELECT * FROM app_events WHERE occurred_at >= CURRENT_TIMESTAMP - INTERVAL '6 hours'; |
They are less precise than B-tree indexes, but they can greatly reduce scanning on large time-series tables. For smaller tables or highly selective lookups, a B-tree index is often better.
Managing time-based PostgreSQL data at scale with Instaclustr
Whether you are storing audit trails, event logs, or transaction histories with TIMESTAMPTZ, getting reliable, performant time-based data in production depends on a well-run database. Instaclustr customizes and optimizes the configuration of PostgreSQL (also known as Postgres) instances on all major cloud providers and on-premises data centers, delivering a production-ready, fully hosted and managed PostgreSQL cluster backed by 24×7 support so your team can focus on building applications instead of operating infrastructure.
Key capabilities of Instaclustr for PostgreSQL:
- Fully managed, 100% open source: Run PostgreSQL in your own cloud provider account or Instaclustr’s, with 24×7 support, built-in monitoring, and SOC2, ISO27001, and ISO27018 certifications, provisioned via console, API, or Terraform provider with no proprietary lock-in.
- 99.99% availability SLA: Industry-leading SLAs for PostgreSQL that set the managed platform apart for production workloads.
- Multi-region replication: Read replicas can be created in secondary regions for high availability, minimizing latency and maximizing uptime across geographies.
- Multi-version concurrency control (MVCC): PostgreSQL snapshots data as it was at the start of each transaction, enabling reads without locking and significantly enhanced read/write performance, which keeps time-stamped, concurrent writes consistent.
- PGBouncer connection pooling: A lightweight connection pooler that enhances database performance and scalability through efficient connection management, scalable performance, and resource optimization.
- Continuous maintenance and DevOps-friendly access: Automated provisioning and configuration, continuous maintenance and version upgrades, and REST API or Terraform provisioning with Prometheus and REST-based monitoring integrations.
Eliminate the burden of self-managed PostgreSQL and run reliable, time-aware databases in the cloud or on-premises. Learn more about managed PostgreSQL on the Instaclustr platform.
