Select "todays" timestamps in an index friendly way

From: "Lutz Horn" <lutz(dot)horn(at)posteo(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Select "todays" timestamps in an index friendly way
Date: 2018-10-23 09:38:01
Message-ID: 20181023093801.GA21523@lutz-pc.ecm4u.intra
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2018-10-23 10:05:17 Re: Select "todays" timestamps in an index friendly way
Previous Message Andrew Gierth 2018-10-23 09:17:43 Re: How to declare PG version for compiling extensions.