Date/Time, String, Regular Expression, Numeric, and Conditional Functions

Learning outcomes

SQL functions become difficult when learned as a dictionary. In production they are tools for specific problems: normalize a timestamp for reporting, compare multilingual text consistently, validate a pattern, round exact money, or choose a fallback value. This lesson groups functions by those problems and makes hidden session/collation/type assumptions visible.

Temporal problem: “show the same instant in the operator’s zone”

NOW()/CURRENT_TIMESTAMP are evaluated in the session time zone. A DATETIME column, however, stores calendar date/time fields without automatic timezone conversion semantics. If your application stores UTC in DATETIME by convention, that convention lives outside the type and must be applied explicitly.

USE servicehub_analytics_lab;SELECT @@SESSION.time_zone AS session_time_zone,       NOW() AS session_now,       UTC_TIMESTAMP() AS utc_now;SET @old_time_zone = @@SESSION.time_zone;SET time_zone = '+00:00';SELECT @@SESSION.time_zone, NOW(), UTC_TIMESTAMP();SET time_zone = @old_time_zone;

Multiple references to NOW() within one statement use the statement-start time. Restore the session setting when a lab changes it; connection pools can otherwise leak altered session state into later application requests.

SELECT work_order_id,       opened_at AS opened_utc_by_contract,       CONVERT_TZ(opened_at,'+00:00','+03:30') AS opened_at_plus_0330FROM work_ordersWHERE work_order_id IN (1001,1002)ORDER BY work_order_id;

Named zones such as America/Toronto depend on MySQL time-zone tables being populated. Numeric offsets do not model daylight-saving history, so production systems that need civil-time history should load/maintain named-zone data and test transitions.

Parsing malformed dates: make warnings part of the contract

STR_TO_DATE() parses a string according to a format. Invalid input can return NULL and produce a warning. Do not quietly convert a bad feed into a plausible date.

SELECT STR_TO_DATE('2026-08-16','%Y-%m-%d') AS valid_date;SELECT STR_TO_DATE('2026-99-99','%Y-%m-%d') AS invalid_date;SHOW WARNINGS;

In an ingestion pipeline, capture parse failures separately and reject or quarantine bad records. SQL warnings are diagnostic evidence, not decoration.

String problem: “does equality ignore case or accents?”

The answer comes from collation. In names such as utf8mb4_0900_ai_ci, ai means accent-insensitive and ci means case-insensitive. An as_cs collation is accent-sensitive and case-sensitive. Choose based on the domain instead of forcing every comparison through LOWER().

SELECT  _utf8mb4'Résumé' COLLATE utf8mb4_0900_ai_ci    = _utf8mb4'resume' COLLATE utf8mb4_0900_ai_ci AS ai_ci_equal,  _utf8mb4'Résumé' COLLATE utf8mb4_0900_as_cs    = _utf8mb4'resume' COLLATE utf8mb4_0900_as_cs AS as_cs_equal;-- Expected conceptually: 1 under ai_ci, 0 under as_cs.SELECT COLLATION_NAME, PAD_ATTRIBUTEFROM INFORMATION_SCHEMA.COLLATIONSWHERE COLLATION_NAME IN ('utf8mb4_0900_ai_ci','utf8mb4_0900_as_cs');

Collation affects equality, ordering, grouping, uniqueness, and indexes. It is therefore schema behavior, not merely UI formatting.

Regular expressions: MySQL uses ICU semantics

MySQL 8.4 regular-expression support is based on International Components for Unicode (ICU), so matching is Unicode-aware and multibyte safe. Use functions such as REGEXP_LIKE() when a pattern is truly required; for simple prefixes/equality, simpler predicates may be clearer and more index-friendly.

SELECT work_order_id, summaryFROM work_ordersWHERE REGEXP_LIKE(summary, '(sensor|controller)', 'i')ORDER BY work_order_id;

-- Wrong: unclosed character class.SELECT REGEXP_LIKE('sensor', '[', 'i');-- Expected: regular-expression syntax error.-- Repair: a valid class/pattern.SELECT REGEXP_LIKE('sensor', '^[sS]', 'c') AS starts_with_s_or_S;

If patterns are user-supplied, validate/limit them and use MySQL’s regex resource controls where appropriate. A complex regular expression can consume significant CPU even if it is logically correct.

Numeric problem: exact money is not approximate telemetry

The parts_cost column is DECIMAL(10,2), an exact fixed-point type suitable for money-like values. FLOAT/DOUBLE are approximate. MySQL’s rounding behavior can differ for exact and approximate inputs, especially at halfway values.

SELECT ROUND(CAST('2.50' AS DECIMAL(10,2)),0) AS exact_positive,       ROUND(CAST('-2.50' AS DECIMAL(10,2)),0) AS exact_negative,       ROUND(2.5E0,0) AS approximate_result;SELECT SUM(parts_cost) AS exact_parts_total,       ROUND(AVG(parts_cost),2) AS average_parts_costFROM work_orders;

Exact halfway values use MySQL’s exact-value rounding rules; approximate values can depend on the platform C library. Do not design financial reconciliation around approximate literals or DOUBLE merely because they display familiar decimal digits.

Conditional problem: preserve meaning while choosing output

CASE is standard SQL and usually the clearest general conditional expression. MySQL also provides IF() and IFNULL(). COALESCE() returns the first non-NULL value. NULLIF(a,b) returns NULL when the values compare equal, which is useful for avoiding division by zero.

SELECT work_order_id,       CASE priority         WHEN 1 THEN 'urgent'         WHEN 2 THEN 'normal'         ELSE 'low'       END AS priority_label,       COALESCE(closed_at, opened_at) AS last_known_boundary,       IF(status='open','needs attention','completed') AS queue_label,       labor_minutes / NULLIF(parts_cost,0) AS minutes_per_cost_unitFROM work_ordersORDER BY work_order_id;

The ratio intentionally returns NULL when parts cost is zero rather than raising a divide-by-zero problem or inventing infinity. That NULL means “ratio not defined under this formula.” Preserve it unless the reporting contract defines another representation.

Common wrong approach: wrap indexed columns in functions without evidence

A function can change sargability. For example, WHERE DATE(opened_at)='2026-03-03' is readable but may make a plain index on opened_at less directly usable than a half-open range. This lesson does not promise a specific plan on the tiny lab; it teaches a safer predicate shape.

-- Tempting:SELECT work_order_id FROM work_ordersWHERE DATE(opened_at) = '2026-03-03';-- Range form with the same calendar-day meaning:SELECT work_order_id FROM work_ordersWHERE opened_at >= '2026-03-03 00:00:00'  AND opened_at <  '2026-03-04 00:00:00'ORDER BY opened_at, work_order_id;

Later indexing chapters will measure these access paths properly. For now, learn to separate data transformation in the select list from search predicates that should expose stored values to indexes.

Hands-on lab and verification

1. Record and restore @@SESSION.time_zone; compare NOW() and UTC_TIMESTAMP().
2. Convert two UTC-by-contract DATETIME values to +03:30 with CONVERT_TZ().
3. Parse one valid and one invalid date string and inspect SHOW WARNINGS.
4. Compare Résumé and resume under ai_ci and as_cs collations.
5. Run a Unicode-aware REGEXP_LIKE search and deliberately trigger one regex syntax error.
6. Compare exact DECIMAL rounding with an approximate scientific-notation value, then build CASE/COALESCE/NULLIF output columns.

Production judgment and next bridge

Function choice is part of the data contract. Time zone, collation, numeric type, SQL mode, and NULL conventions should be explicit in applications and tests. Avoid function-heavy predicates that hide searchable columns without first checking plans. Avoid regex where a simpler relational predicate expresses the rule more clearly.

Next: Lesson 5 combines joins, grouping, windows, date bucketing, and conditional aggregation into reusable operational reports and views, then defines the boundary where a dedicated BI/analytics platform becomes the better tool.

Authoritative references

• MySQL 8.4 Reference Manual — Date and Time Functions
• MySQL 8.4 Reference Manual — Character Sets, Collations, Unicode
• MySQL 8.4 Reference Manual — Unicode Character Sets and Collations
• MySQL 8.4 Reference Manual — Regular Expressions
• MySQL 8.4 Reference Manual — Precision Math / Rounding Behavior
• MySQL 8.4 Reference Manual — Flow Control Functions