Doing arithmetic with ANSI dates

Roy Hann

unread,

Oct 27, 2021, 3:53:12 PM10/27/21

to

I'm well used to doing arithmetic with the native Ingres DATE
(INGRESDATE) type, e.g.:

SELECT birthdate + '3 months' FROM...

If birthdate is an ANSI date (ANSIDATE) the above still works
perfectly well.

But I doubt that is idiomatic ISO/ANSI SQL. I've Googled a bit and found
all kinds of syntax being used. I suspect ...+'3 months' or ...-'49
days' might not work reliably outside of Ingres.

So is there anything that does work reliably? Google is not being super
helpful on this... Maybe I haven't guessed the best search term
though.

Roy

Karl Schendel

unread,

Oct 27, 2021, 4:06:09 PM10/27/21

to info-...@lists.planetingres.org

> On Oct 27, 2021, at 3:53 PM, Roy Hann <spec...@processed.almost.meat> wrote:
>
> I'm well used to doing arithmetic with the native Ingres DATE
> (INGRESDATE) type, e.g.:
>
> SELECT birthdate + '3 months' FROM...
>
> If birthdate is an ANSI date (ANSIDATE) the above still works
> perfectly well.
>
> But I doubt that is idiomatic ISO/ANSI SQL. I've Googled a bit and found
> all kinds of syntax being used. I suspect ...+'3 months' or ...-'49
> days' might not work reliably outside of Ingres.

You'll want an INTERVAL.

SELECT birthdate + INTERVAL '3' MONTH FROM ...

Karl

Roy Hann

unread,

Oct 28, 2021, 4:08:55 AM10/28/21

to

Aha. I had encountered that in my reading but it was not clear (till
now) that it is the standard syntax. Thank you.

For completeness, it seems like the permitted keywords are DAY, MONTH,
and YEAR only...right?

And while I am here, Ingres accepts ANSIDATE + INTEGER. The integer is
interpretted as a number of days. Does the standard endorse such an
expression?

Roy

Karl Schendel

unread,

Oct 28, 2021, 10:18:09 AM10/28/21

to info-...@lists.planetingres.org

> On Oct 28, 2021, at 4:08 AM, Roy Hann <spec...@processed.almost.meat> wrote:

>
> Karl Schendel wrote:
>>
>> You'll want an INTERVAL.
>>
>> SELECT birthdate + INTERVAL '3' MONTH FROM ...
>
> Aha. I had encountered that in my reading but it was not clear (till
> now) that it is the standard syntax. Thank you.
>
> For completeness, it seems like the permitted keywords are DAY, MONTH,
> and YEAR only...right?

It's a bit more complicated than that. I don't have the full syntax at hand, but
the options are YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
YEAR TO MONTH, and DAY TO HOUR, MINUTE, or SECOND.
Intervals can have either year and month fields, or day / hour / minute / second
fields, but not both for some bizarre reason that I don't know.

The quoted part in the middle is 'year-month' for a year to month interval,
and 'day hour[:min[:second]]' for a day to second interval. You can have
a leading minus sign (inside the quotes) to indicate a negative interval.

>
> And while I am here, Ingres accepts ANSIDATE + INTEGER. The integer is
> interpretted as a number of days. Does the standard endorse such an
> expression?

I don't think it does.

Karl

Roy Hann

unread,

Oct 28, 2021, 11:56:30 AM10/28/21

to

Karl Schendel wrote:

>> On Oct 28, 2021, at 4:08 AM, Roy Hann <spec...@processed.almost.meat> wrote:

>> [snip]

>> For completeness, it seems like the permitted keywords are DAY, MONTH,
>> and YEAR only...right?
>
> It's a bit more complicated than that. I don't have the full syntax at hand, but
> the options are YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
> YEAR TO MONTH, and DAY TO HOUR, MINUTE, or SECOND.
> Intervals can have either year and month fields, or day / hour / minute / second
> fields, but not both for some bizarre reason that I don't know.

I recall an argument that it is because months don't all have the same
number of days so if an expression included months as well as days you
can't legitimately evaluate it. Sometimes, depending in my mood I can
hypnotize myself into agreeing.

> The quoted part in the middle is 'year-month' for a year to month interval,
> and 'day hour[:min[:second]]' for a day to second interval. You can have
> a leading minus sign (inside the quotes) to indicate a negative interval.

I had wondered why it has to be quoted. 'YEAR-MONTH' explains it. It's
still kinda grotesque-looking and it grates, but OK, fine.

I don't see why it can't be an expression that evaluates to a string
though.

>> And while I am here, Ingres accepts ANSIDATE + INTEGER. The integer is
>> interpretted as a number of days. Does the standard endorse such an
>> expression?
>
> I don't think it does.

On the one hand that's a pity, because it's unambiguous and useful. On
the other hand Ingres/X/Vector support it so I'm happy.

Roy