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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
