Re: Quick Date/Time Index Question

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 <, such as

WHERE field >= date0 AND field < date0 + 1

This would use an index on the timestamp column.

Michael Glaesemann
grzm seespotcode net