Skip site navigation (1) Skip section navigation (2)

Re: index compatible date_trunc in postgres?

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Bryce Nesbitt" <bryce2(at)obviously(dot)com>
Cc: "sql pgsql" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: index compatible date_trunc in postgres?
Date: 2008-12-19 05:52:24
Message-ID: dcc563d10812182152ida80ebcgab4ca7e16d11bdff@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Thu, Dec 18, 2008 at 10:46 PM, Bryce Nesbitt <bryce2(at)obviously(dot)com> wrote:
> I've got a legacy app that does 8.3 incompatible date searches like so:
> explain select count(*) from contexts where publication_date like '2006%';
> explain select count(*) from contexts where publication_date like
> '2006-09%';
>
> I've got my choice of refactoring, but all these share the same
> sequential scan limitation:
> explain select count(*) from contexts where publication_date::text LIKE
> '2006%';
> explain select count(*) from contexts where
> date_trunc('year',publication_date) = '2006-01-01';
> explain select count(*) from contexts where extract('year' from
> publication_date) = '2006';
>
> Are there any other index compatible methods, other than turning it into
> a range search?
> explain select count(*) from contexts where publication_date >=
> '2006-01-01' and publication_date < '2007-01-01';
> explain select count(*) from contexts where publication_date >=
> '2006-09-01' and publication_date < '2006-09-31 24:00:00';

You can create an index on date_trunc (on timestamp without timezone,
but not on timestamp with timezone since it's not immutable)

create index mytable_datetrunc_month on mytable (date_trunc('month',
timestampfield));

In response to

Responses

pgsql-sql by date

Next:From: Sebastian RychterDate: 2008-12-19 13:03:42
Subject: Downgrade database and problem with sequences
Previous:From: Bryce NesbittDate: 2008-12-19 05:46:45
Subject: index compatible date_trunc in postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group