Numbers and dates

Part of the free LogicWiz SQL & Databases course, Module 3: Functions and expressions. The queries and practice run in the app on real Postgres.

Aarav's dashboard says the cancellation rate is 0%. Two out of 24 orders were cancelled. The maths in his query is right. So why is the answer 0?

You'll learn to

  • Do arithmetic in a query and avoid the integer division trap
  • Round and change types with ROUND, CAST and ::
  • Take dates apart with EXTRACT, DATE_TRUNC and TO_CHAR
  • Add intervals and find the gap between two dates

Learn

Maths on every row

SELECT name,
  price,
  price * 2 AS two_plates,
  ROUND(price * 1.05, 2) AS with_gst
FROM menu_items
WHERE restaurant_id = 2;

+ - * / work on columns just like on a calculator, row by row. ROUND(x, 2) keeps 2 digits after the decimal point. % gives the remainder: 7 % 2 is 1.

The integer division trap

SELECT 7 / 2 AS int_div,
  7 % 2 AS remainder,
  7 / 2.0 AS real_div,
  7::numeric / 2 AS cast_first,
  ROUND(7::numeric / 2, 2) AS rounded;

When both sides are whole numbers (integers), / throws away the decimal part: 7 / 2 is 3, and 2 / 24 is 0. Make one side a decimal first: write 2.0, multiply by 100.0, or cast with ::numeric.

ROUND(2 / 24 * 100, 1) is 0.0, not 8.3. The division happens first, between two integers, and gives 0. Rounding later cannot bring the lost part back. Write ROUND(2 * 100.0 / 24, 1) instead.

Changing a type: CAST and ::

Every value has a type: INT (whole number), NUMERIC (exact decimal, used for money), TEXT, DATE, TIMESTAMP (date plus time).

To change it, write CAST(x AS NUMERIC) (standard SQL) or x::numeric (Postgres short form). Both do the same thing.

  • '2025-07-01'::date turns text into a date
  • ordered_at::date drops the time from a timestamp
  • '42'::int turns text into a number

ROUND(x, 2) needs a NUMERIC. On a double precision value it gives an error, so cast first.

Taking a timestamp apart

SELECT order_id,
  ordered_at,
  ordered_at::date AS day,
  EXTRACT(HOUR FROM ordered_at) AS hour,
  EXTRACT(DOW FROM ordered_at) AS weekday_no,
  TO_CHAR(ordered_at, 'Dy DD Mon, HH24:MI') AS pretty,
  DATE_TRUNC('month', ordered_at) AS month_start
FROM orders
WHERE order_id <= 1005;

EXTRACT(part FROM ts) pulls out one number: YEAR, MONTH, DAY, HOUR, DOW (day of week, 0 = Sunday to 6 = Saturday). DATE_TRUNC('month', ts) rounds down to the start of the month, which is how you group by month. TO_CHAR formats a date as text for display.

Adding time and measuring gaps

SELECT name,
  signup_date,
  signup_date + INTERVAL '30 days' AS trial_ends,
  DATE '2025-08-01' - signup_date AS days_with_us,
  AGE(DATE '2025-08-01', signup_date) AS age
FROM customers
WHERE customer_id <= 5;

An interval is a length of time: INTERVAL '45 minutes', INTERVAL '2 days'. Date minus date gives a whole number of days. AGE gives years, months and days, which reads better for people.

For a range of days on a timestamp column, write ordered_at >= '2025-07-01' AND ordered_at < '2025-07-06'. BETWEEN '2025-07-01' AND '2025-07-05' stops at midnight at the start of 5 July, so every order later that day is missed.

MySQL has no ::; use CAST. It formats dates with DATE_FORMAT(d, '%d %b %Y') instead of TO_CHAR, counts days with DATEDIFF(a, b), and adds time with DATE_ADD(d, INTERVAL 30 DAY). MySQL's 7 / 2 gives 3.5000; its whole-number division is DIV.

Interviews ask: "Why does 1 / 2 give 0?", "Count orders per month" (DATE_TRUNC or EXTRACT), and "Users who signed up in the last 30 days". In real code that last one uses current_date; in practice here we use a fixed date so the answer never changes.

In one line

Integer / integer drops the decimals: cast or use 100.0 first; EXTRACT, DATE_TRUNC, TO_CHAR and INTERVAL handle dates.