PostgreSQL | Как отобрать сегодняшние записи из таблицы? — efim360.ru

PostgreSQL | Как отобрать сегодняшние записи из таблицы?

Имеются ввиду записи таблицы, которые в одном из столбцов имеют дату, подходящую под понятие «СЕГОДНЯ«. Это все даты которые начались с времени 00:00 до текущего времени или до конца текущего дня, то есть до 24:00.

Перед решением задачи нужно знать в каком формате «дата/время» записываются данные в ячейки. Какой тип у них?

PostgreSQL предлагает нам основные 6 вариантов.

  • timestamp without time zone
  • timestamp with time zone
  • date
  • time without time zone
  • time with time zone
  • interval

Все эти варианты могут быть ещё и в виде массивов:

  • timestamp without time zone []
  • timestamp with time zone []
  • date []
  • time without time zone []
  • time with time zone []
  • interval []

А ещё все эти варианты могут иметь точность значений. Можно урезать точность вычислений при помощи круглых скобок.

 

Отбор сегодняшних записей таблицы через приведение к типу date

Мы можем воспользоваться оператором «двойного двоеточия» для приведения типа «timestamp with time zone» к типу «date»

SELECT * FROM t_scheme.my_table WHERE datecreate::date = CURRENT_TIMESTAMP::date;

или с лимитом, если записей может быть много

SELECT * FROM t_scheme.my_table WHERE datecreate::date = CURRENT_TIMESTAMP::date LIMIT 1000;

В PostgreSQL текущую «временную метку» можно получить тремя способами:

SELECT now();
SELECT CURRENT_TIMESTAMP;
SELECT TIMESTAMP WITH TIME ZONE 'now';

Скриншот:

Три способа получения текущей метки времени с зоной в PostgreSQL
Три способа получения текущей метки времени с зоной в PostgreSQL

Каждый из них вернёт тип «timestamp with time zone«.

В PostgreSQL текущую «дату» можно получить тремя способами:

SELECT now()::date;
SELECT CURRENT_TIMESTAMP::date;
SELECT TIMESTAMP WITH TIME ZONE 'now'::date;

Скриншот:

Три способа получения текущей даты в PostgreSQL
Три способа получения текущей даты в PostgreSQL

 

Сколько сегодняшних записей было создано в таблице в PostgreSQL через приведение к date?

SELECT count(*) FROM t_scheme.my_table WHERE datecreate::date = CURRENT_TIMESTAMP::date;

 

Отбор сегодняшних записей через функцию округления date_trunc()

Информация о «временном типе» (ударение на «о») поможет правильно выбрать тип в функции date_trunc(), при помощи которой можно приводить разные типы к одному формату.

SELECT * FROM t_scheme.my_table WHERE date_trunc('day', datecreate) = date_trunc('day', CURRENT_TIMESTAMP);

или с лимитом, если записей может быть много

SELECT * FROM t_scheme.my_table WHERE date_trunc('day', datecreate) = date_trunc('day', CURRENT_TIMESTAMP) LIMIT 1000;

В результате мы отберём все возможные записи с датой сегодняшнего дня.

 

Как работает отбор сегодняшних записей?

Вся суть находится в предложении WHERE для команды SELECT. Нам нужно в условии сопоставления получать истину (TRUE).

Когда SELECT начнёт обходить каждую запись таблицы, тогда мы будем сравнивать приведение «двух временных меток».

 

Первая метка времени — это значение ячейке в столбце datecreate, округлённое до «ДНЯ».

Пример

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2022-12-04 00:51:57.200537');

Результат округления «метки времени»:

Округлили метку времени до ДНЯ в PostgreSQL
Округлили метку времени до ДНЯ в PostgreSQL

В нашем случае мы работаем с типом TIMESTAMP WITH TIME ZONE. Это значит, что округление этого типа до «ДНЯ» обнулит часы, минуты, секунды, миллисекунды до нуля. Временная зона останется неизменной. Мы как бы получаем тип DATE, но только со временем из нулей.

 

Вторая метка времени — это текущее значение времени, округлённое до «ДНЯ».

SELECT date_trunc('day', CURRENT_TIMESTAMP);

Получаем

Округлили текущую метку времени до ДНЯ в PostgreSQL
Округлили текущую метку времени до ДНЯ в PostgreSQL

 

Сравнивая эти два округлённых значения, мы по сути сравниваем их строковые представления посимвольно. В результате если строковые представления «дат/времён» равны, тогда получаем истину TRUE. Значит метка времени в текущей записи подходит под понятие «СЕГОДНЯ».

 

Информационные ссылки

PostgreSQL | Как отобрать вчерашние записи из таблицы?

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Официальный сайт СУБД PostgreSQLhttps://www.postgresql.org

Команды SQLhttps://postgrespro.ru/docs/postgresql/15/sql-commands

Функции для работы с метками времени — https://postgrespro.ru/docs/postgresql/15/functions-datetime

Функция date_trunc()https://postgrespro.ru/docs/postgresql/15/functions-datetime#FUNCTIONS-DATETIME-TRUNC

PostgreSQL | How to select today’s records?