Re: Revisiting extract(epoch from timestamp)

Excerpts from Tom Lane's message of lun abr 09 15:04:10 -0300 2012:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mon, Apr 9, 2012 at 1:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
> >> The above-linked discussion also brings up a different point, which is
> >> that extracting the epoch from a timestamptz is an immutable operation,
> >> but because it's provided in the context of timestamptz_part we can only
> >> mark it stable. (That is correct because the other cases depend on the
> >> timezone setting ... but epoch doesn't.) It seems like it might be
> >> worth providing a single-purpose function equivalent to extract(epoch),
> >> so that we could mark it immutable. On the other hand, it's not
> >> entirely apparent why people would need to create indexes on the epoch
> >> value rather than just indexing the timestamp itself, so I'm a tad less
> >> excited about this angle of it.
>
> > If somebody needs it I'd probably be in favor of doing it. I'm not
> > sure I'd do it on spec.
>
> Hmm, I thought depesz was asking for such a function here:
> http://archives.postgresql.org/pgsql-hackers/2012-01/msg01690.php
> but now that I look more closely, he may have just meant that as an
> alternative to touching the existing behavior of timestamp_part.
> But providing a new function wouldn't be enough to solve the problem
> that timestamp_part's immutability marking is wrong.

It would be useful to have a simple function to use with timestamp in
constraint exclusion without having to use contorted expressions ...
An immutable extract_epoch(timestamptz) would fit the bill.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support