Working with Timestamps and Dates in PostgreSQL

PostgreSQL provides great support for handling timestamps and dates, making it a powerful tool for managing temporal data. Whether you're dealing with simple date values or complex timestamp operations, PostgreSQL offers a range of features to meet the needs of any project.

DISCLAIMER: The bulk of this article was written by ChatGPT v4o with some modifications applied by a human being. Use this information with caution and discretion.

Understanding PostgreSQL Timestamps and Dates

In PostgreSQL, timestamps and dates are managed using the following data types:

• timestamp without time zone: Stores date and time without timezone information. It's essentially a naive datetime value and does not include timezone context.

• timestamp with time zone (a.k.a. timestamptz): Stores date and time with timezone information. Despite its name, it actually converts all input to UTC internally, and PostgreSQL will adjust display to the session's timezone.

• date: Stores only the date part (year, month, day) without time.

• time without time zone: Stores only the time part (hours, minutes, seconds) without date or timezone.

• time with time zone: Stores time with timezone information. Rarely used in practice.

Setting Timezone Defaults

PostgreSQL respects timezone configurations at multiple levels. You can configure this at:

• The PostgreSQL server level (postgresql.conf):

timezone = 'UTC'

• Per database session:

SET timezone='UTC';

• Or, in your init.sql (as you might already be doing):

SET TIME ZONE 'UTC';

It's important to remember: the session timezone setting only affects display and input interpretation, not how the data is stored internally.

The Timezone Trap: Why Your UTC-TZ Logic May Be Failing

Here's the kicker. Many developers assume that this expression:

CURRENT_TIMESTAMP AT TIME ZONE 'UTC'

...will return a proper UTC timestamp. And yes, it technically does. But if you try to use that as a DEFAULT value for a column, and you're using timestamp with time zone, you'll get surprising results.

For example:

created_at timestamp with time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')

On the surface, this looks correct. But the result is not UTC, nor is it even aware of timezones anymore. PostgreSQL strips the timestamptz context because you're coercing the value into a literal timestamp (without zone), then storing it in a column expecting timezone context. PostgreSQL doesn't re-add the timezone, it simply assumes your local session TZ (e.g. GMT-5), which gets embedded into the output like this:

"2024-08-29T20:00:00-05:00"

In a Docker container or server running in a different timezone, this can get real ugly, real fast.

The Clean Fix: Use timestamp without time zone

Ironically, the simplest fix is to avoid timestamp with time zone entirely if you're always storing and reading in UTC.

updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()

PostgreSQL will insert the current timestamp as-is, in UTC (assuming your server/session TZ is UTC). No surprises. No -05:00 suffix. Just plain, predictable ISO strings like:

"2024-08-29T20:00:00"

This may seem counterintuitive, but in many server-side apps (especially when your app handles timezone conversion), it's the cleanest route.

The Safer Fix (if you must use timestamptz): Use a Function

If you need to use timestamp with time zone and still want UTC consistency, define a custom function:

CREATE OR REPLACE FUNCTION utc_now()
RETURNS timestamp with time zone AS $$
BEGIN
    RETURN timezone('UTC', now());
END;
$$ LANGUAGE plpgsql;

Then use this in your schema:

created_at TIMESTAMPTZ DEFAULT utc_now()

Now you're storing UTC-timestamped values as timestamptz, and PostgreSQL won't guess or reinterpret anything.

Verify the Server's Timezone

You can also use the SHOW keyword to get the server's current run-time parameter for the timezone like so:

SHOW timezone;
-[ RECORD 1 ]-
TimeZone | UTC

Querying Date Ranges in PostgreSQL

PostgreSQL shines when querying temporal ranges. Here are some practical examples:

Querying Between Two Dates

SELECT *
FROM your_table
WHERE your_date_column BETWEEN '2024-01-01' AND '2024-12-31';

Or use comparison operators for more precision:

SELECT *
FROM your_table
WHERE your_date_column >= '2024-01-01'
  AND your_date_column < '2025-01-01';

Filtering by Day (Ignoring Time)

SELECT *
FROM your_table
WHERE your_timestamp_column::date='2024-08-15';

Relative Time Queries with INTERVAL

SELECT *
FROM your_table
WHERE your_timestamp_column >= NOW() - INTERVAL '30 days';

Monthly Range Query

SELECT *
FROM your_table
WHERE your_timestamp_column >= DATE_TRUNC('month', NOW())
  AND your_timestamp_column < DATE_TRUNC('month', NOW()) + INTERVAL '1 month';

This gets you all records in the current month, excluding anything from the next.

Pro Tips for Working with Timestamps

• Be explicit about timezone handling in both the DB and your application layer.
• If you always want UTC, consider TIMESTAMP WITHOUT TIME ZONE, especially in APIs.
• Avoid coercions like AT TIME ZONE 'UTC' unless you really know what it's doing under the hood.
• Store all times in UTC, then convert for display in the frontend (this aligns with most best practices).
• Use date_trunc('day', ...) or cast to ::date when grouping or filtering by day.
• Always double-check how your Docker or cloud environment sets the server TZ.

Conclusion

PostgreSQL offers powerful tools for working with timestamps and dates, but it can bite you if you're not careful with timezones. The best approach is to be consistent: pick either TIMESTAMPTZ or TIMESTAMP (without time zone), enforce UTC at the database level, and keep conversions in the app layer.

Hopefully, this deeper dive clears up the confusion around timezone behavior and shows you how to manage your temporal data confidently and clearly.

Happy querying!

Sources

• Postgres Docs - 8.5. Date/Time Types
• Troubleshooting Common Errors with PostgreSQL Timestamps with Time Zones