13 Boolean and Dates

PostgreSQL’s boolean type stores true, false, or NULL. For dates and times you have a small family of types: date for a calendar day, time for a time of day, timestamp for date-and-time without a time zone, and timestamptz (timestamp with time zone) for an instant displayed in the session time zone (the original zone/offset is not retained). For almost all application use, timestamptz is what you want.

What you’ll learn

• The boolean type and how it accepts many inputs (true, 't', 'yes', 1)
• The four core date/time types and when each makes sense
• Why timestamptz is preferred over timestamp
• current_date, current_timestamp, now(), localtime
• Date arithmetic with interval

Example

-- Boolean: true, false, NULL
SELECT true AS t, false AS f, (1 = 1) AS same;

-- Boolean accepts many input forms
SELECT 'yes'::boolean AS yes, 'n'::boolean AS n, '1'::boolean AS one;

-- Dates and times
SELECT current_date AS today,
       current_timestamp AS now_tz,
       localtimestamp AS now_local,
       current_time AS time_now;

-- Casting date/time literals
SELECT '2024-01-15'::date AS d,
       '2024-01-15 10:30:00'::timestamp AS ts,
       '2024-01-15 10:30:00+00'::timestamptz AS ts_tz;

-- Arithmetic with interval
SELECT current_date + 7 AS in_a_week,
       current_timestamp - interval '1 hour' AS an_hour_ago,
       date '2024-12-31' - date '2024-01-01' AS days_between;

-- Extracting parts
SELECT extract(year FROM current_date) AS year,
       extract(dow  FROM current_date) AS day_of_week;

The boolean type accepts a generous set of input strings: true, false, t, f, yes, no, y, n, 1, 0, on, off. The output is always shown as t or f in psql. Comparisons (=, <>, etc.) produce booleans; you can store the result of a comparison in a boolean column.

timestamp without time zone (just timestamp) stores a wall-clock value with no notion of where on Earth it was. If you insert 2024-01-15 10:00:00, that is exactly what you get back, regardless of who reads it. timestamp with time zone (timestamptz) stores the same instant in UTC internally and presents it in the session’s time zone. Two sessions in different time zones will see the same timestamptz as different wall-clock times, but the underlying instant is the same.

For nearly every “when did this event happen?” question — order placed, log written, user created — timestamptz is the right answer. Plain timestamp is for things like “the recipe says bake at 10am local time, in whatever local time the user is in” — rare.

Run the example

To run:

$ psql -f source/boolean-and-dates.sql postgres
 t | f | same
---+---+------
 t | f | t
(1 row)
 ...
   today
------------
 2024-xx-xx
(1 row)
 ...

Common pitfalls

• Storing event times as timestamp instead of timestamptz: a year later, you cannot tell whether they were UTC, server-local, or user-local. Always timestamptz.
• Comparing a date or timestamp without time zone to a timestamptz uses the session time zone to interpret the value without a zone. Be explicit when it matters.
• now() returns the time of the transaction start, not the wall-clock now. Inside a long transaction, repeated calls return the same value. Use clock_timestamp() for true wall-clock time.
• interval '1 month' plus a date gives a timestamp in the next month — sometimes with surprises (January 31 + 1 month = February 28/29).

Downloads

SQL file: boolean-and-dates.sql · All lesson files · Setup guide