Дата и время (вводная статья)¶
В PostgreSQL есть четыре основных типа для работы с временем:
- date - дата без временной части.
Дату удобно задавать в формате 'YYYY-MM-DD' ('2010-06-30')
- timestamp - дата с временем.
Данный тип данных отличается от типа date наличием временной составляющей, которая позволяет хранить время с точностью до микросекунды.
- interval - интервал времени между датами
Обычно задается в достаточно свободном текстовом формате (примеры: interval '1 day 2 week 36 minute', interval '1 year -5 day 1 hour')
- time - время без даты.
Удобный тип данных чем-то похожий на interval, который работает по модулю 24 (часа), то есть интервал, в котором отсутствуют дни.
Временная арифметика¶
Вычисление интервала доступно для всех временных типов данных.
Если в операции участвуют только переменные с типом date, то результатом будет целое число, означающее количество дней между указанными датами.
date - date = число дней (integer)
SELECT '2012-01-05'::date - '2012-01-01'::date AS result;
result
----------
4
Если при вычислении интервала между датами хотя бы одна переменная с типом timestamp, то результатом вычисления будет значение с типом interval
timestamp - timestamp = interval
SELECT '2012-01-05'::timestamp - '2012-01-01'::timestamp AS result;
result
----------
4 days
В арифметических временных операциях можно произвольно смешивать временные типы данных. В этом случае date преобразуется в timestamp.
Значения времени можно уменьшать или увеличивать на заданный интервал, результатом будет значение с типом timestamp
Примеры
SELECT '2012-01-05'::timestamp - '1 hour'::interval AS result;
result
---------------------
2012-01-04 23:00:00
SELECT '2010-05-06'::date + interval '1 month 1 day 1 minute' AS result;
result
---------------------
2010-06-07 00:01:00
Интервалы можно складывать и вычитать между собой, делить и умножать на произвольные вещественные числа.
SELECT '1 hour'::interval / 7 AS result;
result
-----------------
00:08:34.285714
SELECT interval '1 minute' * 99 AS result;
result
----------
01:39:00
SELECT interval '1 hour' - interval '33 minutes' AS result;
result
----------
00:27:00
SELECT interval '1 hour 27 minutes' + interval '33 minutes' AS result;
result
----------
02:00:00
Предупреждение
Интервалы хранят данные в трёх отдельных полях: месяцах, днях, секундах. Это сделано из-за того, что количество дней в месяце и часов в дне могут быть разными. Поэтому арифметика с участием двух интервалов довольно специфичная
Часы "не превращаются" в дни
SELECT interval '1 day 20 hours' + interval '10 hours' AS result;
result
----------------
1 day 30:00:00
Дни "не превращаются" в месяцы
SELECT interval '20 days' + interval '20 days' AS result;
result
---------
40 days
Будьте внимательны!
Специальные значения времени¶
В PostgreSQL есть строковые константы, которые преобразуются в реальные значения времени в момент выполнения запроса
epoch - возвращает нулевое время отчета Unix-систем
SELECT 'epoch'::timestamp; timestamp --------------------- 1970-01-01 00:00:00
infinity - возвращает дату, которая, как принято считать, находится позже любой другой даты
SELECT 'infinity'::timestamp; timestamp ----------- infinity
-infinity - возвращает дату, которая, как принято считать, находится раньше, чем любая другая дата
SELECT '-infinity'::timestamp; timestamp ----------- -infinity
today - возвращает текущую дату
SELECT 'today'::timestamp, now()::date; timestamp | now ---------------------+----------- 2013-07-21 00:00:00 | 2013-07-21
now - возвращает текущую дату с временем
SELECT 'now'::timestamp, now(); timestamp | now ----------------------------+------------------------------- 2013-07-21 17:16:55.938193 | 2013-07-21 17:16:55.938193+06
tomorrow - возвращает дату завтрашнего дня
SELECT 'tomorrow'::timestamp, now()::date; timestamp | now ---------------------+----------- 2013-07-22 00:00:00 | 2013-07-21
yesterday - возвращает дату вчерашнего дня
SELECT 'yesterday'::timestamp, now()::date; timestamp | now ---------------------+----------- 2013-07-20 00:00:00 | 2013-07-21
allballs - возвращает полночь
SELECT 'allballs'::time; time ---------- 00:00:00
Полезные функции¶
Округлить время до даты
SELECT (timestamp '2010-06-12 20:11')::date;
date
------------
2010-06-12
Округлить дату до недели, месяца, квартала, года
Для округления времени существует полезная функция date_trunc
SELECT date_trunc('month', timestamp '2010-06-12 20:11');
date_trunc
---------------------
2010-06-01 00:00:00
SELECT date_trunc('week', timestamp '2010-06-12 20:11');
date_trunc
---------------------
2010-06-07 00:00:00
SELECT date_trunc('quarter', timestamp '2010-06-12 20:11');
date_trunc
---------------------
2010-04-01 00:00:00
SELECT date_trunc('year', timestamp '2010-06-12 20:11');
date_trunc
---------------------
2010-01-01 00:00:00
Существуют следующие константы для округления даты:
- microseconds - до микросекунд
- milliseconds - до миллисекунда
- second - до секунд
- minute - до минут
- hour - до часов
- day - до дней
- week - до недели
- month - до месяца
- quarter - до квартала
- year - до года
- decade - до десятилетия
- century - до века
- millennium - до тысячелетия
Получение полей времени (года, месяца, недели, дня, часа, минуты, секунды и т. д.)
Для получение полей времени применяются функции EXTRACT или date_part
SELECT EXTRACT(year FROM now()), date_part('year', now()), now()::date;
date_part | date_part | now
-----------+-----------+-----------
2013 | 2013 | 2013-07-21
SELECT EXTRACT(month FROM now()), date_part('month', now()), now();
date_part | date_part | now
-----------+-----------+-----------
7 | 7 | 2013-07-21
SELECT EXTRACT(dow FROM now()), date_part('dow', now()), now()::date;
date_part | date_part | now
-----------+-----------+-----------
0 | 0 | 2013-07-21
Получить можно следующие поля:
- century - номер столетия
- day - день месяца
- decade - номер десятилетия
- dow - день недели, где 0 - воскресения, а 6 - суббота
- isodow - день недели, где 1 - понедельник, а 7 - суббота
- doy - день года
- epoch - количество секунд с начала отчета Unix-времени
- hour - час
- isoyear - год в формате ISO 8601
- microseconds - микросекунды
- millennium - номер тысячелетия
- milliseconds - миллисекунды
- minute - минуты
- month - месяц
- quarter - квартал (с 1 по 4)
- second - секунда
- timezone - зона времени в секундах
- timezone_hour - час зоны времени
- timezone_minute - минута зоны времени
- week - номер недели года
- year - год