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
istimezone stuff encoded. it's meaning is fooo. the fact that the interval has both years and seconds is that it is not
possibleto express years as seconds.. bla bla beware! the representation is inexact! errors sum up and one day your
programwill finally fail, if you don't make sure you take care of that problem! (( telling the user about the internal
storageanswers many questions!! i had to fiddle around very long to understand time zone handling. if i had known the
internalrepresentation, 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
isNOT 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
timestampswith different time zones, what is the meaning of the result? 4.2 comparison = < > beware! since
representationis 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
ifi cast a timestamp with time zone to one without? are the hours added or discarded? 4.4 warning if you need to
regulatesome 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
forpostgresql apps.
thanks!
cu
erik
--
Erik Thiele
