Thread: Quick Date/Time Index Question

"David Johnston" <polobo@yahoo.com> wrote:

> I have a database field that stores a timestamp to second+ precision;
> however, I want to search against it only to day precision.  If I leave the
> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I
> get no results (OK, fine) but then I cast the field to date "WHERE
> field::date BETWEEN date0 AND date0" and get the expected results.  So now I
> want to index "field::date" by I cannot create a functional index on
> "field::date" OR "CAST(field AS date)" OR "date_trunc('day',field)" due to
> either syntax (::) or non-IMMUTABLE function errors (cast; date_trunc).

> Is there some other way to create an index on only the "date" portion of the
> field?  Is it even necessary since any index ordered on timestamp is also,
> by definition, order on date as well?

Not necessarily a direct answer, but have you tried querying
"WHERE field >= date0 AND field < (date0 + 1)"?

Tim

"David Johnston" <polobo@yahoo.com> writes:
> I have a database field that stores a timestamp to second+ precision;
> however, I want to search against it only to day precision.  If I leave the
> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I
> get no results (OK, fine) but then I cast the field to date "WHERE
> field::date BETWEEN date0 AND date0" and get the expected results.

Try "WHERE field BETWEEN date0 AND date0+1".  When comparing a date to a
timestamp, the date is considered to represent midnight of its day, so
you're testing for a zero-width range there.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> I have a database field that stores a timestamp to second+ precision;
>> however, I want to search against it only to day precision.  If I leave the
>> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I
>> get no results (OK, fine) but then I cast the field to date "WHERE
>> field::date BETWEEN date0 AND date0" and get the expected results.

> Try "WHERE field BETWEEN date0 AND date0+1".  When comparing a date to a
> timestamp, the date is considered to represent midnight of its day, so
> you're testing for a zero-width range there.

Dare I to say it? :-) Not quite true:

| tim=# SELECT t
| tim-#        FROM (VALUES ('2011-01-01 00:00:00'::TIMESTAMP),
| tim(#                     ('2011-01-02 00:00:00'::TIMESTAMP)) AS d (t)
| tim-#        WHERE t BETWEEN '2011-01-01'::DATE AND ('2011-01-01'::DATE + 1);
|           t
| ---------------------
|  2011-01-01 00:00:00
|  2011-01-02 00:00:00
| (2 Zeilen)

| tim=#

So you would have to assert that no timestamp will ever fall
on midnight.

Tim

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