Содержание

Предыдущий раздел

MVCC и VACUUM

Следующий раздел

Ресурсы в сети

Дата и время (вводная статья)

В 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 - год
comments powered by Disqus