Thread: Date/time types: big change
I've just committed changes to "reunify" the date/time types.
"timestamp" and "interval" are now the two primary date/time types for
users. Also, I've changed the default date style to "ISO" (not just in
time for Y2K, but we'll be ready for "Y3K").
Also, I made some changes to have NUMERIC be a "known" type for
purposes of implicit type coersion, but have not tested to see if the
underlying conversion functions are available.
initdb required (and enforced by a catalog version change).
Regression tests pass, except for the rules test due to ongoing rules
formatting work.
- Thomas
The detailed change log:
Make NUMERIC a known native type for purposes of type coersion. Not
tested.
Make ISO date style (e.g. "2000-02-16 09:33") the default.
Implement "date/time grand unification".Transform datetime and timespan into timestamp and interval.Deprecate datetime
andtimespan, though translate to new types in
gram.y.Transform all datetime and timespan catalog entries into new types.Make "INTERVAL" reserved word allowed as a
columnidentifier in
gram.y.Remove dt.h, dt.c files, and retarget datetime.h, datetime.c as
utility routines for all date/time types.date.{h,c} now deals with date, time types.timestamp.{h,c} now deals with
timestamp,interval types.nabstime.{h,c} now deals with abstime, reltime, tinterval types.
All regression tests pass except for rules.sql (unrelated).
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Wed, 16 Feb 2000, Thomas Lockhart wrote: > I've just committed changes to "reunify" the date/time types. > "timestamp" and "interval" are now the two primary date/time types for > users. Also, I've changed the default date style to "ISO" (not just in > time for Y2K, but we'll be ready for "Y3K"). I still don't like our Y2038 status. ;) Anyway, the question I have is what did you do with functions such as datetimein() or comparison functions and such for the old types? Did you remove them? What if some, say, user-defined trigger function uses them? The reason I'm asking is that I would like to see the floating point types converted to SQL in a similar fashion, but when I rename, say, float4eq to realeq it might break user applications. Or not? This is all hypothetical of course. -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> I've just committed changes to "reunify" the date/time types.
> "timestamp" and "interval" are now the two primary date/time types for
> users. Also, I've changed the default date style to "ISO" (not just in
> time for Y2K, but we'll be ready for "Y3K").
>
I think we need a consensus on this. I think this may be a problem for
some people. Comments?
test=> create table x ( y date);CREATEtest=> insert into x values ('02/01/99');INSERT 18697 1test=> select * from x;
y ------------ 02-01-1999(1 row)test=> set datestyle to 'iso';SET VARIABLEtest=> select * from x; y
------------1999-02-01(1 row)
-- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Also, I've changed the default date style to "ISO" (not just in
>> time for Y2K, but we'll be ready for "Y3K").
> I think we need a consensus on this. I think this may be a problem for
> some people. Comments?
Good point. Perhaps there should be a way to select the default date
style at configure or initdb time. I don't mind if the "default default"
is ISO, but if I had apps that were dependent on the old default setting
I'd sure be annoyed by this change...
Has anyone thought much about the fact that beginning next year,
heuristics to guess which field is the year will become nearly useless?
Quick, when is '01/02/03'? I suspect a lot of people who got away with
not thinking hard about datestyles will suddenly realize that they need
to set the default datestyle to whatever they are accustomed to using.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Also, I've changed the default date style to "ISO" (not just in > >> time for Y2K, but we'll be ready for "Y3K"). > > > I think we need a consensus on this. I think this may be a problem for > > some people. Comments? > > Good point. Perhaps there should be a way to select the default date > style at configure or initdb time. I don't mind if the "default default" > is ISO, but if I had apps that were dependent on the old default setting > I'd sure be annoyed by this change... > > Has anyone thought much about the fact that beginning next year, > heuristics to guess which field is the year will become nearly useless? > Quick, when is '01/02/03'? I suspect a lot of people who got away with > not thinking hard about datestyles will suddenly realize that they need > to set the default datestyle to whatever they are accustomed to using. Wow, that is an excellent point. I was doing it for 2000, and was thinking, gee, that's not too hard. I can see it getting much more confusing next year, as you said. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Tom Lane wrote: >Bruce Momjian <pgman@candle.pha.pa.us> writes: >>> Also, I've changed the default date style to "ISO" (not just in >>> time for Y2K, but we'll be ready for "Y3K"). > >> I think we need a consensus on this. I think this may be a problem for >> some people. Comments? > >Good point. Perhaps there should be a way to select the default date >style at configure or initdb time. I don't mind if the "default default" >is ISO, but if I had apps that were dependent on the old default setting >I'd sure be annoyed by this change... > >Has anyone thought much about the fact that beginning next year, >heuristics to guess which field is the year will become nearly useless? >Quick, when is '01/02/03'? I suspect a lot of people who got away with >not thinking hard about datestyles will suddenly realize that they need >to set the default datestyle to whatever they are accustomed to using. I have code to let the installer choose the default datestyle in Debian's installation script for PostgreSQL. It makes itsown best guess on the basis of the timezone and then asks the user with its own guess as the presented default. See the attached script; I don't know how generalisable the timezone guessing would be. Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "But as many as received him, to them gave he power to become the sons of God, even to them that believe on his name" John 1:12
Attachment
> > I've just committed changes to "reunify" the date/time types.
> > "timestamp" and "interval" are now the two primary date/time types for
> > users. Also, I've changed the default date style to "ISO" (not just in
> > time for Y2K, but we'll be ready for "Y3K").
> I still don't like our Y2038 status. ;)
Yeah. Like the ntp rfc doc says: "we'll expect the solution to appear
before it is needed" or something to that effect.
> Anyway, the question I have is what did you do with functions such as
> datetimein() or comparison functions and such for the old types? Did you
> remove them? What if some, say, user-defined trigger function uses them?
Then they are SOL. I had originally implemented datetime and timespan
as an experiment to see if a floating point number could behave well
enough to represent dates (I was worried about rounding and the
.999999 problem, especially with the wide range of platforms we
support).
So it turns out that they work. In the meantime, someone contributed a
timestamp type, but did not fully implement it and chose a 4 byte
representation, which is fundamentally flawed imho.
I've been waiting a year or two to do this upgrade, and the major rev
bump is the time and place to do it.
One reason why I didn't carry along both datetime *and* timestamp is
the large number of related functions and operators. It would have
significantly increased the size of the catalogs (mostly because
timestamp didn't have much to start with).
> The reason I'm asking is that I would like to see the floating point types
> converted to SQL in a similar fashion, but when I rename, say, float4eq to
> realeq it might break user applications. Or not? This is all hypothetical
> of course.
Lots of work for not much gain imho. For the date/time stuff, it made
sense because timestamp needed to be replaced. There isn't the same
underlying need for the floating point types afaik.
On the other hand, 7.0 (or 8.0, but that may be another 4 years ;) is
the time to do it. Does anyone else see this as an issue?
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> >> Also, I've changed the default date style to "ISO" (not just in
> >> time for Y2K, but we'll be ready for "Y3K").
> > I think we need a consensus on this. I think this may be a problem for
> > some people. Comments?
> Good point. Perhaps there should be a way to select the default date
> style at configure or initdb time. I don't mind if the "default default"
> is ISO, but if I had apps that were dependent on the old default setting
> I'd sure be annoyed by this change...
I've been talking about this for quite some time, but there *really*
is no excuse to not go to the ISO date/time standard. Every other date
style is prone to misinterpretation, and the ISO standard is commonly
used in other instances where reliable date reporting is needed.
I've waited until a major rev to do this, and the groundwork has been
there for a year or two. There are some good summaries of the issues
on the web.
But, I'd have no objection to a configure or initdb option; I *would*
suggest that the old default (and it is the default mostly because
original Postgres95 had no other styles implemented) is a relatively
poor choice, and that ISO should be the default choice in the absence
of an explicit configure or initdb switch.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> But, I'd have no objection to a configure or initdb option; I *would*
> suggest that the old default (and it is the default mostly because
> original Postgres95 had no other styles implemented) is a relatively
> poor choice, and that ISO should be the default choice in the absence
> of an explicit configure or initdb switch.
As I said, I have no objection to making ISO the new "standard default";
I just think some people will need a way to change the default in their
installations.
regards, tom lane
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> The reason I'm asking is that I would like to see the floating point types
>> converted to SQL in a similar fashion, but when I rename, say, float4eq to
>> realeq it might break user applications. Or not? This is all hypothetical
>> of course.
> Lots of work for not much gain imho. For the date/time stuff, it made
> sense because timestamp needed to be replaced. There isn't the same
> underlying need for the floating point types afaik.
> On the other hand, 7.0 (or 8.0, but that may be another 4 years ;) is
> the time to do it. Does anyone else see this as an issue?
I think it's too late in the 7.0 cycle to start thinking about renaming
the numeric types. While you implemented the date/time changes at
almost the last minute, the changes had been discussed and agreed to
long ago, and you knew exactly what you needed to do. I don't think
that constitutes a precedent for a hurried revision of the numeric
types...
We've already postponed 7.0 beta twice. Seems to me it's time to
start raising the bar for what we will accept into this revision.
regards, tom lane
On Thu, 17 Feb 2000, Tom Lane wrote: > I think it's too late in the 7.0 cycle to start thinking about renaming > the numeric types. I didn't mean that this should happen now or even soon. It was more of a policy/practice inquiry. -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> On Thu, 17 Feb 2000, Tom Lane wrote:
>> I think it's too late in the 7.0 cycle to start thinking about renaming
>> the numeric types.
> I didn't mean that this should happen now or even soon. It was more of a
> policy/practice inquiry.
OK, fair enough. What I'm thinking at the moment is let's wait and see
how painful or painless the transition is for the date/time types.
The number of squawks we hear about that should give us a clue whether
we want to be in a hurry to rename the numeric types...
regards, tom lane
> I've been talking about this for quite some time, but there *really* > is no excuse to not go to the ISO date/time standard. Every other date > style is prone to misinterpretation, and the ISO standard is commonly > used in other instances where reliable date reporting is needed. > > I've waited until a major rev to do this, and the groundwork has been > there for a year or two. There are some good summaries of the issues > on the web. > > But, I'd have no objection to a configure or initdb option; I *would* > suggest that the old default (and it is the default mostly because > original Postgres95 had no other styles implemented) is a relatively > poor choice, and that ISO should be the default choice in the absence > of an explicit configure or initdb switch. Well, no one is objecting to this yet, so it may be a good choice. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, Feb 16, 2000 at 11:20:26PM +0000, Oliver Elphick wrote: > I have code to let the installer choose the default datestyle in Debian's installation script for PostgreSQL. It makesits own best guess on > the basis of the timezone and then asks the user with its own guess as > the presented default. Yes, Oliver's script works nicely on all Debian machines. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On Thu, Feb 17, 2000 at 06:14:23AM +0000, Thomas Lockhart wrote: > I've been talking about this for quite some time, but there *really* > is no excuse to not go to the ISO date/time standard. Every other date Yes, please let's go to this standard. It's an awful lot of work to fix apps just because they expect US notation and most people in Germany cannot even think about typing it that way. > poor choice, and that ISO should be the default choice in the absence > of an explicit configure or initdb switch. Completely agree. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
> >>> Also, I've changed the default date style to "ISO" (not just in
> >>> time for Y2K, but we'll be ready for "Y3K").
> >... Perhaps there should be a way to select the default date
> >style at configure or initdb time. I don't mind if the "default default"
> >is ISO, but if I had apps that were dependent on the old default setting
> >I'd sure be annoyed by this change...
Well, that is the joy of a major release; not all backward
compatibility is guaranteed. This has been a *documented change* for
at least a year or two; check the chapter on date/time data types for
more info.
However, istm that we could/should have more "default settings"
traveling in the pg_database table. We've got the encoding, which if
set for template1 will be set for every db. We've got the database
location, which can point to an alternate location.
Wouldn't it be reasonable to allow a "default datestyle", or something
more general to help with other defaults? Hmm, could be a text field
which allows something like "PGDATESTYLE='ISO',LANGUAGE='french',..."
so that it is extensible, but maybe that detail is a bad idea because
it is a bit fragile.
What fields would be appropriate for v7.0? The datestyle and timezone
are two obvious candidates, and if we add them now then we could make
use of them later.
Later, we can get things like
ALTER DATABASE SET DEFAULT DATESTYLE='ISO';
etc etc.
For v7.1, I'm hoping to work with Tatsuo and others to get closer to
the general character sets and collation sequences allowed by SQL92.
At that point, the MULTIBYTE hardcoded differences in the backend
might go away and we will need these configurable default values.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> > >>> Also, I've changed the default date style to "ISO" (not just in > > >>> time for Y2K, but we'll be ready for "Y3K"). > > >... Perhaps there should be a way to select the default date > > >style at configure or initdb time. I don't mind if the "default default" > > >is ISO, but if I had apps that were dependent on the old default setting > > >I'd sure be annoyed by this change... > > Well, that is the joy of a major release; not all backward > compatibility is guaranteed. This has been a *documented change* for > at least a year or two; check the chapter on date/time data types for > more info. > > However, istm that we could/should have more "default settings" > traveling in the pg_database table. We've got the encoding, which if > set for template1 will be set for every db. We've got the database > location, which can point to an alternate location. But we have to store this information in the database because it is related to how the data is stored. Do the date/time fields also have that assumption _in_ that stored data? If so, we need it stored in the database, if not, it seems some SET command or psql startup file setting is enough. Many people work on the same database from different locations and may need different settings. I would only store database settings that relate to the data, not how the data is displayed. That stuff belongs outside the database, I think. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
-----------------------------------------------------------------------
On Wed, 16 Feb 2000, Thomas Lockhart wrote:
> I've just committed changes to "reunify" the date/time types.
> "timestamp" and "interval" are now the two primary date/time types for
> users. Also, I've changed the default date style to "ISO" (not just in
> time for Y2K, but we'll be ready for "Y3K").
>
> Also, I made some changes to have NUMERIC be a "known" type for
> purposes of implicit type coersion, but have not tested to see if the
> underlying conversion functions are available.
>
> initdb required (and enforced by a catalog version change).
>
> Regression tests pass, except for the rules test due to ongoing rules
> formatting work.
Great, you fix my formatting code for timestamp. Thanks Thomas!
But conversion timestam to 'tm' struct is not Y2038 ready
(POSIX 'tm' limitation?):
test=# select to_char('Fri Feb 18 11:57:47 2038 CET'::timestamp, 'HH:MI:SS YYYY'); to_char
---------------10:57:47 2038
(1 row)
Or simple:
test=# select 'Fri Feb 18 11:57:47 2038 CET'::timestamp; ?column?
--------------------------Thu Feb 18 10:57:47 2038
(1 row)
Or am I something leave out?
Karel
On Thu, 17 Feb 2000, Thomas Lockhart wrote: > However, istm that we could/should have more "default settings" > traveling in the pg_database table. We've got the encoding, which if > set for template1 will be set for every db. We've got the database > location, which can point to an alternate location. I don't think this should be a per database setting. Why not use an environment variable PGDATESTYLE for it. That's easy enough for now. Before we throw all kinds of per database defaults around, I'd like to see some sort of a concept where exactly a "database" stands versus "schema", etc. What happens if one day queries across databases are allowed? > For v7.1, I'm hoping to work with Tatsuo and others to get closer to > the general character sets and collation sequences allowed by SQL92. Excellent. -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> But conversion timestam to 'tm' struct is not Y2038 ready
> (POSIX 'tm' limitation?):
> to_char
> ---------------
> 10:57:47 2038
> (1 row)
> Or am I something leave out?
No, that is the expected behavior. In most of the world (certainly in
the US), time zones and daylight savings time were both very nebulous
things until around the turn of the century. I recall reading that in
the US building the continental railroads in the 1860's provoked
thinking about standardizing time zone.
There are also minor changes in time zone and DST behavior in recent
history; in the US we had a year or two in the 1970's which ran DST
year round due to the oil shortage.
So, since the actual time zone behavior for years past 2038 is
uncertain, and since the Unix time support routines don't support
anything past 2038 anyway, I omit time zone calculations after
2038-01-18 and before 1901-12-14. Everything is carried as equivalent
to GMT, but no time zone adjustment is carried out.
btw, there *may* be some edge effects which are, um, unexpected; e.g.
having a time zone adjustment as you enter a date w/o an explicit tz
into the database, to which the same adjustment is *not* applied as
the date is read back out. Feel free to test it out...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> I don't think this should be a per database setting. Why not use an
> environment variable PGDATESTYLE for it.
We already have that, and I wouldn't have brought up the issue if I
thought it was sufficient. The case where you really want to be able
to set a default at the database or installation level is when you have
a ton of client apps running on a bunch of different machines, and you
can't or don't want to fix them all at once. A client-side fix doesn't
get the job done for a dbadmin faced with that kind of situation.
Or were you talking about a server-side env variable? That could work
I guess, but I thought you were intent on eliminating env-var
dependencies in initdb and postmaster startup ... for good reasons ...
> Before we throw all kinds of per database defaults around, I'd like to see
> some sort of a concept where exactly a "database" stands versus "schema",
> etc. What happens if one day queries across databases are allowed?
Presumably a client doing that would make sure to request the same
datestyle (or whatever) from each database. You're right though
that we could use some global thinking about what parameters need
to be settable from where, and what their scopes need to be.
regards, tom lane
> Or were you talking about a server-side env variable?
fwiw, we've already got that one...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Or were you talking about a server-side env variable?
> fwiw, we've already got that one...
We do? (... examines code ...) By golly, you're right.
OK, I agree with Peter: this is enough to save the day for a dbadmin
who really really doesn't want to switch to default ISO datestyle,
so I withdraw my complaint.
I do, however, suggest that the backend env var needs to be documented
more prominently than it is now. One might also ask why its set of
allowed values is inconsistent with the SET command's (probably
postgres.c ought to just call a routine in variable.c, rather than
having its own parsing code)?
regards, tom lane
> I do, however, suggest that the backend env var needs to be documented
> more prominently than it is now.
Hmm, I thought it was in the Admin Guide, but in fact it shows up only
in the Data Types chapter and in the release notes. Should be added to
runtime.sgml just before (?) "Starting postmaster".
> One might also ask why its set of
> allowed values is inconsistent with the SET command's (probably
> postgres.c ought to just call a routine in variable.c, rather than
> having its own parsing code)?
I'm vaguely recalling that there was a "chicken and egg" problem with
the backend firing up... Ah, in fact I think (still from my sometimes
faulty memory) that it had to do with whether the Postgres memory
management stuff (palloc et al) was available at the time postgres.c
needed to make the call.
Feel free to review it though and make sweeping or small changes.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On 2000-02-18, Tom Lane mentioned: > Or were you talking about a server-side env variable? That could work > I guess, but I thought you were intent on eliminating env-var > dependencies in initdb and postmaster startup ... for good reasons ... Yes, as you noticed. I don't mind postmaster startup environment variables that much. The ones for initdb were much more evil. This really seems to be an item for the Grand Unified Configuration File, but until that happens it's easier to have a dozen of orthogonal environment variables than having to reorganize this whole thing later on. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> One might also ask why its set of
>> allowed values is inconsistent with the SET command's (probably
>> postgres.c ought to just call a routine in variable.c, rather than
>> having its own parsing code)?
> I'm vaguely recalling that there was a "chicken and egg" problem with
> the backend firing up... Ah, in fact I think (still from my sometimes
> faulty memory) that it had to do with whether the Postgres memory
> management stuff (palloc et al) was available at the time postgres.c
> needed to make the call.
Yup, your memory is still working...
> Feel free to review it though and make sweeping or small changes.
OK, I tweaked the code in variable.c to not depend on palloc(), and
made the change. In the course of doing so, I noticed what I assume
is a bug: RESET DateStyle and SET DateStyle = 'DEFAULT' were still
setting to Postgres style. Presumably they should reset to ISO style
in the brave new world, no?
What I actually did was to make them reset to whatever the backend's
startup setting is. Thus, if a postmaster PGDATESTYLE environment
variable exists, it will determine the result of RESET DateStyle as
well as the state of a new backend. (A client-side PGDATESTYLE setting
cannot affect RESET, of course, since it just causes a SET command to
be issued.) I think this is appropriate behavior, but it might be open
to debate.
BTW, here is an interesting corner case for you: what happens when
the postmaster is started with PGDATESTYLE="DEFAULT"? You get ISO
now, but I almost committed code that would have gone into infinite
recursion...
regards, tom lane