pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtime date interval...
2+ messages / 2 participants
[nested] [flat]
* time date interval...
@ 2003-07-12 07:00 Erik Thiele <erik@thiele-hydraulik.de>
0 siblings, 1 reply; 2+ messages in thread
From: Erik Thiele @ 2003-07-12 07:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: time date interval...
@ 2003-07-12 13:36 Bruno Wolff III <bruno@wolff.to>
parent: Erik Thiele <erik@thiele-hydraulik.de>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2003-07-12 13:36 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-12 13:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-12 07:00 time date interval... Erik Thiele <erik@thiele-hydraulik.de>
2003-07-12 13:36 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox