Select "todays" timestamps in an index friendly way

Поиск
Список
Период
Сортировка
От Lutz Horn
Тема Select "todays" timestamps in an index friendly way
Дата
Msg-id 20181023093801.GA21523@lutz-pc.ecm4u.intra
обсуждение исходный текст
Ответы Re: Select "todays" timestamps in an index friendly way  (Francisco Olarte <folarte@peoplecall.com>)
Re: Select "todays" timestamps in an index friendly way  (Thomas Kellerer <spam_eater@gmx.net>)
Re: Select "todays" timestamps in an index friendly way  (Mike Rylander <mrylander@gmail.com>)
Re: Select "todays" timestamps in an index friendly way  (Steven Lembark <lembark@wrkhors.com>)
Список pgsql-general
Hi,

I am looking for a way to select all timestamps that are "today" in an
index friendly way. This select should not depend on the concrete value
of "today".

Given a table

    create temporary table t (
      id SERIAL primary key,
      ts timestamp not null default now()
    );

with some data

    insert into t (ts)
    select ts
    from generate_series(
      '2018-01-01T00:00:01'::timestamp,
      '2018-12-31T23:59:59'::timestamp,
      '2 minutes')
    as ts;

and an index

    create index on t (ts, id);

I can of course make an explicit select for `ts` values that are
"today":

    select ts, id
      from t
     where ts >= '2018-10-23T00:00:00'::timestamp
       and ts <= '2018-10-23T23:59:59'::timestamp;

This uses an Bitmap Index Scan on `t_ts_id_idx`. Good.

But the where conditions depends on concrete values of "today" which
will not return the intended result if I execute it tomorrow. I will
have to change the where condition. Not good.

I am looking for a way to make the where condition independed of the
date of execution. I can create a function

    create function is_today(timestamp) returns boolean as $$
        select to_char(now(), 'YYYY-MM-DD') = to_char($1, 'YYYY-MM-DD');
    $$ language sql;

that converts the timestamps to text. But using this function

    select * from t where is_today(ts);

will not benefit from the existing index. A Seq Scan on `t` will be
used. Not good.

Is there a way to have both: be independed of the concrete value of
"today" *and* use the index on the timestamp column?

Lutz


В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Gierth
Дата:
Сообщение: Re: How to declare PG version for compiling extensions.
Следующее
От: Francisco Olarte
Дата:
Сообщение: Re: Select "todays" timestamps in an index friendly way