On Sat, 19 Mar 2005, Roy Badami wrote:

Hmm, I'm not entirely sure what the spec says about this. I think the
covering clause in SQL92 is 6.10 (<cast specification>) GR 12d. The error
definition appears to be:


d) If SD is interval and TD and SD have different interval pre-
cisions, then let Q be the least significant <datetime field>
of TD.
i) Let Y be the result of converting SV to a scalar in units Q
according to the natural rules for intervals as defined in the
Gregorian calendar.

ii) Normalize Y to conform to the datetime qualifier "P TO Q"
of TD. If this would result in loss of precision of the
leading datetime field of Y, then an exception condition is
raised: data exception-interval field overflow.

And SQL 99 seems to add a sentence saying "whether to truncate or round in
the least significant field of the result is implementation-defined."

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom> In order to support the spec *exactly*, we would have to.
Tom> For instance we cannot presently tell the difference between
Tom> '13 months' and '1 year 1 month' ... they both end up stored
Tom> as '13 months'. I can't say that I find this very important,
Tom> but it does put limits on how exactly we can emulate the
Tom> spec.

Which is where my comment about EXTRACT comes in. They can both be
stored as 13 months, but EXTRACT (MONTH FROM ...) should return 1
month or 13 months as appropriate. Surely this isn't a problem, you
know the type of the interval?

So you _can_ emulate the spec, you just don't use the same internal
representation that a naive implementation of the spec would...

-roy

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Roy Badami wrote:
What happens if you store '13 months' into an interval column that is
YEAR TO MONTH? Does extract MONTH return 1 or 13?

Right now we return one:

test=> select extract(month from interval '13 months' year to month);
date_part
-----------
1
(1 row)

but this seems strange:

test=> select extract(month from interval '13 months' month);
date_part
-----------
1
(1 row)

It is because it is really '1 year 1 month':

test=> select interval '13 months' as month;
month
--------------
1 year 1 mon
(1 row)

What I would really like to avoid is having a different internal
representation for ANSI and PG interval values.

The lack of complaints all these years perhaps means people either don't
care or accept the PG behavior.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Bruce> What happens if you store '13 months' into an interval
Bruce> column that is YEAR TO MONTH? Does extract MONTH return 1
Bruce> or 13?

In standard SQL the MONTH field of INTERVAL YEAR TO MONTH can't
contain a value greater than 11. Though I don't immediately see how
you'd go about storing 13 in the month field. I don't think there's
an analogue of EXTRACT that allows you to set fields, is there?

Bruce> The lack of complaints all these years perhaps means people
Bruce> either don't care or accept the PG behavior.

To be honest, I don't really care :-)

I try to write my SQL in as standard a way as possible, in case I
later want to port to another database...

I would be perfectly happy for

INTERVAL '1' MONTH

to be a syntax error. I just don't like the fact that it gives me a
zero interval.

Taking out the ISO support from the parser is a valid fix as far as
I'm concerned (though actually making it do the ISO thing would
obviously be nicer)

-roy


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org