On Sep 22, 2011, at 17:13, David Johnston wrote:
> Is there some other way to create an index on only the "date" portion of the
> field?
The cast from timestamptz to date is time zone dependent, which is why it's volatile and can't be used indexes.
However,you can do this:
test=# create table timestamps (ts timestamp with time zone primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "timestamps_pkey" for table "timestamps"
CREATE TABLE
test=# create index ts_date_idx on timestamps (cast(ts AS DATE));
ERROR: functions in index expression must be marked IMMUTABLE
test=# create index ts_date_idx on timestamps (cast(ts at time zone 'UTC' AS DATE));
CREATE INDEX
Just decide what time zone you want the index to be in.
> Is it even necessary since any index ordered on timestamp is also,
> by definition, order on date as well?
Another option is to use some other operator rather than =, if you're selecting items, such as BETWEEN or >= and <,
suchas
WHERE field >= date0 AND field < date0 + 1
This would use an index on the timestamp column.
Michael Glaesemann
grzm seespotcode net