EXTRACT(поле FROM источник)

Функция extract извлекает поля, такие как год или час, из значений даты и времени. Источником должно быть заданное выражением значение типа timestamp, date, time или interval. (Значения timestamp и time могут быть как с указанием часового пояса, так и без.) Поле — это идентификатор или строка, с помощью которой выбирается поле для извлечения из значения источника. Для разных типов входных данных допустимы разные поля: например, поля меньше дней не могут быть извлечены из date, а поля дней и более не могут быть извлечены из time. Функция extract возвращает значения типа numeric.

Допустимы следующие имена полей:

При обработке значения interval функция extract создаёт значения полей, соответствующие интерпретации, которая используется функцией вывода интервала. При этом, если начать с ненормализованного представления интервала, могут быть получены неожиданные результаты, например:

SELECT INTERVAL '80 minutes';
Результат: 01:20:00
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
Результат: 20

Функция extract в основном предназначена для вычислительных целей. Функции форматирования даты/времени описаны в Разделе 9.8.

Функция date_part эмулирует традиционный для Ingres эквивалент стандартной SQL-функции extract:

date_part('поле', источник)

Заметьте, что здесь параметр поле должен быть строковым значением, а не именем. Функция date_part воспринимает те же поля, что и extract. По историческим причинам функция date_part возвращает значения типа double precision. В некоторых случаях это может привести к потере точности. Поэтому вместо неё рекомендуется использовать функцию extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Результат:16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Результат:4

Функция date_trunc работает подобно trunc для чисел.

date_trunc(поле, значение [, часовой_пояс ])

Здесь значение — выражение типа timestamp, timestamp with time zone или interval. (Значения типов date и time автоматически приводятся к типам timestamp и interval, соответственно.) Параметр поле определяет, до какой точности обрезать переданное значение. В возвращаемом значении, имеющем также тип timestamp, timestamp with time zone или interval, все поля, менее значимые, чем заданное, будут равны нулю (или одному, если это номер дня или месяца).

Параметр поле может принимать следующие значения:

Когда входное значение имеет тип timestamp with time zone, оно обрезается с учётом заданного часового пояса; например, если обрезать значение до поля day (день), в результате будет получена полночь в этом часовом поясе. По умолчанию входное значение обрезается с учётом параметра TimeZone, но дополнительный аргумент часовой_пояс позволяет выбрать и другой пояс. Название часового пояса может задаваться любым из способов, описанных в Подразделе 8.5.3.

Часовой пояс нельзя задать для значений типа timestamp without time zone или interval. Такие значения всегда воспринимаются как есть.

Несколько примеров (выбран часовой пояс America/New_York):

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Результат: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Результат: 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Результат: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Результат: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Результат: 3 days 02:00:00

Функция date_bin «подгоняет» заданное значение под интервал (шаг), отсчитывая от указанного начального момента.

date_bin(шаг, значение, источник)

Здесь значение — выражение типа timestamp, timestamp with time zone. (Значения типов date автоматически приводятся к типу timestamp.) В качестве шага передаётся выражение типа interval. Результат этой функции, также имеющий тип timestamp, timestamp with time zone или interval, — начало интервала, под который подгоняется значение.

Примеры:

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Результат: 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Результат: 2020-02-11 15:32:30

В случае целых единиц измерения (1 минута, 1 час и т. д.), она даёт тот же результат, что и аналогичный вызов функции date_trunc, с той лишь разницей, что date_bin может округлить дату до произвольного интервала.

Параметр шаг интервала должен быть больше нуля и задаваться в единицах измерения меньше месяца.

Оператор AT TIME ZONE преобразует дату/время без часового пояса в дату/время с часовым поясом и обратно, а также пересчитывает значения time with time zone для различных часовых поясов. Его вариации показаны в Таблице 9.34.

В этих выражениях желаемый часовой_пояс можно задать либо в виде текстовой строки (например, 'America/Los_Angeles'), либо как интервал (например, INTERVAL '-08:00'). В первом случае название часового пояса можно указать любым из способов, описанных в Подразделе 8.5.3. Вариант с интервалом полезен, только если для часового пояса смещение от UTC всегда постоянно, что на практике встречается нечасто.

Синтаксис AT LOCAL можно использовать в качестве сокращённой записи для AT TIME ZONE локальный, где локальный — это значение TimeZone сеанса.

Примеры (предполагается, что текущее значение параметра TimeZone — America/Los_Angeles):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Результат: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Результат: 2001-02-16 18:38:40
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Результат: 2001-02-16 05:38:40
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
Результат: 2001-02-16 17:38:40
SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
Результат: 17:38:40

В первом примере для значения, заданного без часового пояса, указывается часовой пояс и полученное время выводится в текущем часовом поясе (заданном параметром TimeZone). Во втором примере значение времени смещается в заданный часовой пояс и выдаётся без указания часового пояса. Это позволяет хранить и выводить значения с часовым поясом, отличным от текущего. В третьем примере время в часовом поясе Токио преобразуется для часового пояса Чикаго. В четвёртом примере временная метка со значением часового пояса смещается до часового пояса, указанного текущим значением параметра TimeZone, и возвращает значение без часового пояса.

Пятый пример служит в качестве предостережения. Поскольку с входным значением не связана дата, преобразование выполняется с использованием текущей даты сеанса. Поэтому этот статический пример может показывать неверный результат в зависимости от времени года, поскольку в часовом поясе 'America/Los_Angeles' есть переход на летнее время.

Функция timezone(часовой_пояс, время) равнозначна SQL-совместимой конструкции время AT TIME ZONE часовой_пояс.

Функция timezone(часовой_пояс, время) равнозначна SQL-совместимой конструкции время AT TIME ZONE часовой_пояс.

Функция timezone(timestamp) равнозначна SQL-совместимой конструкции timestamp AT LOCAL.

Функция timezone(время) равнозначна SQL-совместимой конструкции время AT LOCAL.

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

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(точность)
CURRENT_TIMESTAMP(точность)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(точность)
LOCALTIMESTAMP(точность)

CURRENT_TIME и CURRENT_TIMESTAMP возвращают время с часовым поясом. В результатах LOCALTIME и LOCALTIMESTAMP нет информации о часовом поясе.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME и LOCALTIMESTAMP могут принимать необязательный параметр точности, определяющий, до какого знака после запятой следует округлять поле секунд. Если этот параметр отсутствует, результат будет иметь максимально возможную точность.

Несколько примеров:

SELECT CURRENT_TIME;
Результат: 14:39:53.662522-05
SELECT CURRENT_DATE;
Результат: 2019-12-23
SELECT CURRENT_TIMESTAMP;
Результат: 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
Результат: 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
Результат: 2019-12-23 14:39:53.662522

Так как эти функции возвращают время начала текущей транзакции, во время транзакции эти значения не меняются. Это считается не ошибкой, а особенностью реализации: цель такого поведения в том, чтобы в одной транзакции «текущее» время было одинаковым и для разных изменений в одной транзакций записывалась одна отметка времени.

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

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

Функция transaction_timestamp() равнозначна конструкции CURRENT_TIMESTAMP, но в её названии явно отражено, что она возвращает. Функция statement_timestamp() возвращает время начала текущего оператора (более точно, время получения последнего командного сообщения от клиента). Функции statement_timestamp() и transaction_timestamp() возвращают одно и то же значение в первой команде транзакции, но в последующих их показания будут расходиться. Функция clock_timestamp() возвращает фактическое текущее время, так что её значение меняется в рамках одной команды SQL. Функция timeofday() существует в PostgreSQL по историческим причинам и, подобно clock_timestamp(), она возвращает фактическое текущее время, но представленное в виде форматированной строки типа text, а не значения timestamp with time zone. Функция now() — традиционный для PostgreSQL эквивалент функции transaction_timestamp().

Все типы даты/времени также принимают специальное буквальное значение now, подразумевающее текущую дату и время (тоже на момент начала транзакции). Таким образом, результат следующих трёх операторов будет одинаковым:

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';  -- см. замечание ниже

В случае необходимости выполнение серверного процесса можно приостановить, используя следующие функции:

pg_sleep( double precision )
pg_sleep_for( interval )
pg_sleep_until( timestamp with time zone )

Функция pg_sleep переводит процесс текущего сеанса в спящее состояние на указанное число секунд (это число может быть дробным). В дополнение к ней для удобства добавлены две функции: pg_sleep_for, принимающая время задержки в типе interval, и pg_sleep_until, позволяющая задать определённое время выхода из спящего состояния. Например:

SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');