String, Numeric & Date-Time Functions — SQL Course

What you'll learn

Clean, calculate, and summarize values with portable core functions and PostgreSQL date-time capabilities. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply String functions to a realistic data question
Apply Numeric functions to a realistic data question
Apply Date arithmetic to a realistic data question
Apply Intervals to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Immutable function	A function whose result depends only on inputs	It is safest for expression indexes and generated values
Interval	A duration value used in date arithmetic	Use date/time types rather than encoded text or seconds
Time zone	Rules mapping an instant to local civil time	Store instants as timestamptz and convert at boundaries

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the value-transformation query question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Build a monthly sales summary

date_trunc creates a consistent bucket while numeric rounding is delayed until presentation.

monthly_sales.sql

SELECT
  date_trunc('month', paid_at AT TIME ZONE 'UTC')::date AS month,
  count(*) AS payments,
  round(sum(amount), 2) AS revenue,
  round(avg(amount), 2) AS average_payment
FROM sales.payments
WHERE paid_at >= now() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Formatting timestamps as text too early breaks chronological sorting, arithmetic, indexing, and time-zone reasoning.

Independent workshop

Build a review-ready value-transformation query lab against the course commerce dataset.

Your finished workshop must include:

String functions
Numeric functions
Date arithmetic
Intervals
Date truncation
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Immutable function: It is safest for expression indexes and generated values
Interval: Use date/time types rather than encoded text or seconds
Time zone: Store instants as timestamptz and convert at boundaries

Quick check

1. Which rule best applies to Immutable function?

2. Which rule best applies to Interval?

3. Which rule best applies to Time zone?

Next: CASE, COALESCE, NULLIF & Type Conversion