# Boolean and Dates

> Work with booleans, dates, timestamps, time zones, and intervals.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="13.1" caption="Boolean and Dates"}
-- 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 {#run}

To run:

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

## Common pitfalls {#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`).

> [!TIP] 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.

> [!NOTE] Try it
> Run `SELECT current_timestamp, now(), clock_timestamp();` — `current_timestamp` and `now()` are identical; `clock_timestamp()` can already differ within the same statement. Then `BEGIN; SELECT now(); SELECT pg_sleep(1); SELECT now(); COMMIT;` and observe.

## Downloads {#downloads}

SQL file: [boolean-and-dates.sql](/examples/source/boolean-and-dates.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)