Re: BUG #1993: Adding/subtracting negative time

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgman(at)candle(dot)pha(dot)pa(dot)us>, <kg(at)kgb(dot)une(dot)edu(dot)au>
Cc: <mike(at)fuhr(dot)org>, <grzm(at)myrealbox(dot)com>, <pgsql-bugs(at)postgresql(dot)org>, <mr-russ(at)pws(dot)com(dot)au>, <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <hb(at)x256(dot)com>
Subject: Re: BUG #1993: Adding/subtracting negative time
Date: 2005-10-26 14:20:49
Message-ID: 435F4A740200002500000246@gwmta.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

This is probably why the ANSI SQL standard specifies that
the way to get an INTERVAL from two timestamps is to subtract
them within parentheses follow that with interval_qualifier. Under
the standard you are required to SAY whether you want the
interval in days or hours, etc.

*interval_value_expression
: interval_term
| interval_value_expression_1 PLUS_SIGN interval_term_1
| interval_value_expression_1 MINUS_SIGN interval_term_1
| LEFT_PAREN datetime_value_expression MINUS_SIGN datetime_term
RIGHT_PAREN interval_qualifier
;

*interval_qualifier
: start_field TK_to end_field
| single_datetime_field
;

*single_datetime_field
: non_second_datetime_field [ LEFT_PAREN
interval_leading_field_precision RIGHT_PAREN ]
| TK_second [ LEFT_PAREN interval_leading_field_precision [ COMMA
interval_fractional_seconds_precision ] RIGHT_PAREN ]
;

*non_second_datetime_field
: TK_year
| TK_month
| TK_day
| TK_hour
| TK_minute
;

*interval_leading_field_precision
: UNSIGNED_INTEGER
;

*interval_fractional_seconds_precision
: UNSIGNED_INTEGER
;

>>> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> >>>

When you do a subtraction, it isn't clear if you are
interested in "days" or "hours"

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Frost 2005-10-26 14:27:00 Index name different from constraint name
Previous Message Tom Lane 2005-10-26 14:19:09 Re: BUG #1993: Adding/subtracting negative time intervals