Thread: Default to TIMESTAMP WITH TIME ZONE?
I heard the moan about "Why doesn't TIMESTAMP mean TIMESTAMP WITH TIME ZONE" again today, so here is something concrete to address that. AFAIK, SQL Standard requires the default to be WITHOUT TIME ZONE, but nobody seems to think that is useful. We even added a specially optimized ALTER TABLE command to make switching from WITHOUT to WITH TIME ZONE easy, so it is clearly an important thing to solve. So add a parameter called default_timestamp_with_timezone = off (default) | on Thoughts? -- Simon Riggs http://www.EnterpriseDB.com/
Attachment
On Thu, Aug 12, 2021 at 07:24:58PM +0100, Simon Riggs wrote: > I heard the moan about "Why doesn't TIMESTAMP mean TIMESTAMP WITH TIME > ZONE" again today, so here is something concrete to address that. > > AFAIK, SQL Standard requires the default to be WITHOUT TIME ZONE, but > nobody seems to think that is useful. We even added a specially > optimized ALTER TABLE command to make switching from WITHOUT to WITH > TIME ZONE easy, so it is clearly an important thing to solve. > > So add a parameter called > default_timestamp_with_timezone = off (default) | on I thought we found that changing behavior via GUC usually ends badly. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Bruce Momjian <bruce@momjian.us> writes:
> On Thu, Aug 12, 2021 at 07:24:58PM +0100, Simon Riggs wrote:
>> I heard the moan about "Why doesn't TIMESTAMP mean TIMESTAMP WITH TIME
>> ZONE" again today, so here is something concrete to address that.
>>
>> AFAIK, SQL Standard requires the default to be WITHOUT TIME ZONE, but
>> nobody seems to think that is useful.
Standards compliance is useful, no? If it isn't, there are about 1001
other things nobody much likes about SQL.
>> We even added a specially
>> optimized ALTER TABLE command to make switching from WITHOUT to WITH
>> TIME ZONE easy, so it is clearly an important thing to solve.
>>
>> So add a parameter called
>> default_timestamp_with_timezone = off (default) | on
> I thought we found that changing behavior via GUC usually ends badly.
Yeah. Changing from SQL-spec to not-SQL-spec behavior is going to be
one tough sell to begin with, even without the point that that's been
our behavior for over two decades. But proposing to do it via a GUC
is just not-even-worth-discussing territory. That would force every
wannabe-portable program to cope with both behaviors; which would
end up meaning that not only do you still have to take care to write
WITH TIME ZONE when you want that, but *also* you'd have to be sure
to write WITHOUT TIME ZONE when you want that. In short, the worst
of both worlds.
regards, tom lane
On Thu, 12 Aug 2021 at 20:07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > On Thu, Aug 12, 2021 at 07:24:58PM +0100, Simon Riggs wrote: > >> I heard the moan about "Why doesn't TIMESTAMP mean TIMESTAMP WITH TIME > >> ZONE" again today, so here is something concrete to address that. > >> > >> AFAIK, SQL Standard requires the default to be WITHOUT TIME ZONE, but > >> nobody seems to think that is useful. > > Standards compliance is useful, no? If it isn't, there are about 1001 > other things nobody much likes about SQL. > > >> We even added a specially > >> optimized ALTER TABLE command to make switching from WITHOUT to WITH > >> TIME ZONE easy, so it is clearly an important thing to solve. > >> > >> So add a parameter called > >> default_timestamp_with_timezone = off (default) | on > > > I thought we found that changing behavior via GUC usually ends badly. > > Yeah. Changing from SQL-spec to not-SQL-spec behavior is going to be > one tough sell to begin with, even without the point that that's been > our behavior for over two decades. But proposing to do it via a GUC > is just not-even-worth-discussing territory. That would force every > wannabe-portable program to cope with both behaviors; which would > end up meaning that not only do you still have to take care to write > WITH TIME ZONE when you want that, but *also* you'd have to be sure > to write WITHOUT TIME ZONE when you want that. In short, the worst > of both worlds. All of which I agree with, but this wasn't a cute idea of mine, this was what our users have requested because of the extreme annoyance caused by the current behavior. -- Simon Riggs http://www.EnterpriseDB.com/
On Fri, Aug 13, 2021 at 12:25:00AM +0100, Simon Riggs wrote: > > > I thought we found that changing behavior via GUC usually ends badly. > > > > Yeah. Changing from SQL-spec to not-SQL-spec behavior is going to be > > one tough sell to begin with, even without the point that that's been > > our behavior for over two decades. But proposing to do it via a GUC > > is just not-even-worth-discussing territory. That would force every > > wannabe-portable program to cope with both behaviors; which would > > end up meaning that not only do you still have to take care to write > > WITH TIME ZONE when you want that, but *also* you'd have to be sure > > to write WITHOUT TIME ZONE when you want that. In short, the worst > > of both worlds. > > All of which I agree with, but this wasn't a cute idea of mine, this > was what our users have requested because of the extreme annoyance > caused by the current behavior. Understood, but the problem is that our users sometimes don't think through the ramifications of their suggestions. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
An idea just occurred to me: a systematic way to provide this feature might be to support aliases for objects. So I could declare an alternate name for an object, something like:
CREATE ALIAS timestamp FOR TYPE timestamptz;
Which would mean that [current schema].timestamp would now be an alternate name for the built-in timestamptz object. There are other situations in which being able to define aliases would be handy, including schema migrations and probably other compatibility scenarios.
Of course I'm aware that this idea itself would need a lot of discussion and I'm not volunteering to implement it right now, but it might be a workable approach if aliases ever become a feature.
I think having a GUC to change to a different set of semantics is not workable. However that doesn't mean we can't do anything. We could have a GUC that just disables allowing creating columns of type timestamp without tz. That could print an error with a hint suggesting you can change the variable if you really want to allow them. I still would hesitate to make it the default but admins could set it to help their developers. You could maybe imagine a set of parameters to disable various options that can be seen as undesirable features that sites may not want their developers accidentally introducing. That said, even disabling a feature is a semantic change. Consider for example an extension that did make use of some feature. To be portable and reliable it would have to start with a block of temporary GUC settings to ensure it worked properly on user databases where various settings may be in place. But that's a lot easier to manage than subtle behavioural changes.
On 13/08/21 5:14 pm, Greg Stark wrote: > I think having a GUC to change to a different set of semantics is not workable. > > However that doesn't mean we can't do anything. We could have a GUC > that just disables allowing creating columns of type timestamp without > tz. That could print an error with a hint suggesting you can change > the variable if you really want to allow them. > > I still would hesitate to make it the default but admins could set it > to help their developers. > [...] I always use the tz version, except when I forget. Initially, I was totally unaware of the need & usefulness of storing time in tz. So I would use the GUC. Suspect that it is extremely rare when one would not want to use the TZ option, which suggests to me that using TZ should be the default. Cheers, Gavin
On Fri, Aug 13, 2021 at 12:33 AM Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
I always use the tz version, except when I forget.
I find it nearly impossible for me to forget how this works. But that is probably because I just pretend that the standard, multi-word, data types don't even exist. It's not that "timestamp" defaults to "WITHOUT TIME ZONE" but rather that there are only two types in existence: timestamp and timestamptz. It's self-evident which one doesn't handle time zones.
Suspect that it is extremely rare when one would not want to use the TZ
option, which suggests to me that using TZ should be the default.
I would agree, but at this point I'd leave it up to documentation and educational materials to teach/encourage, not runtime behaviors.
David J.
On Fri, Aug 13, 2021 at 07:09:15AM -0700, David G. Johnston wrote: > On Fri, Aug 13, 2021 at 12:33 AM Gavin Flower <GavinFlower@archidevsys.co.nz> > wrote: > > > I always use the tz version, except when I forget. > > > I find it nearly impossible for me to forget how this works. But that is > probably because I just pretend that the standard, multi-word, data types don't > even exist. It's not that "timestamp" defaults to "WITHOUT TIME ZONE" but > rather that there are only two types in existence: timestamp and timestamptz. > It's self-evident which one doesn't handle time zones. > > > > Suspect that it is extremely rare when one would not want to use the TZ > option, which suggests to me that using TZ should be the default. > > > > I would agree, but at this point I'd leave it up to documentation and > educational materials to teach/encourage, not runtime behaviors. Yes, only using 'timetamptz' does fix it. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
On 8/12/21 7:25 PM, Simon Riggs wrote: > I thought we found that changing behavior via GUC usually ends badly. >> Yeah. Changing from SQL-spec to not-SQL-spec behavior is going to be >> one tough sell to begin with, even without the point that that's been >> our behavior for over two decades. But proposing to do it via a GUC >> is just not-even-worth-discussing territory. That would force every >> wannabe-portable program to cope with both behaviors; which would >> end up meaning that not only do you still have to take care to write >> WITH TIME ZONE when you want that, but *also* you'd have to be sure >> to write WITHOUT TIME ZONE when you want that. In short, the worst >> of both worlds. > All of which I agree with, but this wasn't a cute idea of mine, this > was what our users have requested because of the extreme annoyance > caused by the current behavior. > What do other DBMSs do? This strikes me as primarily an education issue (I did a webinar on it not that long ago) If you want to protect against people using tz-less timestamp, maybe an event trigger would be a solution, although maybe that's using a sledgehammer to crack a nut. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Fri, 13 Aug 2021 at 17:23, Andrew Dunstan <andrew@dunslane.net> wrote: > What do other DBMSs do? I think MySQL defaults to WITH TIME ZONE, not sure, but I would bet a few others follow the standard. > This strikes me as primarily an education issue > (I did a webinar on it not that long ago) Yes, agreed. > If you want to protect against people using tz-less timestamp, maybe an > event trigger would be a solution, although maybe that's using a > sledgehammer to crack a nut. If you know about the issue, I guess you fix it in lots of different ways. The issue is about those that don't know and my patch didn't help them either. The only hope is to eventually change the default, so probably the best thing is to apply pressure via the SQL Std process. -- Simon Riggs http://www.EnterpriseDB.com/
On Fri, Aug 13, 2021 at 9:28 AM Simon Riggs <simon.riggs@enterprisedb.com> wrote:
The only hope is to eventually change the default, so probably
the best thing is to apply pressure via the SQL Std process.
Then there is no hope because this makes the situation worse.
If anything I'd suggest the SQL standard should probably just admit this "default behavior of timestamp" is a bad idea and deprecate its existence. IOW, the only two standard conforming syntaxes are the explicit WITH/WITHOUT TIME ZONE ones. Any database implementation that implements "timestamp" as a type alias is doing so in an implementation dependent way. Code that wants to be SQL standard conforming portable needs to use the explicit types.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Aug 13, 2021 at 9:28 AM Simon Riggs <simon.riggs@enterprisedb.com>
> wrote:
>> The only hope is to eventually change the default, so probably
>> the best thing is to apply pressure via the SQL Std process.
> Then there is no hope because this makes the situation worse.
Agreed; the points I made upthread are just as valid if the change
is made in the standard. But I'd be astonished if the SQL committee
would consider such a change anyway.
The one thing I could potentially see us doing is more strongly
encouraging the use of the names "timestamp" and "timestamptz",
up to and including changing what format_type() et al. put out.
Yeah, "timestamptz" is not standard, but so what? At least it's
not actually *contrary* to the standard, as the original proposal
here is. (Also, while I hate to bring it up in this context,
our timestamptz data type is not really compatible with the spec
in the first place, so that a case could be made that this behavior
is more honest/spec-compatible than what we do today.)
If you wanted to be even more in people's faces about it, you
could print the type names as "timestamptz" and "timestamp
without time zone".
regards, tom lane
On Friday, August 13, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The one thing I could potentially see us doing is more strongly
encouraging the use of the names "timestamp" and "timestamptz",
up to and including changing what format_type() et al. put out.
+1. Having the canonical form be timestamptz would make pretending the “with time zone” version doesn’t exist much easier.
David J.
On 13.08.21 19:07, Tom Lane wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: >> On Fri, Aug 13, 2021 at 9:28 AM Simon Riggs <simon.riggs@enterprisedb.com> >> wrote: >>> The only hope is to eventually change the default, so probably >>> the best thing is to apply pressure via the SQL Std process. > >> Then there is no hope because this makes the situation worse. > > Agreed; the points I made upthread are just as valid if the change > is made in the standard. But I'd be astonished if the SQL committee > would consider such a change anyway. AFAIU, our timestamp with time zone type doesn't really do what the SQL standard specifies anyway, as it doesn't actually record the time zone, but it's more of a "timestamp with time zone aware formatting". For SQL, it might make sense to add a (third) time stamp type that behaves more like that.
On Sat, 14 Aug 2021 at 09:03, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > > On 13.08.21 19:07, Tom Lane wrote: > > "David G. Johnston" <david.g.johnston@gmail.com> writes: > >> On Fri, Aug 13, 2021 at 9:28 AM Simon Riggs <simon.riggs@enterprisedb.com> > >> wrote: > >>> The only hope is to eventually change the default, so probably > >>> the best thing is to apply pressure via the SQL Std process. > > > >> Then there is no hope because this makes the situation worse. > > > > Agreed; the points I made upthread are just as valid if the change > > is made in the standard. But I'd be astonished if the SQL committee > > would consider such a change anyway. > > AFAIU, our timestamp with time zone type doesn't really do what the SQL > standard specifies anyway, as it doesn't actually record the time zone, > but it's more of a "timestamp with time zone aware formatting". For > SQL, it might make sense to add a (third) time stamp type that behaves > more like that. Hmm, a new datatype would make sense, but I would go in the direction of usability, since that's where this thread started. It would also make sense to have a type called timestampUTC, where 1. all inputs that specify a timezone are converted to UTC 2. all inputs that do not specify a timezone are assumed to be UTC, ignoring the setting of time zone 3. output is not affected by the session time zone, so wherever you look at it from, you see UTC values This allows the UTC Everywhere design pattern and also ensures that all functions are immutable, so will not cause optimization problems. This is useful because using TIMESTAMP WITHOUT TIME ZONE for UTC Everywhere only works if nobody ever sets their time zone, which every user can do. -- Simon Riggs http://www.EnterpriseDB.com/
On Sat, Aug 14, 2021 at 10:03:01AM +0200, Peter Eisentraut wrote: > On 13.08.21 19:07, Tom Lane wrote: > > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > > On Fri, Aug 13, 2021 at 9:28 AM Simon Riggs <simon.riggs@enterprisedb.com> > > > wrote: > > > > The only hope is to eventually change the default, so probably > > > > the best thing is to apply pressure via the SQL Std process. > > > > > Then there is no hope because this makes the situation worse. > > > > Agreed; the points I made upthread are just as valid if the change > > is made in the standard. But I'd be astonished if the SQL > > committee would consider such a change anyway. > > AFAIU, our timestamp with time zone type doesn't really do what the > SQL standard specifies anyway, as it doesn't actually record the > time zone, but it's more of a "timestamp with time zone aware > formatting". For SQL, it might make sense to add a (third) time > stamp type that behaves more like that. The way the standard defines time zones, namely as fix offsets from UTC, is just ludicrous and has been baseless in fact much longer than SQL has existed. If we're going to capture input time zones, we should come up with a way to capture the time zone as it existed when the write occurred, i.e. both its name and the UTC offset it represented at that time of the write. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate