Part 2 — Querying › Chapter 17

Strings, dates, times and time zones

Date arithmetic that survives DST, why storing local time is a bug, and collation as a correctness problem.

From the advert

“Ottima conoscenza del linguaggio SQL” — and specifically the parts of it that are not standard at all. Dates are where dialects diverge most and where bugs hide longest.

Never wrap the column

The bug survives review because BETWEEN reads like English, and survives testing whenever the test data happens to be timestamped midnight.

This is chapter 07 again, and it is worth repeating because date filtering is where it happens most:

Four ways to ask for one day. Three of them read the whole table.

WHERE YEAR(placed_at) = 2026                        -- scan: a function on the indexed column
WHERE CAST(placed_at AS DATE) = '2026-03-14'        -- scan: CAST is a function too
WHERE DATE_TRUNC('day', placed_at) = '2026-03-14'   -- scan: so is truncation

WHERE placed_at >= DATE '2026-03-14'                -- seek: a plain lower bound…
  AND placed_at <  DATE '2026-03-15'                -- …and an EXCLUSIVE upper bound

Why the last one is the only sargable spelling

The index stores placed_at. It does not store YEAR(placed_at), CAST(placed_at AS DATE) or the truncation — each of those is a different value the engine has to compute per row before it can compare.
The half-open range works at any precision. <= '2026-03-14 23:59:59' loses rows in the last second of the day the moment the column gains milliseconds — and columns gain precision in migrations.
Some engines can use an expression index to rescue the first three (CREATE INDEX … ON orders (CAST(placed_at AS DATE))). That is a deliberate decision to pay for a second index, not an excuse to write the predicate this way by accident.

✔ The half-open habit

>= start AND < next_start is correct for a date, a DATETIME, a DATETIME2(7) and whatever precision somebody changes the column to in 2029. BETWEEN is correct only for pure dates and loses the last day for everything else. Adopt half-open once and you never think about it again.

Time zones: the three-layer rule

🧒 Simple

“Half past two on the last Sunday in October” happens twice in Italy — the clocks go back and that hour is repeated. So if you only wrote down “02:30”, you cannot tell which of the two it was, and you never will be able to. Writing down the real moment instead — the one everyone in the world agrees on — costs nothing and can never be ambiguous.

🎓 Professional

Store instants in UTC, or in a zone-aware type (timestamptz, DATETIMEOFFSET). Every event, log line, audit row.
Store the zone separately when the local zone is part of the meaning — a meeting at 09:00 Europe/Rome, which stays 09:00 across a DST change. Store the local time and the IANA zone name, not the offset: offsets change, zones do not.
Convert at the edge, when displaying. Never in the middle of a query you also want to be indexable.

⚠ Trap — “+02:00” is not a time zone

An offset is a snapshot; a zone is a rule with history. Rome is +01:00 in winter and +02:00 in summer, and the rules have changed before and will again — Italy has debated abolishing DST twice. Storing the offset makes future dates wrong the moment the rule changes. Store Europe/Rome.

Converting a stored UTC instant into local time, in the two dialects you are most likely to meet.

-- PostgreSQL: the conversion is a first-class operation.
SELECT placed_at AT TIME ZONE 'Europe/Rome'   -- IANA name: knows about daylight saving…
       AS local_time                          -- …so it is right in both halves of the year
FROM orders;                                  -- placed_at is stored as UTC

-- SQL Server 2016+: two conversions, and you must state the starting point.
SELECT placed_at AT TIME ZONE 'UTC'                        -- 1. "what is stored IS UTC"…
                 AT TIME ZONE 'W. Europe Standard Time'    -- 2. "…now show it in Rome"
FROM orders;                                               -- Windows zone names, not IANA

The rule, and the trap in zone names

Store UTC, convert at the edges. A timestamp column holds an instant; a timezone is a presentation concern. The moment two services disagree about what the stored value means, every report is wrong by an hour for half the year.
Store the zone as an IANA name (Europe/Rome), never as a fixed offset (+02:00). An offset is a fact about one instant; a name is a fact about a place, and it survives the next change to the daylight-saving rules.
SQL Server needs the first AT TIME ZONE 'UTC' because a plain DATETIME2 carries no zone: without it, the server assumes the value is already in the target zone and shifts it twice.
Windows and IANA use different zone names. There is no way round it but a lookup table.

The date functions, four ways

Task	PostgreSQL	SQL Server	MySQL	Oracle
Now	`now()`	`SYSDATETIME()`	`NOW()`	`SYSTIMESTAMP`
Add 7 days	`d + INTERVAL '7 day'`	`DATEADD(day,7,d)`	`DATE_ADD(d,INTERVAL 7 DAY)`	`d + 7`
Difference	`a - b` (interval)	`DATEDIFF(day,b,a)`	`DATEDIFF(a,b)`	`a - b` (days)
Start of month	`date_trunc('month',d)`	`DATEFROMPARTS(YEAR(d),MONTH(d),1)`	`DATE_FORMAT(d,'%Y-%m-01')`	`TRUNC(d,'MM')`
Format	`to_char(d,'YYYY-MM')`	`FORMAT(d,'yyyy-MM')`	`DATE_FORMAT(d,'%Y-%m')`	`TO_CHAR(d,'YYYY-MM')`

⚠ Trap — DATEDIFF counts boundaries, not elapsed time

DATEDIFF(day, '2026-03-14 23:59', '2026-03-15 00:01') is 1, even though two minutes passed. It counts how many day boundaries were crossed. That makes “orders older than 30 days” subtly wrong at the edges. If you mean elapsed time, subtract and compare to an interval.

Strings: the functions and the two real traps

The string functions worth knowing by heart, with the dialect spellings beside them.

-- Portable-ish. The idea is universal; the spelling is not.
UPPER(s) LOWER(s) LENGTH(s) TRIM(s) REPLACE(s, a, b)  -- LEN(s) in T-SQL, and it ignores trailing spaces
SUBSTRING(s FROM 2 FOR 5)          -- standard; SUBSTR(s, 2, 5) or SUBSTRING(s, 2, 5) elsewhere
s || t                             -- standard concatenation; T-SQL uses +, MySQL needs CONCAT()
POSITION('x' IN s)                 -- CHARINDEX('x', s) in T-SQL, INSTR(s, 'x') in Oracle/MySQL

How to read this, and the two things that will bite you

A reference list, not a runnable statement: each line is one operation with its portable spelling first.
SQL strings are 1-indexed. SUBSTRING(s, 1, 5) is the first five characters — unlike every application language you will call it from.
LENGTH counts characters, not bytes: LENGTH('è') is 1 while its UTF-8 encoding is 2 bytes. SQL Server's LEN additionally ignores trailing spaces, and DATALENGTH is the byte count.
|| propagates NULL — one NULL part erases the whole string — while CONCAT() treats NULL as empty by definition. Choose deliberately.

⚠ Trap 1 — LEN vs DATALENGTH

T-SQL's LEN() ignores trailing spaces; DATALENGTH() returns bytes (so double for NVARCHAR). Validating an IBAN or a VAT number with LEN() silently accepts trailing whitespace. Standard CHAR_LENGTH() does not have this behaviour.

⚠ Trap 2 — collation decides equality

Under a case-insensitive collation 'Rossi' = 'rossi' is true, so a UNIQUE constraint rejects the second one and a GROUP BY merges them. Under a case-sensitive one, both are separate. This is not a display setting — it changes which rows come back. And joining columns with different collations forces an implicit conversion that kills the index (chapter 07). Decide once, at database creation.

Normalising a messy imported column: the pattern you will use in every data-loading job.

SELECT                                                                 -- the cleaned columns
  NULLIF(TRIM(LOWER(email)), '')                       AS email_norm,  -- lower, trim, blank→NULL
  REPLACE(REPLACE(TRIM(vat_number), ' ', ''), '.', '') AS vat_norm     -- strip spaces, then dots
FROM staging_customers;                                                -- the raw import table
-- Then deduplicate on email_norm, never on the raw email. See chapter 15.

Reading it inside out

Function calls nest, so the innermost runs first: LOWER, then TRIM, then NULLIF. Written outside in, it reads backwards — which is why this idiom is worth recognising as a whole rather than parsing each time.
NULLIF(x, '') turns the empty string into NULL. That matters because '' and NULL are different values everywhere except Oracle, and an import that produces both means two spellings of “missing” in one column.
Nested REPLACE is the portable way to strip several characters. It is also O(n) passes over the string — fine in a load job, not something to put in a WHERE clause on a hot query.
Normalise on the way in and store the normalised form. Normalising on the way out means every query pays for it and no index can help.

The calendar table

🎓 A table nobody teaches and every reporting system needs

A dim_date table with one row per day and columns for month, quarter, ISO week, day name, whether it is a working day and whether it is an Italian public holiday. It turns “sales per working day” and “fill in the days with no orders” from awkward date arithmetic into an ordinary join, it is trivially indexable, and it is the only sane place to encode the fact that Ferragosto exists. Every mature warehouse has one; suggesting it in an interview signals reporting experience.

Never wrap a date column in a function to filter it. Half-open ranges instead.
BETWEEN on a timestamp loses the last day.
Store instants in UTC or a zone-aware type; convert at the edge.
Store a zone name (Europe/Rome), never an offset. Offsets are snapshots of a rule that changes.
Local time is ambiguous for one hour every autumn, permanently and unrecoverably.
DATEDIFF counts boundaries crossed, not elapsed time.
T-SQL LEN() ignores trailing spaces; use DATALENGTH or CHAR_LENGTH when it matters.
Collation decides equality, grouping and uniqueness — not just sort order.
Build a calendar table. Working days and holidays are data, not arithmetic.

Interview questions this chapter answers

How would you store a timestamp for a multi-country application?

Instants in UTC or a zone-aware column, and if the local wall-clock time is part of the meaning — a scheduled appointment — the local time plus the IANA zone name, not the offset. Conversion happens on display. Local-only storage is ambiguous for one hour each autumn and cannot be repaired later.

Get all orders from March 2026.

WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'. Half-open, so it is correct at any precision and can seek an index — unlike MONTH(placed_at) = 3, which scans.

Two queries compare the same strings and give different results. Why?

Different collations — on the columns, the database, or introduced by a cast. A case- or accent-insensitive collation makes 'Perù' = 'Peru' true. It also affects GROUP BY, UNIQUE and index seekability across a join.