From: Adam Tauno Williams <awilliam@whitemice.org>
To: pgsql-sql@postgresql.org
Subject: Re: Date Index
Date: Mon, 05 Nov 2012 05:54:47 -0500
Message-ID: <1352112887.4089.1.camel@workstation.wmmi.net> (raw)
In-Reply-To: <20081031074854.GA1015@a-kretschmer.de>
References: <001e01c93ad0$ffcbe190$ff63a4b0$@hansen@brightbuilders.com>
<20081031074854.GA1015@a-kretschmer.de>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: awilliam@whitemice.org
Subject: Re: Date Index
In-Reply-To: <1352112887.4089.1.camel@workstation.wmmi.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox