We can't find the internet
Attempting to reconnect
Something went wrong!
Hang in there while we get back on track
Date, Boolean & NULL Types
Dates, booleans, and NULL are fundamental types you will encounter in virtually every database. PostgreSQL handles each of them with care and provides powerful functions for working with temporal data. This lesson covers all three.
Date and Time Types
PostgreSQL offers several types for storing temporal data:
| Type | Stores | Example |
|---|---|---|
DATE |
Date only (year, month, day) | 2024-03-15 |
TIME |
Time only (hours, minutes, seconds) | 14:30:00 |
TIMESTAMP |
Date and time | 2024-03-15 14:30:00 |
TIMESTAMPTZ |
Date, time, and timezone | 2024-03-15 14:30:00+05:30 |
INTERVAL |
A duration of time | 3 days 4 hours |
DATE
DATE stores a calendar date without any time component:
SELECT DATE '2024-03-15' AS a_date;
SELECT CURRENT_DATE AS today;
The standard format is YYYY-MM-DD (ISO 8601). PostgreSQL also accepts other formats, but ISO 8601 is the safest and most portable.
TIMESTAMP and TIMESTAMPTZ
TIMESTAMP stores a date and time:
SELECT TIMESTAMP '2024-03-15 14:30:00' AS a_timestamp;
SELECT NOW() AS current_timestamp;
TIMESTAMPTZ (short for TIMESTAMP WITH TIME ZONE) also stores timezone information:
SELECT TIMESTAMPTZ '2024-03-15 14:30:00+05:30' AS with_tz;
SELECT NOW() AS current_timestamp_with_tz;
Best practice: Use TIMESTAMPTZ when your application deals with users in multiple time zones. PostgreSQL stores the value in UTC internally and converts to the session timezone on display.
INTERVAL
INTERVAL represents a duration of time, not a specific point in time:
SELECT
INTERVAL '1 year' AS one_year,
INTERVAL '3 months' AS three_months,
INTERVAL '7 days' AS one_week,
INTERVAL '2 hours 30 minutes' AS two_and_half_hours;
Intervals are powerful because you can add and subtract them from dates:
SELECT
CURRENT_DATE AS today,
CURRENT_DATE + INTERVAL '7 days' AS next_week,
CURRENT_DATE - INTERVAL '1 month' AS last_month,
CURRENT_DATE + INTERVAL '1 year' AS next_year;
Date Arithmetic
Adding and Subtracting Dates
-- Add days to a date
SELECT DATE '2024-01-15' + 30 AS thirty_days_later;
-- Returns: 2024-02-14
-- Subtract dates to get the difference
SELECT DATE '2024-12-31' - DATE '2024-01-01' AS days_between;
-- Returns: 365
-- Using intervals
SELECT DATE '2024-01-15' + INTERVAL '3 months' AS three_months_later;
-- Returns: 2024-04-15
Real-World Examples with Employee Data
-- How long has each employee been with the company?
SELECT
name,
hire_date,
CURRENT_DATE - hire_date AS days_employed
FROM employees
ORDER BY days_employed DESC;
-- Using AGE for a human-readable duration
SELECT
name,
hire_date,
AGE(hire_date) AS tenure
FROM employees
ORDER BY hire_date;
The AGE function returns an interval like 3 years 4 months 12 days, which is much more readable than a raw number of days.
Extracting Date Parts
The EXTRACT function pulls specific components from a date or timestamp:
SELECT
EXTRACT(YEAR FROM DATE '2024-03-15') AS the_year, -- 2024
EXTRACT(MONTH FROM DATE '2024-03-15') AS the_month, -- 3
EXTRACT(DAY FROM DATE '2024-03-15') AS the_day, -- 15
EXTRACT(DOW FROM DATE '2024-03-15') AS day_of_week; -- 5 (Friday)
Day of week values: 0 = Sunday, 1 = Monday, ..., 6 = Saturday.
Applied to real data:
-- Count employees hired per year
SELECT
EXTRACT(YEAR FROM hire_date) AS hire_year,
COUNT(*) AS employees_hired
FROM employees
GROUP BY hire_year
ORDER BY hire_year;
-- Find orders placed in a specific month
SELECT customer_name, order_date, total_price
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 6;
DATE_PART Function
DATE_PART is an alternative to EXTRACT with slightly different syntax:
SELECT DATE_PART('year', hire_date) AS hire_year
FROM employees;
Both EXTRACT and DATE_PART return the same results.
Formatting Dates
The TO_CHAR function formats dates as strings:
SELECT
TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS iso_format,
TO_CHAR(CURRENT_DATE, 'DD Mon YYYY') AS readable,
TO_CHAR(CURRENT_DATE, 'Day, Month DD, YYYY') AS full_format;
Common format patterns:
| Pattern | Meaning | Example |
|---|---|---|
YYYY |
4-digit year | 2024 |
MM |
Month number (01-12) | 03 |
Mon |
Abbreviated month | Mar |
Month |
Full month name | March |
DD |
Day of month (01-31) | 15 |
Day |
Full day name | Friday |
HH24 |
Hour (00-23) | 14 |
MI |
Minute (00-59) | 30 |
SS |
Second (00-59) | 00 |
Truncating Dates
DATE_TRUNC rounds a timestamp down to a specified precision:
SELECT
DATE_TRUNC('year', TIMESTAMP '2024-07-15 14:30:00') AS year_start,
-- Returns: 2024-01-01 00:00:00
DATE_TRUNC('month', TIMESTAMP '2024-07-15 14:30:00') AS month_start,
-- Returns: 2024-07-01 00:00:00
DATE_TRUNC('day', TIMESTAMP '2024-07-15 14:30:00') AS day_start;
-- Returns: 2024-07-15 00:00:00
This is useful for grouping by time periods:
-- Monthly order totals
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(total_price) AS revenue
FROM orders
GROUP BY month
ORDER BY month;
BOOLEAN Type
PostgreSQL has a native BOOLEAN type that stores TRUE, FALSE, or NULL:
SELECT
TRUE AS yes,
FALSE AS no,
TRUE AND FALSE AS and_result, -- FALSE
TRUE OR FALSE AS or_result, -- TRUE
NOT TRUE AS not_result; -- FALSE
Accepted Boolean Values
PostgreSQL accepts several representations:
| True Values | False Values |
|---|---|
TRUE, 't', 'true', 'yes', 'on', '1' |
FALSE, 'f', 'false', 'no', 'off', '0' |
Using Booleans in Queries
You do not need to compare a boolean column to TRUE -- just reference the column directly:
-- These are equivalent:
SELECT name FROM employees WHERE is_active = TRUE;
SELECT name FROM employees WHERE is_active;
-- For false:
SELECT name FROM employees WHERE is_active = FALSE;
SELECT name FROM employees WHERE NOT is_active;
Counting with Booleans
A common pattern is to count true/false values:
SELECT
COUNT(*) FILTER (WHERE is_active) AS active_count,
COUNT(*) FILTER (WHERE NOT is_active) AS inactive_count
FROM employees;
Or without the FILTER clause:
SELECT
SUM(CASE WHEN is_active THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN NOT is_active THEN 1 ELSE 0 END) AS inactive_count
FROM employees;
NULL: The Absence of Value
NULL is not a value -- it represents the absence of a value, meaning "unknown" or "not applicable." Understanding NULL behavior is critical because it often surprises beginners.
NULL Comparisons
SELECT
NULL = NULL AS eq_test, -- NULL (not TRUE!)
NULL <> NULL AS neq_test, -- NULL (not TRUE!)
NULL > 0 AS gt_test, -- NULL
NULL + 10 AS add_test; -- NULL
Any operation involving NULL produces NULL. This is called three-valued logic: the answer is not TRUE or FALSE, but UNKNOWN.
Testing for NULL
Always use IS NULL and IS NOT NULL, never = NULL:
-- Correct:
SELECT name FROM employees WHERE manager_id IS NULL;
SELECT name FROM employees WHERE manager_id IS NOT NULL;
-- Wrong (this will not work as expected):
SELECT name FROM employees WHERE manager_id = NULL;
-- Returns no rows, because NULL = NULL is NULL (which is not TRUE)
COALESCE: Replacing NULL
COALESCE returns the first non-NULL value from a list of arguments:
SELECT
name,
COALESCE(manager_id::TEXT, 'No Manager') AS manager_info
FROM employees;
This replaces NULL manager IDs with the text "No Manager".
You can provide multiple fallback values:
SELECT COALESCE(NULL, NULL, 'fallback') AS result;
-- Returns: 'fallback'
NULLIF: Creating NULL
NULLIF returns NULL if two values are equal, otherwise returns the first value:
SELECT NULLIF(10, 10) AS result; -- NULL
SELECT NULLIF(10, 20) AS result; -- 10
A practical use is avoiding division by zero:
SELECT 100 / NULLIF(0, 0) AS safe_division;
-- Returns NULL instead of an error
Try It Yourself
- Find all employees hired in the last 2 years, showing their name and hire date.
SELECT name, hire_date
FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '2 years'
ORDER BY hire_date DESC;
- Show each employee's name, hire_date, and the number of complete years they have worked (as a whole number).
SELECT
name,
hire_date,
EXTRACT(YEAR FROM AGE(hire_date))::INTEGER AS years_employed
FROM employees
ORDER BY years_employed DESC;
- Count the number of active and inactive employees.
SELECT
is_active,
COUNT(*) AS employee_count
FROM employees
GROUP BY is_active;
- List all employees, replacing NULL manager_id values with the text "Top Level".
SELECT
name,
department,
COALESCE(manager_id::TEXT, 'Top Level') AS manager
FROM employees
ORDER BY name;