time date interval...

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.

how exactly is the interval type encoded? is it something like:

struct interval{
int years;
int seconds;
};

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

and what about that kind of stuff:

select "1 year" > "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?

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.

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.

zeit=# create table delme (i interval);
CREATE
zeit=# insert into delme values ('234.2342478618234823467862462348264');
INSERT 38974 1

(ok he inserted it and silently (!) discarded my digits instead of doing an error)

zeit=# select * from delme;
i
-----------------
00:03:54.234248
(1 row)

woops? what's that? only 6 digits? does he store more digits internally?

zeit=# select * from delme where i = '234.23424786182348234';
i
-----------------
00:03:54.234248
(1 row)

oh. thats interesting.... :-) weird sql semantics

zeit=# select * from delme where i = '234.234247';
i
---
(0 rows)

ok. that one makes sense.

zeit=# select * from delme where i = '234.234248';
i
-----------------
00:03:54.234248
(1 row)

ok, too.

zeit=# select * from delme where i = '234.2342485';
i
-----------------
00:03:54.234248
(1 row)

he rounds the 5 downwards???

zeit=# select * from delme where i = '234.2342489';
i
---
(0 rows)

but the 9 upwards???

zeit=# select * from delme where i = '234.2342479';
i
-----------------
00:03:54.234248
(1 row)

yes the 9 goes upwards.

zeit=#

i am using postgresql on debian woody. version is 7.2.1-2woody2.

i'd like to have a better documentation on postgresql time data types.
maybe someone can help me understand things better.

maybe the documentation on time types should be structured like this:

1. overview
timestamp is for bla,
interval is for doing foo...
2. representation
timestamp is internally stored like this:
interval is internally stored like this:
as you see there is timezone stuff encoded. it's meaning is fooo.
the fact that the interval has both years and seconds is that
it is not possible to express years as seconds.. bla bla
beware! the representation is inexact! errors sum up and one day your
program will finally fail, if you don't make sure you take care of that problem!
(( telling the user about the internal storage answers many questions!!
i had to fiddle around very long to understand time zone handling. if i
had known the internal representation, all would have been much clearer ))
3. io
3.1 timestamp
depending on the settings (ISO,german,...)
if there are no seconds, only hours:minutes is printed..... foo foo
bla bla if you add CET, then the timezone in the representation is set to +02. the
time itself is NOT touched. just the additional info about time zone is encoded.
3.2 interval
bla bla
4. operators
4.1 + and -
if you subtract timestamps, you get an interval. you cannot add timestamps.
if i subtract timestamps with different time zones, what is the meaning of the result?
4.2 comparison = < >
beware! since representation is inexact, calculation errors sum up!!
this ends in weirdness so better use INTEGER for time representation...
4.3 oddities with years,seconds
a-b != (a+c) - (b+c) because bla bla bla
4.4 casting
what happens if i cast a timestamp with time zone to one without? are the
hours added or discarded?
4.4 warning
if you need to regulate some chemical reaction process and all
is related to timing, don't use all those time types. use your own microsecond
counter, that is completely independent of all other time stuff. that's a general
guideline, not only for postgresql apps.

thanks!
cu
erik

--
Erik Thiele