Thread: date/time improvements for 7.2

Accept an INTERVAL argument for SET TIME ZONE per SQL99.Modified the parser and the SET handlers to use full Node
structuresratherthan simply a character string argument.
 
I've implemented and committed changes to improve the feature set for
INTERVAL, as well as making other bug fixes and improvements to other
date/time types. There is one more issue to address as a bug fix
regarding precision and rounding for INTERVAL values for which precision
has been specified (it is possible to pick up some extraneous cruft in
the lsb of the floating point number when rounding).

The CVS log entry is below. initdb required (sorry!) and non-reference
date/time regression tests probably will need to be updated.

All regression tests pass.
                          - Thomas

Implement INTERVAL() YEAR TO MONTH (etc) syntax per SQL99.Does not yet accept the goofy string format that goes along
with,butthat should be easy to add as a bug fix now or feature improvementlater.
 
Accept an INTERVAL argument for SET TIME ZONE per SQL99.Modified the parser and the SET handlers to use full Node
structuresratherthan simply a character string argument.
 
Implement INTERVAL() YEAR TO MONTH (etc) syntax per SQL99.Does not yet accept the goofy string format that goes along
with,butthis should be fairly straight forward to fix now as a bug or lateras a feature.
 
Implement precision for the INTERVAL() type.Use the typmod mechanism for both of INTERVAL features.
Fix the INTERVAL syntax in the parser:opt_interval was in the wrong place.
INTERVAL is now a reserved word, otherwise we get reduce/reduce errors.
Implement an explicit date_part() function for TIMETZ.Should fix coersion problem with INTERVAL reported by Peter E.
Fix up some error messages for date/time types.Use all caps for type names within message.
Fix recently introduced side-effect bug disabling 'epoch' as a
recognizedfield for date_part() etc. Reported by Peter E. (??)
Bump catalog version number.
Rename "microseconds" current transaction time fieldfrom ...Msec to ...Usec. Duh!
date/time regression tests updated for reference platform, but a fewchanges will be necessary for others.

Thomas Lockhart <lockhart@fourpalms.org> writes:
> Implement precision for the INTERVAL() type.
>  Use the typmod mechanism for both of INTERVAL features.

If I could figure out what the typmod of an interval type is defined
to be, I'd fix format_type() to display the type name properly so that
pg_dump would do the right thing.  But it doesn't seem very well
documented as to what the valid values are...

Also:

regression=# create table foo(f1 interval(6));
CREATE
regression=# insert into foo values ('1 hour');
ERROR:  AdjustIntervalForTypmod(): internal coding error

which I think is because
    if (range == MASK(YEAR))

should be
    else if (range == MASK(YEAR))

at line 384 of timestamp.c.

Also, you're going to have some problems with your plan to make
0xFFFF in the high bits mean "no range, but maybe a precision",
because there are a number of places that think that any typmod < 0
is a dummy.  I would strongly suggest that you arrange the coding
of interval's typmod to follow that convention, rather than assume
you can ignore it.  Perhaps use 0x7FFF (or zero...) to mean "no range",
and make sure none of the bits that are used are the sign bit?
        regards, tom lane

> > Implement precision for the INTERVAL() type.
> >  Use the typmod mechanism for both of INTERVAL features.
> If I could figure out what the typmod of an interval type is defined
> to be, I'd fix format_type() to display the type name properly so that
> pg_dump would do the right thing.  But it doesn't seem very well
> documented as to what the valid values are...

I tried to follow what seemed to be the conventions of the numeric data
type in putting the "precision" in the low 16 bits. 0xFFFF implies
"unspecified precision". I reused some existing mask definitions for the
fields within an interval, and plopped those into the high 16 bits, with
0xFFFF << 16 implying that all fields are allowed. So "typmod = -1"
implies behavior compatible with the existing/former feature set.

Not sure *where* this should be documented, since it is used in more
than one place. Suggestions?

> ERROR:  AdjustIntervalForTypmod(): internal coding error

Oops. You found the problem spot; I've got patches...

> Also, you're going to have some problems with your plan to make
> 0xFFFF in the high bits mean "no range, but maybe a precision",
> because there are a number of places that think that any typmod < 0
> is a dummy.  I would strongly suggest that you arrange the coding
> of interval's typmod to follow that convention, rather than assume
> you can ignore it.  Perhaps use 0x7FFF (or zero...) to mean "no range",
> and make sure none of the bits that are used are the sign bit?

What exactly does "is a dummy" mean? (outside of possible personal
opinions ;) Are there places which decline to call a "normalization
routine" if typmod is less than zero, rather than equal to -1? I didn't
notice an effect such as that in my (limited) testing.

btw, in changing the convention to use 0x7FFF rather than 0xFFFF, I
found another bug, where I transposed the two subfields for one case in
gram.y. Will also be fixed.
                      - Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:
> Are there places which decline to call a "normalization
> routine" if typmod is less than zero, rather than equal to -1?

The format_type routines think that typmod < 0 means "no typmod
specified".  I am not sure where else this may be true, but I'm
pretty sure that that behavior was copied from elsewhere.  We could
try to tighten up the convention to be that only exactly -1 means
"unspecified", but I'm worried about what code we might miss.
        regards, tom lane