Revisiting extract(epoch from timestamp)

A long time ago, we had this bug report:
http://archives.postgresql.org/pgsql-bugs/2003-02/msg00069.php
in consequence of which, I changed timestamp_part() so that it would
rotate a timestamp-without-timezone from the local timezone to GMT
before extracting the epoch offset (commit
191ef2b407f065544ceed5700e42400857d9270f).

Recent discussion makes it seem like this was a bad idea:
http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
The big problem is that timestamp_part() is marked as immutable, which
is a correct statement for every other field type that it can extract,
but wrong for epoch if that depends on the setting of the timezone GUC.
So if we leave this behavior alone, we're going to have to downgrade
timestamp_part() to stable, which is quite likely to break applications
using it in index expressions. Furthermore, while you could still get
the current behavior by explicitly casting the timestamp to timestamptz
before extracting the epoch, there is currently no convenient way to get
a non-timezone-aware epoch value from a timestamp. Which seems rather
silly given that one point of the timestamp type is to not be timezone
sensitive.

So I'm kind of inclined to revert that old change. Back in the day
we thought it was a relatively insignificant bug fix and applied it in a
minor release, but I think now our standards are higher and we'd want to
treat this as a release-notable incompatibility.

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.

Thoughts?

regards, tom lane

On Mon, Apr 9, 2012 at 1:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> A long time ago, we had this bug report:
> http://archives.postgresql.org/pgsql-bugs/2003-02/msg00069.php
> in consequence of which, I changed timestamp_part() so that it would
> rotate a timestamp-without-timezone from the local timezone to GMT
> before extracting the epoch offset (commit
> 191ef2b407f065544ceed5700e42400857d9270f).
>
> Recent discussion makes it seem like this was a bad idea:
> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
> The big problem is that timestamp_part() is marked as immutable, which
> is a correct statement for every other field type that it can extract,
> but wrong for epoch if that depends on the setting of the timezone GUC.
> So if we leave this behavior alone, we're going to have to downgrade
> timestamp_part() to stable, which is quite likely to break applications
> using it in index expressions.  Furthermore, while you could still get
> the current behavior by explicitly casting the timestamp to timestamptz
> before extracting the epoch, there is currently no convenient way to get
> a non-timezone-aware epoch value from a timestamp.  Which seems rather
> silly given that one point of the timestamp type is to not be timezone
> sensitive.
>
> So I'm kind of inclined to revert that old change.  Back in the day
> we thought it was a relatively insignificant bug fix and applied it in a
> minor release, but I think now our standards are higher and we'd want to
> treat this as a release-notable incompatibility.

+1 to all the above.

> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> 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

Well, it makes for smaller indexes if you don't really care about
sub-second resolutions.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201204091345
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk+DIJcACgkQvJuQZxSWSsiLsQCgrA8Sxcljm+HPJ1jQY7l0u3UZ
UTwAnjBGM7SstLCnihtRkxDJrMax2Ikl
=Kjic
-----END PGP SIGNATURE-----

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.

regards, tom lane

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
>> 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

> Well, it makes for smaller indexes if you don't really care about
> sub-second resolutions.

Well, maybe in principle, but in practice it's an 8-byte value either
way. I guess you could down-convert to an int4 if you plan to be
safely dead before 2038 ...

regards, tom lane

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> If somebody needs it I'd probably be in favor of doing it. I'm not
>>> sure I'd do it on spec.

> 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.

What exactly would you do with it there that you couldn't do more easily
and clearly with plain timestamp comparisons? I'm willing to be
convinced, but I want to see a case where it really is the best way.

regards, tom lane

Excerpts from Tom Lane's message of lun abr 09 15:38:21 -0300 2012:
>
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> >> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >>> If somebody needs it I'd probably be in favor of doing it. I'm not
> >>> sure I'd do it on spec.
>
> > 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.
>
> What exactly would you do with it there that you couldn't do more easily
> and clearly with plain timestamp comparisons? I'm willing to be
> convinced, but I want to see a case where it really is the best way.

You mean, having the constraint declaration rotate the timestamptz
column to timestamp and then extract the epoch from that? If you go
that route, then the queries that wish to take advantage of constraint
exclusion would have to do likewise, which becomes ugly rather quickly.

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Excerpts from Tom Lane's message of lun abr 09 15:38:21 -0300 2012:
>> What exactly would you do with it there that you couldn't do more easily
>> and clearly with plain timestamp comparisons? I'm willing to be
>> convinced, but I want to see a case where it really is the best way.

> You mean, having the constraint declaration rotate the timestamptz
> column to timestamp and then extract the epoch from that? If you go
> that route, then the queries that wish to take advantage of constraint
> exclusion would have to do likewise, which becomes ugly rather quickly.

No, I'm wondering why the partition constraints wouldn't just be

tstzcol >= '2012-04-01 00:00' and tstzcol < '2012-05-01 00:00'

or similar. What sort of constraint have you got in mind that is more
naturally expressed involving extract(epoch)? (And will the planner
think so too?)

regards, tom lane