Re: Why extract( ... from timestamp ) is not immutable?

On 01/25/2012 08:57 AM, hubert depesz lubaczewski wrote:
> On Wed, Jan 25, 2012 at 08:54:44AM -0800, Adrian Klaver wrote:
>>> Personally, I think that extract(epoch from timestamp) should assume
>>> that the timestamp is UTC.
>>
>> What if it isn't?
>
> then you can always correct it with "at time zone 'some specific time
> zone'"

I am going to have to think about this, 'at time zone' makes assumptions
about timestamps depending on the set timezone and whether the timestamp
has a tz or not.

>
> but you can't correct it the other way.
>
>>> Or that there should be a way to do it - by "it" i mean - extract epoch
>>> value from timestamp value in immutable way.
>>
>> Have a timezone value on the timestamp. If the data you are working with is
>> stored as timestamp with time zone then the timestamps represent a point in
>> time.
>
> I do have. But you can't have index on epoch from timestamptz.
> and while you can have iundex on epoch from timestamp, it is not
> correct.

Finally dawned on me. When you use 'at time zone' on a timestamp with tz
it strips the tz which then allows the value to be indexed because:

-[ RECORD 5
]-------+-------------------------------------------------------------------------
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, timestamp without time zone
Type | normal
Volatility | immutable
Owner | postgres
Language | internal
Source code | timestamp_part
Description | extract field from timestamp

>
> depesz
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com