Select "todays" timestamps in an index friendly way - Mailing list 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