Message-ID: <1352112887.4089.1.camel@workstation.wmmi.net>
Subject: Re: Date Index
From: Adam  Tauno Williams <awilliam@whitemice.org>
Reply-To: awilliam@whitemice.org
To: pgsql-sql@postgresql.org
Date: Mon, 05 Nov 2012 05:54:47 -0500
In-Reply-To: <20081031074854.GA1015@a-kretschmer.de>
References: <001e01c93ad0$ffcbe190$ff63a4b0$@hansen@brightbuilders.com>
	<20081031074854.GA1015@a-kretschmer.de>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2008-10-31 at 08:48 +0100, A. Kretschmer wrote:
> am  Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes:
> > Hey all,
> > I?m apparently too lazy to figure this out on my own so maybe one of you can
> > just make it easy on me. J 
> > I want to index a timestamp field but I only want the index to include the
> > yyyy-mm-dd portion of the date, not the time.  I figure this would be where the
> > ?expression? portion of the CREATE INDEX syntax would come in, but I?m not sure
> > I understand what the syntax would be for this.
> > Any suggestions?
> Sure.
> You can create an index based on a function, but only if the function is
> immutable:
> test=# create table foo (ts timestamptz);
> CREATE TABLE
> test=*# create index idx_foo on foo(extract(date from ts));
> ERROR:  functions in index expression must be marked IMMUTABLE
> To solve this problem specify the timezone:
> For the same table as above:
> test=*# create index idx_foo on foo(extract(date from ts at time zone 'cet'));
> CREATE INDEX

I'm attempting to create an index as specified in this [old] thread; but
the adapted example fails.

OGo=> create index job_date_only on job(extract(date from start_date at
time zone 'utc'));
ERROR:  timestamp units "date" not recognized

I assume this is because the data type is 'timestamp with timezone'
which differs slightly from the original example.  But -

select extract(month from start_date) from job;

- [for example] works.  Is there an equivalent syntax to 'date' for
timestamp?