EXTRACT - извлечение из даты части (год, месяц, день...)

EXTRACT(field FROM source)

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

century

Век:

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13')

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');

Первый век начался 0001-01-01 00:00:00, хотя люди в то время и не считали так. Это определение распространяется на все страны с григорианским календарём. Века с номером 0 не было; считается, что 1 наступил после -1. Если такое положение вещей вас не устраивает, направляйте жалобы по адресу: Ватикан, Собор Святого Петра, Папе.

day

Для значений timestamp это день месяца (1–31); для значений interval — число дней

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');

SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');

decade

Год, делённый на 10

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');

dow

День недели, считая с воскресенья (0) до субботы (6)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

Заметьте, что в extract дни недели нумеруются не так, как в функции to_char(..., 'D').

doy

День года (1–365/366)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40')

epoch

Для значений timestamp with time zone это число секунд с 1970-01-01 00:00:00 UTC (отрицательное для предшествующего времени); для значений date и timestamp — номинальное число секунд с 1970-01-01 00:00:00 без учёта часового пояса, переходов на летнее время и т. п.; для значений interval — общее количество секунд в интервале

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');

Преобразовать время эпохи назад, в значение timestamp with time zone, с помощью to_timestamp можно так:

SELECT to_timestamp(982384720.12);

Имейте в виду, что применяя to_timestamp к времени эпохи, извлечённому из значения date или timestamp, можно получить не вполне ожидаемый результат: эта функция подразумевает, что изначальное значение задано в часовом поясе UTC, но это может быть не так.

hour

Час (0–23)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');

isodow

День недели, считая с понедельника (1) до воскресенья (7)

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');

Результат отличается от dow только для воскресенья. Такая нумерация соответствует ISO 8601.

isoyear

Год по недельному календарю ISO 8601, в который попадает дата (неприменимо к интервалам)

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');

Год по недельному календарю ISO начинается с понедельника недели, в которой оказывается 4 января, так что в начале января или в конце декабря год по ISO может отличаться от года по григорианскому календарю. Подробнее об этом рассказывается в описании поля week.

Этого поля не было в PostgreSQL до версии 8.3.

julian

Юлианская дата, соответствующая дате или дате/времени (для интервала не определена). Значение будет дробным, если заданное время отличается от начала суток по местному времени. За дополнительной информацией обратитесь к Разделу B.7.

SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');

SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');

microseconds

Значение секунд с дробной частью, умноженное на 1 000 000; заметьте, что оно включает и целые секунды

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');

millennium

Тысячелетие

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');

Годы 20 века относятся ко второму тысячелетию. Третье тысячелетие началось 1 января 2001 г.

milliseconds

Значение секунд с дробной частью, умноженное на 1 000; заметьте, что оно включает и целые секунды.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');

minute

Минуты (0–59)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');

month

Для значений timestamp это номер месяца в году (1–12), а для interval — остаток от деления числа месяцев на 12 (0–11)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');

quarter

Квартал (1–4), к которому относится дата

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');

second

Секунды, включая дробную часть

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');

timezone

Смещение часового пояса от UTC, представленное в секундах. Положительные значения соответствуют часовым поясам к востоку от UTC, а отрицательные — к западу. (Строго говоря, в Postgres Pro используется не UTC, так как секунды координации не учитываются.)

timezone_hour

Поле часов в смещении часового пояса

timezone_minute

Поле минут в смещении часового пояса

week

Номер недели в году по недельному календарю ISO 8601. По определению, недели ISO 8601 начинаются с понедельника, а первая неделя года включает 4 января этого года. Другими словами, первый четверг года всегда оказывается в 1 неделе этого года.

В системе нумерации недель ISO первые числа января могут относиться к 52-ой или 53-ей неделе предыдущего года, а последние числа декабря — к первой неделе следующего года. Например, 2005-01-01 относится к 53-ей неделе 2004 г., а 2006-01-01 — к 52-ей неделе 2005 г., тогда как 2012-12-31 включается в первую неделю 2013 г. Поэтому для получения согласованных результатов рекомендуется использовать поле isoyear в паре с week.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');

year

Поле года. Учтите, что года 0 не было, и это следует иметь в виду, вычитая из годов нашей эры годы до нашей эры.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');

Примечание

С аргументом +/-бесконечность extract возвращает +/-бесконечность для монотонно увеличивающихся полей (epoch, julian, year, isoyear, decade, century и millennium). Для других полей возвращается NULL. До версии 9.6 Postgres Pro возвращал ноль для всех случаев с бесконечными аргументами.

Функция 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');

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');

Источник здесь.