Thread: BUG #1518: Conversions to (undocumented) SQL year-month and day-time interval types silently discard data
BUG #1518: Conversions to (undocumented) SQL year-month and day-time interval types silently discard data
From
"Roy Badami"
Date:
The following bug has been logged online: Bug reference: 1518 Logged by: Roy Badami Email address: roy@gnomon.org.uk PostgreSQL version: 8.0.1 Operating system: Solaris 9 Description: Conversions to (undocumented) SQL year-month and day-time interval types silently discard data Details: Conversions to the (undocumented) SQL year-month and day-time intervals silently discard data, instead of raising an exception. Note, the following examples intentinally use non-standard interval syntax, since SQL standard interval syntax appears to be broken... radius=# create table foo (year_month interval year to month); CREATE TABLE radius=# insert into foo values ('1 year 1 month'); INSERT 19963 1 radius=# select * from foo; year_month -------------- 1 year 1 mon (1 row) -- correct radius=# insert into foo values ('1 hour 1 minute'); INSERT 19964 1 -- should be an error, I think? radius=# select * from foo; year_month -------------- 1 year 1 mon 00:00:00 (2 rows) -- but instead the interval has been replaced by a zero interval radius=# create table bar (day_time interval day to second); CREATE TABLE radius=# insert into bar values ('1 hour 1 minute'); INSERT 19968 1 radius=# select * from bar; day_time ---------- 01:01:00 (1 row) -- correct radius=# insert into bar values ('1 year 1 month'); INSERT 19969 1 -- should be an error, I think? radius=# select * from bar; day_time ---------- 01:01:00 00:00:00 (2 rows) -- but instead has been converted to a zero interval
Roy Badami wrote: > > The following bug has been logged online: > > Bug reference: 1518 > Logged by: Roy Badami > Email address: roy@gnomon.org.uk > PostgreSQL version: 8.0.1 > Operating system: Solaris 9 > Description: Conversions to (undocumented) SQL year-month and > day-time interval types silently discard data > Details: I have finally found time to research your issues: > Conversions to the (undocumented) SQL year-month and day-time intervals Yes, I noticed that. Once I outline is behavior we need to revisit that. > silently discard data, instead of raising an exception. Yep, noticed that too. Looking at your examples, it looks terrible, but after researching it, it isn't too bad, so let me lay out the information and we can decide how to handle it. First, the fundamental issue with intervals is that they are not tied to a particular date, meaning there is no way to map a specific number of days to a number of months. (Some days are also 23 or 25 hours but that variability seems to be considered acceptable.) This is why the interval data type store both seconds and months. I ran a few tests using constants, which is clearer: test=> select (current_timestamp - 'epoch'::timestamp)::interval; interval ------------------------------- 12860 days 19:24:13.854829073 (1 row) Notice it shows only days and time, not any years or months because it doesn't actually know how many years or months. > Note, the following examples intentinally use non-standard interval syntax, > since SQL standard interval syntax appears to be broken... > > radius=# create table foo (year_month interval year to month); > CREATE TABLE > radius=# insert into foo values ('1 year 1 month'); > INSERT 19963 1 > radius=# select * from foo; > year_month > -------------- > 1 year 1 mon > (1 row) > > -- correct Should this be "mon" or "month"? > radius=# insert into foo values ('1 hour 1 minute'); > INSERT 19964 1 > > -- should be an error, I think? The problem is that an interval restriction controls storage, but does not invalidate input. The only good way to do that is with CHECK and "date_trunc() != val". In fact, the query below shows that the time information that is outside the requested range is not even stored: test=> select (current_timestamp - 'epoch'::timestamp)::interval year to month::interval; interval ---------- 00:00:00 (1 row) > radius=# select * from foo; > year_month > -------------- > 1 year 1 mon > 00:00:00 > (2 rows) > > -- but instead the interval has been replaced by a zero interval What is confusing here is that instead of printing nothing, it prints a zero time. Should it print something different, perhaps "0 mons"? > radius=# create table bar (day_time interval day to second); > CREATE TABLE > radius=# insert into bar values ('1 hour 1 minute'); > INSERT 19968 1 > radius=# select * from bar; > day_time > ---------- > 01:01:00 > (1 row) > > -- correct > > radius=# insert into bar values ('1 year 1 month'); > INSERT 19969 1 > > -- should be an error, I think? > > radius=# select * from bar; > day_time > ---------- > 01:01:00 > 00:00:00 > (2 rows) > > -- but instead has been converted to a zero interval Again, the interval is zero so it prints zero time. Does this help? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes: Bruce> First, the fundamental issue with intervals is that they Bruce> are not tied to a particular date, meaning there is no way Bruce> to map a specific number of days to a number of months. Bruce> (Some days are also 23 or 25 hours but that variability Bruce> seems to be considered acceptable.) Bruce> This is why the interval data type store both seconds and Bruce> months. Indeed. ANSI SQL regards these as two distinct kinds of interval data types. There are day-time intervals, which store some combination of days, hours, minutes and seconds, and are broadly equivalent to the seconds field in PostgreSQL intervals, and there are year-month intervals, which store some combination of years and months, and are broadly equivalent to the months field in PostgreSQL intervals. PostgreSQL instead implements a single hybrid interval data type, that stores both. This isn't problematic in itself. However, in ANSI SQL no casts exist between day-time intervals and year-month intervals. PostgreSQL effectively implements a cast that always returns a zero interval; it should raise an exception. Actually, it looks like the underlying problem is more basic than all this. Here's an example entirely with year-month intervals. radius=# create table foo (a interval year); CREATE TABLE radius=# insert into foo values (interval '1 year 1 month'); INSERT 20947 1 radius=# select * from foo; a -------- 1 year (1 row) I don't have a copy of the spec, but according to "A guide to the SQL standard" conversions like this that would discard data are supposed to raise an exception. Ok, and how about this one, which is far worse: radius=# create table bar (a interval month); CREATE TABLE radius=# insert into bar values (interval '1 year 1 month'); INSERT 20956 1 radius=# select * from bar; a ------- 1 mon (1 row) The ANSI-compliant answer is 13 months, but PostgreSQL returns 1 month! It seems to me that the ANSI compatible interval stuff should be disabled by default, since it clearly doesn't work yet :-/ Like the ANSI interval literal issue (bug 1517) this is another smoking gun for porters... -roy
Roy> I don't have a copy of the spec, but according to "A guide to Roy> the SQL standard" conversions like this that would discard Roy> data are supposed to raise an exception. Just to clarify, my understanding is that in ANSI SQL it is valid to convert from the data type INTERVAL YEAR TO MONTH to the data type INTERVAL YEAR, but the conversion should raise an exception if the value is not an integral number of years... -roy
On Sat, 19 Mar 2005, Roy Badami wrote: > Roy> I don't have a copy of the spec, but according to "A guide to > Roy> the SQL standard" conversions like this that would discard > Roy> data are supposed to raise an exception. > > Just to clarify, my understanding is that in ANSI SQL it is valid to > convert from the data type INTERVAL YEAR TO MONTH to the data type > INTERVAL YEAR, but the conversion should raise an exception if the > value is not an integral number of years... 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."
OK, here are the TODO items I have created: * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH * Process mixed ANSI/PG INTERVAL syntax, and round value to requested precision Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' Is this sufficient? --------------------------------------------------------------------------- Roy Badami wrote: > > The following bug has been logged online: > > Bug reference: 1518 > Logged by: Roy Badami > Email address: roy@gnomon.org.uk > PostgreSQL version: 8.0.1 > Operating system: Solaris 9 > Description: Conversions to (undocumented) SQL year-month and > day-time interval types silently discard data > Details: > > Conversions to the (undocumented) SQL year-month and day-time intervals > silently discard data, instead of raising an exception. > > Note, the following examples intentinally use non-standard interval syntax, > since SQL standard interval syntax appears to be broken... > > radius=# create table foo (year_month interval year to month); > CREATE TABLE > radius=# insert into foo values ('1 year 1 month'); > INSERT 19963 1 > radius=# select * from foo; > year_month > -------------- > 1 year 1 mon > (1 row) > > -- correct > > radius=# insert into foo values ('1 hour 1 minute'); > INSERT 19964 1 > > -- should be an error, I think? > > radius=# select * from foo; > year_month > -------------- > 1 year 1 mon > 00:00:00 > (2 rows) > > -- but instead the interval has been replaced by a zero interval > > radius=# create table bar (day_time interval day to second); > CREATE TABLE > radius=# insert into bar values ('1 hour 1 minute'); > INSERT 19968 1 > radius=# select * from bar; > day_time > ---------- > 01:01:00 > (1 row) > > -- correct > > radius=# insert into bar values ('1 year 1 month'); > INSERT 19969 1 > > -- should be an error, I think? > > radius=# select * from bar; > day_time > ---------- > 01:01:00 > 00:00:00 > (2 rows) > > -- but instead has been converted to a zero interval > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
Bruce Momjian <pgman@candle.pha.pa.us> writes: > OK, here are the TODO items I have created: > * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND > * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH That example might better read, say, * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > OK, here are the TODO items I have created: > > > * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND > > * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH > > That example might better read, say, > > * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH Uh, I thought about that, but we are talking about an interval here, not a year, so 2005 years seems like a strange number of years to span. I will change it to 9-7 or something so it doesn't match the line above. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tom Lane wrote: >> * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH > Uh, I thought about that, but we are talking about an interval here, not > a year, so 2005 years seems like a strange number of years to span. Oh, right. Never mind ... regards, tom lane
>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes: Bruce> OK, here are the TODO items I have created: Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1 Bruce> 2:03:04' DAY TO SECOND Bruce> * Add support for ANSI date INTERVAL Bruce> syntax, INTERVAL '1-2' YEAR TO MONTH You may as well use the correct ANSI terminology: * Add support for ANSI day-time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND * Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value Bruce> to requested precision Never round, I think. Throwing away precision should be an exception, unless the discarded fields were zero. Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 Bruce> year' AS INTERVAL MONTH), and this should return '12 Bruce> months' Bruce> Is this sufficient? You also need to make EXTRACT do the right thing. eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 but EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 Ditto for day-time intervals, of course. I'll have a think about if there's anything else... -roy
Bruce> Is this sufficient? Also support for ANSI interval data types is incomplete in the parser, in that it doesn't recognize the precision field. eg CREATE TABLE foo (a INTERVAL MONTH(3)); fails to parse. -roy
Roy Badami wrote: > >>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Bruce> OK, here are the TODO items I have created: > > Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1 > Bruce> 2:03:04' DAY TO SECOND > > Bruce> * Add support for ANSI date INTERVAL > Bruce> syntax, INTERVAL '1-2' YEAR TO MONTH > > You may as well use the correct ANSI terminology: > > * Add support for ANSI day-time INTERVAL syntax, INTERVAL '1 > 2:03:04' DAY TO SECOND > > * Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2' > YEAR TO MONTH > > Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value > Bruce> to requested precision OK, I added a new ANSI INTERVAL section to the TODO list: * Add ANSI INTERVAL handling o Add support for day-time syntax, INTERVAL '1 2:03:04' DAY TO SECOND o Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH o Process mixed ANSI/PG syntax, and round value to requested precision or generate an error o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' o Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 minute 30 seconds' > Never round, I think. Throwing away precision should be an exception, > unless the discarded fields were zero. Seems that is implentation-dependent so I added "round' or error" to the TODO item. > Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 > Bruce> year' AS INTERVAL MONTH), and this should return '12 > Bruce> months' > > Bruce> Is this sufficient? > > You also need to make EXTRACT do the right thing. > > eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 > > but > > EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 > > Ditto for day-time intervals, of course. Uh, I think this already works fine for PG syntax, and I assume once we support ANSI syntax it will work fine too: test=> select EXTRACT (MONTH FROM INTERVAL '1 year 2 month' YEAR TO MONTH); date_part ----------- 2 (1 row) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
Roy Badami wrote: > > Bruce> Is this sufficient? > > Also support for ANSI interval data types is incomplete in the parser, > in that it doesn't recognize the precision field. > > eg CREATE TABLE foo (a INTERVAL MONTH(3)); > > fails to parse. Added to TODO: o Support precision, CREATE TABLE foo (a INTERVAL MONTH(3)) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
Bruce> Seems that is implentation-dependent so I added "round' or Bruce> error" to the TODO item. Ah, OK. "A guide to the SQL standard" claims it's an exception, but I haven't checked the actual standard. Bruce> Uh, I think this already works fine for PG syntax, and I Bruce> assume once we support ANSI syntax it will work fine too: I guess. It's just that it's something that EXTRACT doesn't currently have to worry about, so it would be easy to overlook. -roy
Roy Badami wrote: > Bruce> Seems that is implentation-dependent so I added "round' or > Bruce> error" to the TODO item. > > Ah, OK. "A guide to the SQL standard" claims it's an exception, but I > haven't checked the actual standard. Here is an email stating it is implementation defined: http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php > Bruce> Uh, I think this already works fine for PG syntax, and I > Bruce> assume once we support ANSI syntax it will work fine too: > > I guess. It's just that it's something that EXTRACT doesn't currently > have to worry about, so it would be easy to overlook. I don't see how the new code would break EXTACT. I don't think we are going to have to change any internal representations. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
Bruce> Here is an email stating it is implementation defined: Bruce> http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php Hmm, looks like I'm mistaken, then. In fact the "whether to truncate or round" bit suggests you should do one or the other, rather than raise an exception. Will have to take a look at the spec when I get a moment... -roy
Roy Badami wrote: > Bruce> Here is an email stating it is implementation defined: > > Bruce> http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php > > Hmm, looks like I'm mistaken, then. In fact the "whether to truncate > or round" bit suggests you should do one or the other, rather than > raise an exception. > > Will have to take a look at the spec when I get a moment... OK, TODO updated: o Round or truncate values to the requested precision, e.g. INTERVAL '11 months' AS YEAR should return one or zero The full TODO section is: * Add ANSI INTERVAL handling o Add support for day-time syntax, INTERVAL '1 2:03:04' DAY TO SECOND o Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH o Interpret syntax that isn't uniquely ANSI or PG, like '1:30' or '1' as ANSI syntax, e.g. interpret '1:30' MINUTE TO SECOND as '1 minute 30 seconds' o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' o Round or truncate values to the requested precision, e.g. INTERVAL '11 months' AS YEAR should return one or zero o Support precision, CREATE TABLE foo (a INTERVAL MONTH(3)) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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
Bruce Momjian <pgman@candle.pha.pa.us> writes: > ... I don't think we are > going to have to change any internal representations. In order to support the spec *exactly*, we would have to. For instance we cannot presently tell the difference between '13 months' and '1 year 1 month' ... they both end up stored as '13 months'. I can't say that I find this very important, but it does put limits on how exactly we can emulate the spec. Note that I don't find "emulate the spec exactly" to be a desirable goal anyway; its failure to consider daylight-savings issues is an unacceptable shortcoming. The one internal representation change I would really like to see is to store days separately from months and seconds, so that we can distinguish '24 hours' from '1 day' (the latter should sometimes be equivalent to 23 or 25 hours). regards, tom lane
Bruce> o Interpret INTERVAL '1 year' MONTH as I'm still not convinced that allowing this doesn't just create unnecessary confusion. How about: o Decide whether constructs such as INTERVAL '1 year' MONTH should be allowed, and if so what they should mean, eg perhaps CAST (INTERVAL '1 year' AS INTERVAL MONTH)
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
Roy Badami wrote: > > 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? > 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@candle.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
Roy Badami <roy@gnomon.org.uk> writes: > 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? EXTRACT doesn't have access to the typmod of its input, in general. We associate typmods with stored columns but not with the results of arbitrary expressions. regards, tom lane
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
>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes: >> You also need to make EXTRACT do the right thing. >> >> eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 >> >> but >> >> EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 >> >> Ditto for day-time intervals, of course. Bruce> Uh, I think this already works fine for PG syntax, and I Bruce> assume once we support ANSI syntax it will work fine too: Ok, so based on Tom's comments it sounds like this isn't a non-issue; in fact it sounds like it will be very hard to make this work (at least without changing the internal represnation of an interval). So perhaps there should be something on the TODO list, even if only: o figure out whether it is feasible to make EXTRACT obey ISO standard semantics.
Roy Badami wrote: > >>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes: > > >> You also need to make EXTRACT do the right thing. > >> > >> eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 > >> > >> but > >> > >> EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 > >> > >> Ditto for day-time intervals, of course. > > Bruce> Uh, I think this already works fine for PG syntax, and I > Bruce> assume once we support ANSI syntax it will work fine too: > > Ok, so based on Tom's comments it sounds like this isn't a non-issue; > in fact it sounds like it will be very hard to make this work (at > least without changing the internal represnation of an interval). > > So perhaps there should be something on the TODO list, even if only: > > o figure out whether it is feasible to make EXTRACT obey ISO > standard semantics. I am thinking it isn't worth following the spec in this case. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.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