Re: Select "todays" timestamps in an index friendly way - Mailing list pgsql-general

Hi Lutz.

On Tue, Oct 23, 2018 at 11:38 AM, Lutz Horn <lutz.horn@posteo.de> wrote:
> 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;

1st remark. Do NOT use closed interval for timestamps. Always use
half-open or you'll run into problems ( i.e., you are going to miss
2018-10-23T23:59:59.25 in that query ). For real like things ( which
timestamps are, they identify a point on the time line ) use half-open
( you can cover a line with non-overlapping half-open segments, not
with closed ones ).

I.e., your query will better be stated as

  where ts >= '2018-10-23T00:00:00'::timestamp
        and ts < '2018-10-24T00:00:00'::timestamp;

Which, as a nice bonus, can rely on the time part defaulting to 0:

  where ts >= '2018-10-23'::timestamp
        and ts < '2018-10-24'::timestamp;

and then be expressed in other ways, like

  where ts >= '2018-10-23'::timestamp
        and ts < ('2018-10-23'::timestamp + '1 day'::interval)

> 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;

This is not a good way to deal with timestamp values, they are just
numbers, play with them as such. Try using something like

date_trunc('day',now()) = date_trunc('day',$1)

which states your purposes more clearly.

> 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?

Well, if your definition of today is 'same value as now() when
truncated to days' we can use part of what I've written above,
1st calculate today and tomorrow with same timestamp arithmetic and date_trunc:

 select now(), date_trunc('day',now()) as today,
date_trunc('day',now()+'1 day') as tomorrow;
              now              |         today          |        tomorrow
-------------------------------+------------------------+------------------------
 2018-10-23 11:58:01.699407+02 | 2018-10-23 00:00:00+02 | 2018-10-24 00:00:00+02
(1 row)

Then plug that result in your query ( using the half-open technique )
described above:

where ts >=  date_trunc('day',now())
    and ts < date_trunc('day',now()+'1 day') as tomorrow;

IIRC this should use the index, you can RTFM in case you prefer using
current_timestamp and her cousins, but bear in mind if you use
something like current_date you should convert it to timestamp, not
convert ts to date, to get easy index usage.


Francisco Olarte.