Re: Date Index

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?