[SQL] 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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
Re: [SQL] Problem with temporary table -- Urgent
On Thursday 10 Jul 2003 9:39 am, Vijay Kumar wrote: > Hi, > We are using postgresql 7.3.3, we are encountering the following problems > when we used temporary tables. > > Here with i'm sending my Sample function. > > create or replace function TestTemp_refcur(refcursor) returns refcursor As [snip] > lString := lString || '' create temporary table temp_Table( Name > Numeric);''; [snip] > open refc for select * from temp_Table; [snip] > psql:test18.sql:25: ERROR: Relation "temp_table" does not exist Sorry about the delay. The issue is that plpgsql is compiled, so the "open refc ... temp_Table" refers to a specific entry in the system-tables. When the temp-table gets recreated it gets a new OID and so your function can't find it. Solution: 1. Don't use temp-tables 2. Use EXECUTE "..." which gets parsed when the function is run. Note that this problem applies to any table that gets dropped and restored. I try to keep my function code in the same file as the table(s) they rely on, that way I recreate both when I make changes. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem with temporary table.
Hi,
We are using postgresql7.3.3, we
are encountering some problems by using temporary tables.
Actually our requirement was,
1. create
temporary table.
2. insert
some values on that table by using some quries.
3.
select the inserted values from the temporary table.
To fullfil the above requirement, we wrote the
below functions..kindly go through the below functions and
guide us to come out from this temporary table
problem.
1. Call the same function more than ones in
the same connection.
eg;
create or replace function TestTemp_refcur(refcursor) returns
refcursor as '
declare
refc alias for $1;
begin
create temporary table temp_table(idno
numeric,iname varchar(10));
insert into temp_table values
(1,''ganesh'');
insert into temp_table values
(2,''John'');
open refc for select * from
test_temp_table;
return refc;
end;
' language
'plpgsql';
begin; select
TestTemp_refcur('funcursor'); fetch all
in funcursor;
commit;
The above
function is working fine for the first call, from next call onwards it is
throwing the below error.
Error: relation 'temp_table' already exists.
2. To avoid this, we had gone through some
of postgresql faq and documents. There some one suggested to create
temporary table by Execute.
So that, we created one function,
inside that fucntion we created one more function to take care of creating
temporary table.
Eg,
create or replace
function TestTemp_refcur(refcursor) returns refcursor As '
declare refc
alias for $1;
lString Varchar(4000);
begin lString
:= '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as
BEGIN
'';
lString := lString || '' create temporary table
temp_Table( Name Numeric);'';
lString := lString || '' insert into temp_Table values (1);
'';
lString := lString || '' insert into temp_Table values
(2);'';
lString := lString || '' return null; end; language
plpgsql;'';
raise notice '' Notice is % '',
lString;
execute
lString;
open refc for select * from
temp_Table;
return refc; end; ' language
'plpgsql';
begin; select
TestTemp_refcur('funcursor'); fetch all in
funcursor; commit;
With the above approach, we are getting the below
error.
Error :
Relation "temp_table" does not exist
Any kind of info/soln/help will be highly
appreciated..
Thanks & Regards
Vijay
Re: [SQL] time date interval...
On Sat, Jul 12, 2003 at 09:00:19 +0200,
Erik Thiele <[EMAIL PROTECTED]> 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.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Blobs
Sir Devi, This is PostgreSQL mailing List. If you need help porting applications from Oracle9i to PostgreSQL we might help. Or if you have generic SQL question not specific to any database we can also consider. for help in Oracle specific problems there may be more appropriate lists on the net. Rajesh Mallah. On Thursday 10 Jul 2003 4:40 pm, sri devi wrote: > hi > > we have to download url files in to oracle using BLOBs how to create oracle > table stucture how to write the query,we are using oracle9i,and > javaswings,reply me to this id. [EMAIL PROTECTED] thanking you > sridevi > > SMS using the Yahoo! Messenger;Download latest version. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger proceedures in sql
Yes of course! contrib/dbmirror does execute a procedure written in 'C' called recordchange() ON update , insert , delete. If you need help in getting its source lemme know. regds Mallah. On Thursday 10 Jul 2003 11:10 am, adivi wrote: > hi, > > can trigger proceedures ( procedures to be executed from within a > trigger ) not be written in sql. > > i was looking for examples and can find proceedures in 'c' only. > > regards > -adivi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Recursive request ...
Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "Nested Sets & Adjacency Lists" http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: > I have to make a function that returns a tree with title and link of a > table. > > Recursively, a information depends on a parent information. > > It is to organise a menu with parent dependance. > > How is it possible and faster ? in C ? pl/pgsql or other ? > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
