From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Revisiting extract(epoch from timestamp) |
Date: | 2012-04-09 17:43:15 |
Message-ID: | CA+Tgmob_jU0q=rPEeiMGxkamR7gLBjXY2BXXY=XRm1YLv5g8SQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2012-04-09 17:47:15 | Re: Revisiting extract(epoch from timestamp) |
Previous Message | Tom Lane | 2012-04-09 17:38:37 | Re: why was the VAR 'optind' never changed in initdb? |