Re: Select "todays" timestamps in an index friendly way

Lutz Horn schrieb am 23.10.2018 um 11:38:
> 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?

I typically use:

where ts >= date '2018-10-23'
and ts < date '2018-10-23' + 1