В PostgreSQL есть несколько подходящих операторов:
- CURRENT_DATE вернёт текущую дату.
- CURRENT_TIME выведет текущее время.
- CURRENT_TIMESTAMP вернёт текущие дату и время.
Дата и время часто хранятся в таком виде: '2009-11-19 11:03:05'. Но сравнивать дату в этом формате не всегда удобно, если нужен, к примеру, только год.
Для таких случаев в PostgreSQL используют функцию DATE_TRUNC. Функция DATE_TRUNC «усекает» дату и время до необходимого значения: года, месяца или дня. Синтаксис функции такой: DATE_TRUNC('отрезок времени', поле).
Отрезок времени может быть разным, главное, не забыть одинарные кавычки:
- 'microseconds' — микросекунды;
- 'milliseconds' — миллисекунды;
- 'second' — секунда;
- 'minute' — минута;
- 'hour' — час;
- 'day' — день;
- 'week' — неделя;
- 'month' — месяц;
- 'quarter' — квартал;
- 'year' — год;
- 'decade' — десятилетие;
- 'century' — век.
Функция DATE_TRUNC — полезный инструмент, но её аналоги есть не во всех СУБД. Функция пригодится в случае, если нужно посчитать записи за конкретный период: неделю или месяц.
Для конкретной даты используют функцию EXTRACT. Её синтаксис отличается от функции DATE_TRUNC:EXTRACT(отрезок времени FROM поле).
Задача 1
Добавьте условие в код задания: выгрузите из таблицы invoice несколько полей, в которых указаны идентификаторы покупателей от 20 до 50 включительно.
SELECT customer_id,
invoice_date,
total
FROM invoice
WHERE customer_id between 20 and 50
Задача 2
Добавьте к выгруженным полям информацию о месяце и неделе заказа. Информацию о дате хранит поле invoice_date. Месяц заказа должен быть представлен первым числом месяца в формате '2009-01-01 00:00:00', а неделя заказа — номером недели.
Не забудьте изменить тип данных поля invoice_date, чтобы применить функции для работы с датой. Поменяйте тип данных поля на timestamp. Тогда данные не исказятся от автоматической поправки на часовой пояс.
SELECT customer_id,
invoice_date,
total,
DATE_TRUNC('month', CAST(invoice_date AS timestamp)),
EXTRACT(WEEK FROM CAST(invoice_date AS timestamp))
FROM invoice
WHERE customer_id BETWEEN 20 AND 50;
Задача 3
Отфильтруйте получившуюся таблицу по номерам недели. Оставьте в таблице данные за 5, 7, 10, 33 и 48 недели.
SELECT customer_id,
invoice_date,
total,
DATE_TRUNC('month', CAST(invoice_date AS timestamp)),
EXTRACT(WEEK FROM CAST(invoice_date AS timestamp))
FROM invoice
WHERE customer_id BETWEEN 20 AND 50
AND (EXTRACT(WEEK FROM CAST(invoice_date AS timestamp)) IN (5,7,10,33,48));
Задача 4
Выгрузите из таблицы invoice всю информацию о заказах, оформленных первого числа каждого месяца. Не забудьте привести дату к типу timestamp.
SELECT *
FROM invoice
WHERE EXTRACT(DAY FROM CAST(invoice_date AS timestamp)) = 1;
Задача 5
Выгрузите адреса электронной почты сотрудников из города Калгари (англ. Calgary), которых наняли на работу в 2002 году.
SELECT email
FROM staff
WHERE EXTRACT(YEAR FROM CAST(hire_date AS timestamp)) = 2002 AND city LIKE 'Calgary';