Re: Fwd: Re: Dynamic update of a date field

On 02/16/2012 02:45 PM, John R Pierce wrote:
> On 02/16/12 2:34 PM, David Salisbury wrote:
>>
>> Would it not be advantageous to replicate information in the above
>> form if you wanted to, say, get all records in the month of May, and
>> therefore create an index on the month field? I would think that
>> would be more efficient than creating a functional index on a timestamp.
>> And if you're not too picky, that would give an index that couldn't
>> be done on a timestamptz field, as that's mutable.
>
> an index on a timestamp will work just fine on date_trunc('month',
> timestampfield)
>
>
>
You can try but PostgreSQL will respond:
...functions in index expression must be marked IMMUTABLE...

The current month returned by extract or date_trunc depends on the
current time zone. New York will see March 3-hours ahead of us
left-coasters.

Note: storing the month in a separate field does not solve this problem
- it just shuffles it around and requires additional mechanisms to
update that field when the timestamp field changes.

You can, if it is appropriate to the situation, specify the time zone in
which you are interested:
... (date_trunc('month', timestampfield at time zone
'posix/America/Los_Angeles') ) ...

Cheers,
Steve