Операторы и функции для работы с датой и временем

В одном из прошлых уроков вы познакомились с оператором BETWEEN, который позволяет выбирать значения из диапазона или временного промежутка. Работая с датой и временем, вы столкнётесь и с другими задачами.

Иногда будет полезно сравнить данные с текущей датой, например, чтобы понять, сколько дней прошло со дня оформления заказа. В PostgreSQL есть несколько подходящих операторов для такой задачи:

CURRENT_DATE вернёт текущую дату,
CURRENT_TIME выведет текущее время,
CURRENT_TIMESTAMP вернёт текущие дату и время.

Так выглядит запрос, который выведет текущую дату на экран:

SQL

SELECT CURRENT_DATE;

Функция `DATE_TRUNC`

Дата и время часто хранятся в таком виде: '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 — полезный инструмент, но её аналоги есть не во всех СУБД. Функция пригодится в случае, если нужно посчитать записи за конкретный период: неделю или месяц.

Разберём работу функции DATE_TRUNC на примере таблицы staff, которая хранит данные о сотрудниках. Таблица содержит поле birth_date с типом данных timestamp: в этом поле указаны даты рождения сотрудника в виде '1968-01-09 00:00:00'.

SQL
SELECT birth_date
FROM staff
LIMIT 5; 

birth_date
1962-02-18 00:00:00
1958-12-08 00:00:00
1973-08-29 00:00:00
1947-09-19 00:00:00
1965-03-03 00:00:00

Чтобы выгрузить из таблицы staff год рождения сотрудника, можно использовать функцию DATE_TRUNC:

SQL
SELECT DATE_TRUNC('year', birth_date)
FROM staff
LIMIT 5; 

date_trunc
01.01.1962 00:00:00
01.01.1958 00:00:00
01.01.1973 00:00:00
01.01.1947 00:00:00
01.01.1965 00:00:00

Обратите внимание, что дата отображена в том же формате, но теперь она «округлилась» до первого числа года. Если поменять значение на 'month', появится первое число месяца.

SQL
SELECT DATE_TRUNC('month', birth_date)
FROM staff
LIMIT 5; 

date_trunc
01.02.1962 00:00:00
01.12.1958 00:00:00
01.08.1973 00:00:00
01.09.1947 00:00:00
01.03.1965 00:00:00

Функция `EXTRACT`

Такое усечение не подойдёт для случаев, когда нужно получить конкретную часть даты: год, месяц или минуту. Для таких задач используют функцию EXTRACT. Её синтаксис отличается от функции DATE_TRUNC: EXTRACT(отрезок времени FROM поле).

Одинарные кавычки для значений внутри EXTRACT не нужны. Отрезок времени может быть представлен следующими значениями:

CENTURY — век;
DAY — день;
DOY (от англ. day of the year) — день года, выраженный числом от 1 до 365 или 366, если год високосный;
DOW (от англ. day of the week) — день недели, выраженный числом от 0 до 6, где понедельник — 1, воскресенье — 0.
ISODOW (от англ. day of the week и ISO 8601) — день недели, выраженный числом от 1 до 7, где понедельник — 1, воскресенье — 7.
HOUR — час;
MILLISECOND — миллисекунда;
MINUTE — минута;
MONTH — месяц;
SECOND — секунда;
QUARTER — квартал;
WEEK — неделя в году;
YEAR — год.

С помощью функции EXTRACT можно оставить от даты рождения в поле birth_date только год:

SQL
SELECT EXTRACT(YEAR FROM birth_date)
FROM staff
LIMIT 5; 

date_part
1962
1958
1973
1947
1965

Функции и типы данных

Функции DATE_TRUNC и EXTRACT принимают на вход данные тех типов, которые используют для работы с датой и временем. Но есть особенности. Типы date и time функции автоматически переведут в timestamp with time zone. Будьте осторожны: при автоматическом переводе date в timestamp with time zone время подстроится к часовому поясу пользователя и потому может сместиться.

У этой проблемы есть решение — явно изменить тип данных на timestamp. Напомним, что в SQL так по умолчанию обозначается тип timestamp without time zone. С типом данных без часового пояса время не сместится.

Другая особенность функций — они возвращают данные разных типов. DATE_TRUNC вернёт данные типов timestamp without timezone, timestamp with timezone или interval (зависит от того, что получит на вход), а EXTRACT — данные типа numeric.

С типом numeric вы ещё не знакомы. Его используют для вещественных чисел, как и тип real. Отличие в том, что в тип numeric входят числа большего диапазона. Также у типа numeric точность больше, чем у типа real. Это значит, что вещественное число типа numeric может иметь больше цифр после запятой.

Операторы и функции для работы с датой и временем

Функция DATE_TRUNC

Функция EXTRACT

Функции и типы данных

Функция `DATE_TRUNC`

Функция `EXTRACT`