Right, let’s talk about the three amigos of temporal data: date, time, and timestamp. You’d think this would be simple, but oh no. This is where databases love to get philosophical on you. The core idea is a good one: separate the concepts. A date is just a calendar date. A time is just a time of day, unmoored from any specific date. A timestamp (also called timestamp without time zone) is the combination of the two, a specific instant on the timeline, but it’s playing a sneaky trick on you that we’ll get to.

The Date: Your Calendar Friend

The date type is the least problematic of the bunch. It stores a year, month, and day. No time, no time zones, no daylight saving nonsense. It’s perfect for birthdays, holidays, or any event that happens on a day, not at a precise moment.

-- This is straightforward. Love it.
SELECT '2023-10-31'::date AS halloween;
--   halloween
-- -------------
--  2023-10-31

The best part? Simple math. You can add and subtract integers to move days around the calendar.

SELECT CURRENT_DATE AS today,
       CURRENT_DATE + 7 AS next_week;
--     today    | next_week
-- ------------+------------
--  2023-10-26 | 2023-11-02

Pitfall: The string format matters. '10/31/2023' might be interpreted as October 31st or… October 31st? Wait, no, it’s a trap! This format is ambiguous and often defaults to the frustrating MDY (Month-Day-Year) format depending on your DateStyle setting. Just save yourself the future aneurysm and always use the unambiguous ISO format: 'YYYY-MM-DD'. It just works, everywhere, always.

The Time: A Moment Unanchored

time (or time without time zone) is just a time of day: hours, minutes, seconds, and fractional seconds. It has no concept of a date or a time zone. This is useful for store opening hours, daily alarms, or the start time of a recurring meeting.

SELECT '15:30:00'::time AS meeting_time;
-- meeting_time
-- -------------
-- 15:30:00

You can do arithmetic with intervals (which we’ll cover next), but adding a time to a date is where the magic happens to create a full timestamp.

SELECT CURRENT_DATE AS today,
       '09:00:00'::time AS start_time,
       CURRENT_DATE + '09:00:00'::time AS full_timestamp;
--    today    | start_time |    full_timestamp
-- ------------+------------+---------------------
--  2023-10-26 | 09:00:00   | 2023-10-26 09:00:00

Pitfall: This type is utterly useless for recording when an event actually happened. Without a time zone, 09:00 is just a idea, not a point on the timeline. Was it 9 AM in Tokyo or New York? The database has no idea, and neither will you later.

The Timestamp (Without Time Zone): The Liar

This is the big one, and its name is a dirty lie. timestamp or timestamp without time zone. The name suggests it’s time zone agnostic, but that’s not quite right. Think of it as a picture of a clock. It stores a date and a time, but it does not store which time zone that clock was in. It’s just the numbers it saw.

When you insert a timestamp string that includes a time zone offset (like 2023-10-26 09:00:00-04), the database silently throws the time zone information away and only stores the local date and time components. It’s a data-destroying operation. Let that sink in.

-- See the deception in action?
SELECT '2023-10-26 09:00:00-04'::timestamp AS no_tz_here;
--      no_tz_here
-- ---------------------
--  2023-10-26 09:00:00

The offset -04 (Eastern Daylight Time) is gone. Forever. What’s stored is just “2023-10-26 at 09:00”. This is fine if and only if you are absolutely certain that all your timestamps are meant to be interpreted in the same time zone (e.g., all in ‘UTC’ or all in ‘America/New_York’). But the moment you need to compare a timestamp stored in EST to one stored in PST, you’re in for a world of pain because the database can’t help you—it thinks they’re both just local times.

Why would you ever use this? Performance, mostly. It’s a simpler, faster type because it doesn’t have to do time zone conversions. For internal logging where everything is UTC, or for application-level events that you know will always be relative to a single server’s time, it can be a valid choice. But you must be hyper-vigilant. The default behavior of silently stripping time zone info is, in my professional opinion, a questionable choice by the designers. It’s the number one source of time-related bugs I see.

So when do you use it? When you can guarantee the context of the timezone is already known and fixed. For almost everything else, especially user-facing timestamps, you want the next type we’ll cover: timestamptz. But that’s a story for the next section. For now, just remember: timestamp is a picture of a clock. It doesn’t know where that clock was.