Now() without timezone

Understanding and Using NOW() Without Timezone in PostgreSQL

Explore the nuances of PostgreSQL's NOW() function and how to handle timestamps without explicit timezone information for consistent data storage.

When working with timestamps in PostgreSQL, developers often encounter the NOW() function. While seemingly straightforward, its behavior regarding timezones can lead to confusion and unexpected results if not properly understood. This article delves into how NOW() operates, particularly when dealing with TIMESTAMP WITHOUT TIME ZONE columns, and provides best practices for ensuring data consistency.

The Nature of NOW() and TIMESTAMP WITHOUT TIME ZONE

PostgreSQL offers two primary timestamp types: TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE. The NOW() function (which is equivalent to transaction_timestamp()) returns the start time of the current transaction as a TIMESTAMP WITH TIME ZONE value. However, when this value is inserted into a column defined as TIMESTAMP WITHOUT TIME ZONE, PostgreSQL performs an implicit conversion.

This conversion involves stripping the timezone information and adjusting the timestamp to the database server's configured timezone (or the session's TimeZone setting). This means that if your server is set to UTC, NOW() will be converted to UTC before the timezone is dropped. If your server is in EST, it will be converted to EST. This behavior is crucial because it means the 'local' time stored is dependent on the server's configuration, not necessarily the client's or a universal standard like UTC.

flowchart TD
    A[Client Request] --> B{PostgreSQL Server}
    B --> C["Get NOW() (TIMESTAMP WITH TIME ZONE)"]
    C --> D{"Is Target Column TIMESTAMP WITHOUT TIME ZONE?"}
    D -- Yes --> E["Convert to Server's TimeZone"]
    E --> F["Strip TimeZone Information"]
    F --> G[Store as TIMESTAMP WITHOUT TIME ZONE]
    D -- No --> H[Store as TIMESTAMP WITH TIME ZONE]
    G --> I[Data Stored]
    H --> I[Data Stored]

Flowchart illustrating NOW() conversion for TIMESTAMP WITHOUT TIME ZONE

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    created_at TIMESTAMP WITHOUT TIME ZONE
);

-- Set server timezone for demonstration (e.g., UTC)
SET TIME ZONE 'UTC';

INSERT INTO events (event_name, created_at) VALUES
('Transaction Start UTC', NOW());

-- Change server timezone (e.g., 'America/New_York')
SET TIME ZONE 'America/New_York';

INSERT INTO events (event_name, created_at) VALUES
('Transaction Start EST', NOW());

SELECT event_name, created_at FROM events;

Demonstrating NOW() behavior with different server timezones

Best Practices for Timezone Handling

To avoid ambiguity and ensure data consistency, especially in distributed systems or applications serving users across different timezones, it's generally recommended to store all timestamps in UTC. This provides a universal reference point, and timezone conversions can then be handled at the application layer or when displaying data to users.

If you must use TIMESTAMP WITHOUT TIME ZONE, ensure your database server's TimeZone setting is consistently UTC. This way, NOW() will always store UTC values, even without the explicit timezone information. Alternatively, explicitly cast NOW() to AT TIME ZONE 'UTC' before insertion.

-- Recommended: Store all timestamps as TIMESTAMP WITH TIME ZONE
CREATE TABLE events_tz (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE
);

-- NOW() automatically stores in UTC if server is UTC, or converts if server is different
INSERT INTO events_tz (event_name, created_at) VALUES
('Event with TZ', NOW());

-- If using TIMESTAMP WITHOUT TIME ZONE, explicitly convert to UTC
CREATE TABLE events_no_tz_utc (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    created_at TIMESTAMP WITHOUT TIME ZONE
);

INSERT INTO events_no_tz_utc (event_name, created_at) VALUES
('Event forced UTC', NOW() AT TIME ZONE 'UTC');

SELECT event_name, created_at FROM events_tz;
SELECT event_name, created_at FROM events_no_tz_utc;

Examples of storing timestamps with and without explicit UTC conversion