Postgres by Example: 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 the same thing with a time zone offset. 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

-- 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.

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 to a timestamp: PostgreSQL will cast for you, but the result depends on the session's time 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 date in the next month — sometimes with surprises (January 31 + 1 month = February 28/29).

Tip: Set your session's time zone with SET TIME ZONE 'UTC';. In production, run the database in UTC and convert in the application layer when displaying to users.

Try it: Run SELECT current_timestamp, now(), clock_timestamp(); — all three are usually the same outside a transaction. Then BEGIN; SELECT now(); SELECT pg_sleep(1); SELECT now(); COMMIT; and observe.

Source: boolean-and-dates.sql

Next: UUID and JSONB

Home: Postgres by Example