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