Quick Date/Time Index Question

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Quick Date/Time Index Question
Date: 2011-09-22 21:13:34
Message-ID: 004701cc796c$7d9494a0$78bdbde0$@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey,

On 9.0.4

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?

Thanks in advance.

David J.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ben Chobot 2011-09-22 21:22:28 Re: Quick Date/Time Index Question
Previous Message Greg Smith 2011-09-22 20:43:54 Re: Materialized views in Oracle