Handling Timestamps: Temporal Data in PostgreSQL

Previously in this course, we explored working with UUIDs to ensure unique, scalable record identification. Now that we have a solid foundation for our IDs, we need to track when our data changes.

Managing time is notoriously difficult in software engineering because of daylight savings, server configuration shifts, and global users. In this lesson, we will implement robust date handling in our store database.

Understanding TIMESTAMP WITH TIME ZONE

In PostgreSQL, you have two primary options for storing dates and times: TIMESTAMP (without time zone) and TIMESTAMP WITH TIME ZONE (often abbreviated as TIMESTAMPTZ).

As a rule of thumb for any production application: always use TIMESTAMP WITH TIME ZONE.

When you store data as TIMESTAMPTZ, PostgreSQL converts the time into UTC internally. When you query the data, PostgreSQL converts it back to the time zone defined in your session. This avoids the "floating time" problem where a record created at 10:00 AM on a server in New York looks different to a user in London.

Tracking Record Creation

To track when a record is added to our orders table, we use the DEFAULT constraint combined with the CURRENT_TIMESTAMP function. This ensures that every time we INSERT a row without an explicit date, PostgreSQL automatically records the exact moment of creation.

SQL
ALTER TABLE orders 
ADD COLUMN created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;

With this constraint, you no longer need to worry about passing date strings from your application code during the insert operation; the database handles it reliably.

Filtering by Date Ranges

Once you have your created_at column populated, you will frequently need to pull reports, such as "all orders from last month." Filtering by time follows the same logical operators we covered in advanced filtering operators.

PostgreSQL treats timestamps as comparable objects. You can use standard comparison operators or the BETWEEN keyword to define your window.

Worked Example: Querying Recent Orders

Imagine you want to see all orders created within a specific week in October 2023. You can execute the following query:

SQL
SELECT order_id, customer_id, created_at
FROM orders
WHERE created_at >= '2023-10-01 00:00:00+00' 
  AND created_at <= '2023-10-07 23:59:59+00';

Alternatively, using the BETWEEN operator:

SQL
SELECT order_id, customer_id 
FROM orders
WHERE created_at BETWEEN '2023-10-01' AND '2023-10-07';

Note: When using BETWEEN with timestamps, be aware that it is inclusive. If you use 2023-10-07, it effectively means 2023-10-07 00:00:00, which might exclude orders placed later on that final day.

Hands-on Exercise

1. Add a created_at column to your products table using TIMESTAMP WITH TIME ZONE.
2. Set the default value to CURRENT_TIMESTAMP.
3. Insert a new product into the table without specifying a date.
4. Verify the date was created automatically by running a SELECT statement on that product.

Common Pitfalls

• Mixing Time Zones: If you use TIMESTAMP (without time zone), the database will store whatever time you send it. If one part of your app sends UTC and another sends local time, your database will become a source of confusion. TIMESTAMPTZ forces a normalized UTC standard.
• Ignoring Timezone Offsets: If your application logic relies on specific local times (e.g., "The store opens at 9 AM local time"), storing only UTC isn't enough. You may eventually need to store the user's timezone separately or handle the conversion in your application layer.
• Performance on Large Tables: If your orders table grows into the millions of rows, filtering by a range on a non-indexed timestamp column will be slow. We will cover how to optimize these queries with indexes later in this course.

FAQ

Q: Can I change a column from TIMESTAMP to TIMESTAMPTZ later? A: Yes, using ALTER TABLE table_name ALTER COLUMN column_name TYPE TIMESTAMPTZ. PostgreSQL will attempt to cast the existing values, assuming they are in UTC.

Q: Does CURRENT_TIMESTAMP change if I update a row? A: No. It only executes when the row is first created if it is set as a DEFAULT. If you want to track the last modification time, you would need to use a database TRIGGER.

Q: Why does my query output look different than what I inserted? A: That is the magic of TIMESTAMPTZ. It is displaying the stored UTC value adjusted to your current database connection's timezone setting.

Recap

Temporal data is best managed by storing it in a normalized format. By using TIMESTAMP WITH TIME ZONE, we ensure consistency. We learned to:

1. Apply TIMESTAMPTZ for timezone-safe storage.
2. Automate creation logs using DEFAULT CURRENT_TIMESTAMP.
3. Filter data using standard comparison operators to isolate specific time windows.

Up next: Establishing Naming Conventions — we'll ensure our database schema remains readable and consistent as it grows.