Working with dates

Dates appear in almost every database: birthdays, order times, exam days. Working with them differs a little between the two systems — this lesson shows both.

Types

• date — just a date: 2026-11-14;
• timestamp (PostgreSQL) / datetime (MySQL) — date and time: 2026-11-14 18:30:00.

Dates can be compared directly — they sort just like numbers:

SELECT id, ordered_at FROM orders WHERE ordered_at >= '2026-10-25' ORDER BY ordered_at;

Today’s date

CURRENT_DATE is today and CURRENT_TIMESTAMP (or NOW()) is the current moment. Both work in either database.

SELECT CURRENT_DATE AS today;

Taking a date apart: EXTRACT

SELECT id, ordered_at, EXTRACT(YEAR FROM ordered_at) AS y, EXTRACT(MONTH FROM ordered_at) AS m
FROM orders LIMIT 5;

EXTRACT exists in both. On top of that:

PostgreSQL has date_trunc, which cuts a date down to the month, week or day:

SELECT date_trunc('month', ordered_at) AS month, count(*) FROM orders GROUP BY 1 ORDER BY 1 DESC LIMIT 4;

To show a date nicely: to_char(ordered_at, 'DD.MM.YYYY').

Adding time and measuring a difference

PostgreSQL uses interval:

SELECT id, ordered_at, ordered_at + interval '7 days' AS deadline FROM orders LIMIT 3;

The difference of two timestamps is delivered_at - ordered_at (an interval); in days: delivered_at::date - ordered_at::date.

Picking a whole month or year

BETWEEN '2026-10-01' AND '2026-10-31' on a column with a time part misses the orders placed on 31 October after midnight! The reliable way is “before the start of the next month”:

SELECT count(*) FROM orders WHERE ordered_at >= '2026-10-01' AND ordered_at < '2026-11-01';

Remember

• Dates are written as 'year-month-day' and compared like any value.
• EXTRACT(YEAR FROM date) works in both databases.
• For a period: >= start AND < start of the next period.