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

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

index compatible date_trunc in postgres?


  • From: Bryce Nesbitt <bryce2(at)obviously(dot)com>
  • To: sql pgsql <pgsql-sql(at)postgresql(dot)org>
  • Subject: index compatible date_trunc in postgres?
  • Date: Thu, 18 Dec 2008 21:46:45 -0800
  • Message-id: <494B3545.8030701@obviously.com> <text/plain>

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




Home | Main Index | Thread Index

Privacy Policy | About PostgreSQL
Copyright © 1996 – 2012 PostgreSQL Global Development Group