Функции и операции даты и времени в PostgreSQL

PostgreSQL поддерживает отдельные типы данных для дат, времени, временных меток и интервалов: date, time, timestamp и interval. С ними можно выполнять арифметические операции, прибавлять дни и часы, считать разницу между датами, округлять дату до нужного периода и извлекать год, месяц, день или час.

В этом разделе собраны основные операторы и функции для работы с датой и временем в PostgreSQL: сложение и вычитание date/time/timestamp/interval, примеры расчета интервалов, функции extract, date_part, date_trunc и типовые SQL-запросы для аналитики.

Что внутри:

арифметика дат и времени в PostgreSQL;
сложение date, time, timestamp и interval;
вычитание дат и расчет разницы в днях или часах;
умножение interval на число;
функции extract и date_part для получения года, месяца, дня и часа;
date_trunc для округления даты до дня, месяца, квартала или года;
примеры SQL-запросов для аналитики по датам;
типовые ошибки при работе с датами, временем и часовыми поясами.

Мы уже обсуждали типы данных и времени в соответствующем разделе выше. Теперь рассмотрим операторов и функции даты и времени.

Таблице ниже описывает поведение базовых арифметических операторов:

Оператор	Пример	Результат
+	дата '2001-09-28' + целое число '7'	дата '2001-10-05'
+	дата '2001-09-28' + интервал '1 час'	Временная отметка '2001-09-28 01:00:00'
+	дата '2001-09-28' + время '03:00'	Временная отметка '2001-09-28 03:00:00'
+	интервал '1 день' + интервал '1 час'	интервал '1 день 01:00:00'
+	Временная отметка '2001-09-28 01:00' + интервал '23 часа'	Временная отметка '2001-09-29 00:00:00'
+	время '01:00' + интервал '3 часа'	Время '04:00:00'
-	- интервал '23 часа'	интервал '-23:00:00'
-	дата '2001-10-01' - дата '2001-09-28'	Целое число '3' (дня)
-	дата '2001-10-01' – целое число '7'	Дата '2001-09-24'
-	дата '2001-09-28' - интервал '1 час'	Временная отметка '2001-09-27 23:00:00'
-	время '05:00' - время '03:00'	Интервал '02:00:00'
-	время '05:00' - интервал '2 часа'	Время '03:00:00'
-	Временная отметка '2001-09-28 23:00' – интервал '23 часа'	Временная отметка '2001-09-28 00:00:00'
-	интервал '1 день' - интервал '1 час'	интервал '1 день -01:00:00'
-	Временная отметка '2001-09-29 03:00' – временная отметка '2001-09-27 12:00'	интервал '1 день 15:00:00'
*	900 * интервал '1 секунда'	интервал '00:15:00'
*	21 * интервал '1 день'	интервал '21 день'
*	Двойная точность '3.5' * Интервалl '1 час'	интервал '03:30:00'
/	Интервал '1 час' / двойная точность '1.5'	интервал '00:40:00'

Ниже приводим список всех важных функций, связанных с датой и временем.

S. No.	Функция и Описание
1	AGE() Вычесть аргументы
2	CURRENT DATE /TIME() Текущие дата и время
3	DATE_PART() Получить подполе (эквивалентное извлечению)
4	EXTRACT() Извлекает части из даты
5	ISFINITE() Тест на конечную дату, время и интервал (не +/- бесконечность)
6	JUSTIFY Изменить интервал

AGE (timestamp, timestamp), AGE(timestamp)

S. No.

Функция и Описание

AGE(timestamp, timestamp)

При совместном использовании с формой TIMESTAMP второго аргумента AGE() вычитает аргументы, создавая «символический» результат, который использует годы и месяцы и имеет тип INTERVAL.

AGE(timestamp)

При использовании только с одним аргументом TIMESTAMP, AGE()вычитает из current_date (текущей даты) (в полночь)

Пример функции AGE(timestamp, timestamp):

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');

Результат будет следующим:

           age
-------------------------
 43 years 9 mons 27 days

Пример функции AGE(timestamp):

testdb=# select age(timestamp '1957-06-13');

Результат будет следующим:

           age
--------------------------
 55 years 10 mons 22 days

CURRENT DATE/TIME()

PostgreSQL предоставляет ряд функций, которые возвращают значения, относящиеся к текущей дате и времени. Ниже приведены некоторые функции:

S. No.	Функция и Описание
1	CURRENT_DATE Показывает текущую дату.
2	CURRENT_TIME Показывает значения с часовым поясом.
3	CURRENT_TIMESTAMP Показывает значения с часовым поясом.
4	CURRENT_TIME(precision) Произвольно принимает параметр точности, который приводит к округлению результата до указанного числа дробных цифр в поле секунд.
5	CURRENT_TIMESTAMP(precision) Произвольно принимает параметр точности, который приводит к округлению результата до указанного числа дробных цифр в поле секунд.
6	LOCALTIME Показывает значения без часового пояса.
7	LOCALTIMESTAMP Показывает значения без часового пояса.
8	LOCALTIME(precision) Произвольно принимает параметр точности, который приводит к округлению результата до указанного числа дробных цифр в поле секунд.
9	LOCALTIMESTAMP(precision) Произвольно принимает параметр точности, который приводит к округлению результата до указанного числа дробных цифр в поле секунд.

Примеры использования функций, указанных выше:

testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)

 

testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)

 

testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)

 

testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)

 

testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

PostgreSQL также предоставляет функции, которые показывают время начала текущего оператора, а также фактическое текущее время в момент вызова функции. Эти функции приведены ниже:

S. No.	Функция и Описание
1	transaction_timestamp() Эквивалентно CURRENT_TIMESTAMP, но назван так, чтобы четко отражать то, что он возвращает
2	statement_timestamp() Возвращает время начала текущего оператора.
3	clock_timestamp() Возвращает фактическое текущее время, и поэтому его значение меняется даже внутри одной SQL-команды.
4	timeofday() Он возвращает фактическое текущее время, но в виде форматированной текстовой строки, а не временной метки со значением часового пояса.
5	now() Традиционный эквивалент PostgreSQL для transaction_timestamp().

DATE_PART(text, timestamp), DATE_PART(text, interval), DATE_TRUNC(text, timestamp)

S. No.

Функция и Описание

DATE_PART('field', source)

Эти функции выводят подполя. Параметр поля должен быть строковым значением, а не именем.

Допустимые имена полей: век, день, декада, эпоха, час, изодоу, изогод, микросекунды, тысячелетие, миллисекунды, минуты, месяц, квартал, секунды, часовой пояс, час_часов, час_минута_часов, неделя, год.

DATE_TRUNC('field', source)

Эта функция концептуально похожа на функцию усечения для чисел. source — это выражение значения типа timestamp или interval. Поле выбирает, до какой точности усекать входное значение. Возвращаемое значение имеет тип метки времени или интервала.

Допустимые значения поля: микросекунды, миллисекунды, секунды, минуты, часы, день, неделя, месяц, квартал, год, десятилетие, столетие, тысячелетие.

Ниже приведены примеры для функций DATE_PART('field', source):

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

 

testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part
-----------
         4
(1 row)

Ниже приведены примеры для функций ('field', source):

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)

 

testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

EXTRACT(field from timestamp), EXTRACT(field from interval)

Функция EXTRACT (field from source) извлекает подполя, такие как год или час, из значений даты/времени. Источник должен быть выражением значения типа timestamp, time или interval. Поле — это идентификатор или строка, которая выбирает, какое поле следует извлечь из исходного значения. Функция EXTRACT возвращает значения типа двойной точности.

Ниже приведены допустимые имена полей (аналогичные именам полей функции DATE_PART): век, день, декада, доу, дой, эпоха, час, изодоу, изогод, микросекунды, миллениум, миллисекунды, минуты, месяц, квартал, секунды, часовой пояс, часовой пояс. , timezone_minute, неделя, год.

Ниже приведены примеры EXTRACT('field', source):

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part
-----------
        20
(1 row)

 

testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

ISFINITE(date), ISFINITE(timestamp), ISFINITE(interval)

S. No.	Функция и Описание
1	ISFINITE(date) Тестирует на конечную дату.
2	ISFINITE(timestamp) Тестирует на конечную отметку времени.
3	ISFINITE(interval) Тестирует на конечный интервал.

Примеры функций ISFINITE():

testdb=# SELECT isfinite(date '2001-02-16');
 isfinite
----------
 t
(1 row)

 

testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite
----------
 t
(1 row)

 

testdb=# SELECT isfinite(interval '4 hours');
 isfinite
----------
 t
(1 row)

JUSTIFY_DAYS(interval), JUSTIFY_HOURS(interval), JUSTIFY_INTERVAL(interval)

S. No.	Функция и Описание
1	JUSTIFY_DAYS(interval) Настраивает интервал таким образом, чтобы 30-дневные периоды времени представлялись в виде месяцев. Вернуть тип интервала.
2	JUSTIFY_HOURS(interval) Регулирует интервал таким образом, чтобы 24-часовые периоды времени представлялись в виде дней. Вернуть тип интервала.
3	JUSTIFY_INTERVAL(interval) Регулирует интервал, используя JUSTIFY_DAYS и JUSTIFY_HOURS, с дополнительными корректировками знака. Вернуть тип интервала.

Примеры функций ISFINITE():

testdb=# SELECT justify_days(interval '35 days');
 justify_days
--------------
 1 mon 5 days
(1 row)

 

testdb=# SELECT justify_hours(interval '27 hours');
 justify_hours
----------------
 1 day 03:00:00
(1 row)

 

testdb=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

Вопрос: Как прибавить дни к дате в PostgreSQL?

Ответ: К значению date можно прибавить целое число дней или interval. Например, date '2001-09-28' + 7 вернет дату через семь дней, а прибавление interval позволяет работать с часами, минутами и другими единицами времени.

Вопрос: Как посчитать разницу между двумя датами?

Ответ: Если вычесть одну дату из другой, PostgreSQL вернет количество дней. Если вычитать timestamp из timestamp, результатом будет interval с днями, часами, минутами и секундами.

Вопрос: Чем отличаются date, time, timestamp и interval?

Ответ: date хранит только дату, time — только время, timestamp — дату и время вместе, а interval описывает длительность. Для расчетов важно понимать, какой тип вернет выражение после операции.

Вопрос: Как получить год, месяц или день из даты?

Ответ: Для извлечения частей даты используют extract или date_part. Эти функции позволяют получить year, month, day, hour и другие компоненты даты или времени.

Вопрос: Как округлить дату до месяца или года?

Ответ: Для этого используют date_trunc. Например, с ее помощью можно привести timestamp к началу дня, месяца, квартала или года и затем группировать данные в аналитических запросах.

PostgreSQL: типы данных

PostgreSQL: команда TRUNCATE TABLE

Функции для работы со строками PostgreSQL

DBeaver: создание подключения

← Предыдущая статья

PostgreSQL - Привилегии

Следующая статья →

PostgreSQL - Функции