Re: time date interval... - Mailing list pgsql-sql

On Sat, Jul 12, 2003 at 09:00:19 +0200, Erik Thiele <erik@thiele-hydraulik.de> wrote:
> hi
> 
> I am having problems with understanding of date/time/interval handling
> in postgresql and sql in general.
> 
> a,b are TIMESTAMP WITHOUT TIME ZONE
> c is INTERVAL
> 
> mathematics tell me:
> 
> a-b = (a+c) - (b+c)
> 
> is this also true in sql? if for example c is "1 year", then depending
> on the value of a and b the results may vary. not all years have the
> same amount of days.

It won't always be true. If c has a month component the change by adding
it to a can be different than adding it to b. The number of leap days
between a and b can also be different than the number of leap days between
a+c and b+c.

> how exactly is the interval type encoded? is it something like:
> 
> struct interval{
> int years;
> int seconds;
> };

There are two parts. One part is a difference in months and the other is
a difference in a fixed amount of time.

> to make it possible to represent both years and normal seconds in that type?

Yes. But if you do this it isn't documented which gets added first, so
you might not be able to count on the order never changing.

> and what about that kind of stuff:
> 
> select "1 year" > "365 days";

The correct format is:
select '1 year'::interval > '365 days';

> 
> how is that handled? is here the year converted down to days in a
> different way than if i add the year to a TIMESTAMP?

Months seem to be treated as having 30 days when comparing intervals.
I don't know if that is documented or not.

> my next problems are with accuracy. i write a program where i think that
> TIMESTAMP and INTERVAL are exact types. i use to directly compare them
> with =. i also do calculations on them. if they are internally
> representated with inexact types like float or double, then my program
> will finally fail.

There is a configure option to control this. You can have them stored
internally as a 64 bit integer. However, I don't know that the way
the information is stored in the default way (using double) isn't also
done so that there can be rounding issues when doing addition and
subtraction (especially for dates around the year 2000).

> next problem is conversion from the postgresql date/time/interval output
> to my internal own structures. i directly use the C-API as a backend to
> my own database API (no i don't like ODBC and so on). now, how do i
> convert those strings sent to me by postgresql to my own date
> structures? what i did was enter several values in psql and check what
> kind of strings postgres sends me, then i wrote a parser for them.

You might be better off using extract or to_char to format things explicitly.