Re: [SQL] Bug with Daylight Savings Time & Interval - Mailing list pgsql-hackers

Switched to -hackers from -sql and -bugs.

On Tue, 2002-05-21 at 16:24, Thomas Lockhart wrote:
>
> You can continue to explore the current behavior and to form an opinion
> on what correct behavior should be. I've resisted adding fields to the
> internal interval type for performance and design reasons. As previously
> mentioned, blind verbatim compliance with SQL9x may suggest breaking our
> INTERVAL type into a bunch of pieces corresponding to the different
> interval ranges specified in the standard. However, the SQL standard is
> choosing to cover a small subset of common usage to avoid dealing with
> the implementation complexities and usage patterns which are uncovered
> when trying to do more.

It's worth pointing out that the same syntax is in SQL92, so I conclude
that no one could think how to improve it through a seven year period.

I don't want to dispose of the existing INTERVAL type, but I would like
the functionality offered by the SQL99 types.  For example, I want to be
able to use INTERVAL HOUR(3) TO MINUTE to record the time taken by some
industrial process and I don't want '125 hours 15 minutes' converted
into '5 days 05:15'.

You talk of breaking interval into a number of pieces, but I don't see
the need.  You have already implemented half of what is needed.  The
other part needed is to record the leading field precision, which we can
surely do in typmod, where you already store the fractional precision.
At present you have in AdjustIntervalForTypmod():
   int range = ((typmod >> 16) & 0x7FFF);   int precision = (typmod & 0xFFFF);

and since precision is limited to the range 0-6, we should certainly be
able to fit the leading field precision into the same space:
   int frac_precision = (typmod & 0xFF);         /* default is 6 */   int lead_precision = ((typmod >> 8) & 0xFF);  /*
defaultis 2 */ 

all that is left is a set of rules to validate input and to format
output according to the given precision, and to change the parser
slightly to get the SQL99 syntax right..

Now I'm sure I'm oversimplifying, but where?


As to other common usage, I can see benefits in extending the subtypes
to include WEEK, and this is conceptually merely an extension of the
existing SQL99 DAY TO SECOND type.  What other usage do you see that can
reasonably be translated from fuzzy human talk into solid data?  Years
and months are already handled and can be used meaningfully.  What you
can't do in SQL99 is translate from exact INTERVAL DAY TO SECOND to
fuzzy INTERVAL YEAR TO MONTH.  I can't see why one should want to, but
if you do, our existing type system would let us cast INTERVAL DAY TO
SECOND to INTERVAL, which already does this in a satisfactorily fuzzy
way.  I can even conceive of doing the conversion using a configured
choice out of a set of fuzzy conversion options.  For example: configure
year to be 360, 365 or 365.2425 days; configure month to be year/12 or
30 days or 4 weeks; and so on.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "We are troubled on every side, yet not distressed; we      are perplexed, but not in despair; persecuted, but not
  forsaken; cast down, but not destroyed; Always bearing     about in the body the dying of the Lord Jesus, that
thelife also of Jesus might be made manifest in our      body."        II Corinthians 4:8-10