beginner~4h

String & Date/Time Functions

Real columns are rarely used as-is — names get combined and reformatted, text gets searched and measured, and dates get truncated, bucketed by month, and compared using safe ranges. This topic covers the string and date/time function families every reporting query eventually needs.

Filtering and joining get you the right rows; string and date/time functions get you the right shape of data inside each row — a full name from two columns, a case-insensitive comparison, a calendar month bucket for a trend report, or a safe date range that doesn't silently miss the last day of the year. Every monthly-revenue chart, cohort report, and "customers signed up this year" query in this course leans on this function family, so gaps here show up as subtly wrong reports rather than obvious query errors.

Raw columns rarely match what a report needs to display or group by: first_name and last_name are two columns but the UI wants one; order_date is a precise timestamp but a monthly trend chart needs it bucketed to the first of the month; comparing a date range naively with BETWEEN '2025-01-01' AND '2025-12-31' looks right but silently drops any row timestamped even one second into January 1st of the next year with a non-midnight time component. Without a working grasp of string and date functions, these are the kind of bugs that pass code review and only get caught when a stakeholder notices a report total looks slightly off.

CONCAT(a, b, ...) — joins multiple text values into one string; || is PostgreSQL's operator equivalent. UPPER() / LOWER() — case conversion, most often used for case-insensitive comparisons or display formatting. LENGTH() — character count of a string. SUBSTRING(str FROM start FOR length) — extracts a slice of a string by position. TRIM() — removes leading/trailing whitespace (or a specified character). REPLACE(str, from, to) — swaps every occurrence of one substring for another. DATE_TRUNC('month'/'day'/'year', date_col) — rounds a timestamp down to the start of the given unit, the standard way to bucket dates for a monthly/daily/yearly report. EXTRACT(field FROM date_col) — pulls a single numeric component (YEAR, MONTH, DAY, DOW) out of a date. INTERVAL — a duration literal (INTERVAL '30 days', INTERVAL '1 month') usable in date arithmetic (date_col + INTERVAL '30 days'). Half-open date range — a range expressed as col >= start AND col < end (exclusive upper bound) rather than BETWEEN start AND end, which safely handles any time-of-day component on the upper-bound date.

String functions

String functions mostly fall into two buckets: combining/reshaping text (CONCAT/||, SUBSTRING, TRIM, REPLACE) and measuring/searching text (LENGTH, LIKE, POSITION). They're almost always applied in the SELECT list for display purposes, or in a WHERE clause for pattern matching — but a string function wrapped around an indexed column in WHERE (WHERE UPPER(email) = 'X@Y.COM') prevents a plain B-tree index on that column from being used, unless a matching functional index was created specifically for that expression.

Date/time truncation and extraction

DATE_TRUNC and EXTRACT solve two different problems that look similar. DATE_TRUNC('month', order_date) keeps the result as a real date (the 1st of that month), which is what you want for GROUP BY in a trend report, since it sorts and compares naturally. EXTRACT(YEAR FROM hire_date) pulls out just a number (e.g. 2024), useful when you want to group by year alone regardless of month — but it throws away the date-ness of the value, so you can't directly compare it against another date.

Date range filtering — BETWEEN vs half-open ranges

WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31' looks correct and usually is correct for a plain DATE column with no time component. But the moment the column is a TIMESTAMP, '2025-12-31' is interpreted as midnight at the start of that day — so any order placed on December 31st after midnight is silently excluded. The safe, standard pattern is a half-open range: order_date >= '2025-01-01' AND order_date < '2026-01-01', which is correct regardless of whether the column carries a time component.

Date arithmetic with INTERVAL

DATE '2026-01-01' - INTERVAL '180 days' produces a real date you can compare a column against directly — this is how "customers inactive for more than 180 days" or "orders placed in the last year" checks are usually expressed, computing the cutoff once rather than repeating date math in every row's condition.

💻 Code example

-- Bucket revenue by month (DATE_TRUNC keeps it comparable/sortable)
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Safe half-open date range instead of BETWEEN
SELECT * FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

-- Interval arithmetic for a rolling cutoff
SELECT customer_id FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING MAX(o.order_date) < DATE '2026-01-01' - INTERVAL '180 days';

PostgreSQL stores DATE and TIMESTAMP as integer counts of days or microseconds internally, which is why date arithmetic (date_col + INTERVAL '30 days') is a cheap, exact integer operation rather than fragile string manipulation. DATE_TRUNC works by zeroing out every field below the requested precision (truncating to 'month' zeroes the day, hour, minute, second) and is timezone-aware for timestamptz columns — always be explicit about which timezone a truncation should apply in for timestamp-with-timezone data, since "the start of the month" can differ by timezone. String functions like UPPER, LOWER, and LENGTH operate byte- or character-aware depending on the column's collation; PostgreSQL's default LENGTH() counts characters, not bytes, which matters for multi-byte UTF-8 text.

Step 1: Decide whether you need the value itself reshaped (CONCAT for display, DATE_TRUNC for grouping) or just a derived fact about the value (LENGTH, EXTRACT).

Step 2: For any date range filter, write it as a half-open range (>= start AND < end_exclusive) rather than BETWEEN, especially if the column could ever be a timestamp.

Step 3: For a monthly/yearly trend, GROUP BY the exact same DATE_TRUNC expression used in the SELECT list — repeating the expression (or aliasing and grouping by the alias, where the engine supports it) keeps the grouping and the displayed bucket in sync.

Step 4: For a rolling cutoff ("more than N days ago"), compute it once as reference_date - INTERVAL 'N days' rather than repeating NOW() - INTERVAL ... inside a HAVING or WHERE that runs per row.

Step 5: Avoid wrapping an indexed column in a string function inside WHERE unless you've also created a matching functional index — otherwise expect a full table scan.

Prefer half-open date ranges (>= start AND < end) over BETWEEN for anything that could ever be a timestamp, not just a plain date.
Use DATE_TRUNC (not EXTRACT) when the bucketed value needs to be sorted, compared, or charted as a real date — EXTRACT is for when you specifically want a bare number, like grouping purely by year regardless of month.
Compute a relative cutoff (DATE '2026-01-01' - INTERVAL '180 days') once, not by reimplementing the same date math differently in multiple queries.
Use || or CONCAT consistently across a codebase rather than mixing both styles — they behave slightly differently on NULL inputs (|| with a NULL operand returns NULL; CONCAT treats NULL as an empty string), which is a subtle source of confusion if both are used interchangeably.
When comparing user-entered text, normalize case explicitly (LOWER(email) = LOWER(:input)) rather than assuming the stored data is already consistently cased.

Using BETWEEN '2025-01-01' AND '2025-12-31' against a TIMESTAMP column and silently dropping rows timestamped after midnight on December 31st.
Confusing ||'s NULL behavior with CONCAT's — first_name || ' ' || last_name returns NULL if either name is NULL, while CONCAT(first_name, ' ', last_name) would just skip the NULL piece; picking the wrong one produces surprising blank names in a report.
Grouping by EXTRACT(MONTH FROM order_date) alone across multiple years, which silently merges January 2024 and January 2025 into a single 'month 1' bucket — DATE_TRUNC('month', order_date) avoids this because it keeps the year as part of the bucket.
Wrapping an indexed column in a function inside WHERE (WHERE UPPER(email) = 'X') and being confused why a query that used to be fast suddenly needs a full table scan.
Off-by-one errors when computing a cutoff date by hand instead of using INTERVAL arithmetic (manually subtracting "180 days" from a date by changing the month number, which breaks around month/year boundaries).

A plain WHERE date_col >= X AND date_col < Y can use a standard B-tree index on date_col directly; wrapping the column in DATE_TRUNC or EXTRACT inside WHERE generally cannot, unless a matching expression index was created for exactly that expression.
For text search beyond simple LIKE 'prefix%' patterns, a plain B-tree index still helps prefix matches, but full substring or fuzzy search (LIKE '%term%') needs a trigram (pg_trgm) or full-text index — a B-tree can't accelerate a leading wildcard.
DATE_TRUNC-based GROUP BY over a large date range benefits from an index on the raw date column (to support the WHERE range filter) even though the truncation itself happens after rows are read.

String functions are the classic vector for SQL injection when a query is built by concatenating raw, un-parameterized user input directly into the SQL text — always pass user-supplied strings as bound parameters and let the database driver escape them, never build a WHERE or CONCAT expression by string-formatting user input into the query itself. LIKE patterns built from user input also deserve care: an unescaped % or _ typed by a user changes the meaning of the pattern, which is a correctness issue more than a security one, but worth handling deliberately (escaping or validating the input) rather than passing it through unmodified.

If a monthly trend report suddenly shows fewer months than expected, or January of different years appears merged into one row, check whether the GROUP BY uses DATE_TRUNC('month', ...) (year-aware) or EXTRACT(MONTH FROM ...) (not year-aware) — this is one of the most common silent bugs in date-bucketed reporting. If a previously-fast query filtering on a date column suddenly shows a sequential/full scan in EXPLAIN, check whether a function got added around the column in WHERE (even something as small as a cast) that broke index usage.

Standardize on half-open date ranges across the codebase (a shared query-building helper, if the application layer supports one) so no individual query author has to remember the BETWEEN pitfall each time.
Store and compare dates/timestamps in UTC internally, converting to a display timezone only at the presentation layer — timezone-naive date arithmetic mixed with timezone-aware columns is a recurring source of off-by-one-day bugs around midnight.
For frequently-filtered text search beyond simple equality or prefix match, provision pg_trgm and a trigram GIN index up front rather than discovering the need for it after a LIKE '%x%' query starts timing out in production.

Write a query that returns each employee's full name as a single column, safely handling the case where last_name might be NULL.
Write a monthly revenue report for the last 12 months using DATE_TRUNC, and a second version using EXTRACT(MONTH FROM ...) grouped across multiple years — compare the two outputs and explain why they differ.
Rewrite a BETWEEN '2025-01-01' AND '2025-12-31' filter as a half-open range, and construct a test row that would be included by one version and excluded by the other.
Write a query that finds every customer inactive for more than 180 days relative to a fixed reference date, using INTERVAL arithmetic rather than hand-computed date literals.

CONCAT/|| combine text; note their different behavior with NULL inputs.
LENGTH, SUBSTRING, TRIM, REPLACE reshape and measure text without changing stored data.
DATE_TRUNC rounds a date down to a unit (month/day/year) and keeps it comparable/sortable as a real date — prefer it for trend-report grouping.
EXTRACT pulls a single numeric field out of a date but discards date-ness — grouping by EXTRACT(MONTH ...) alone silently merges different years.
Prefer half-open date ranges (>= start AND < end) over BETWEEN for anything that might carry a time component.
INTERVAL arithmetic computes safe, exact cutoff dates (reference_date - INTERVAL 'N days') instead of fragile hand-rolled date math.
A function wrapped around an indexed column in WHERE generally blocks normal index usage unless a matching expression index exists.

Want a visual for this concept?

Generate a diagram tailored to “String & Date/Time Functions” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Indexes — Clustered, Non-Clustered, Composite, Covering →← Back to all SQL chapters