[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.
thank
[SQL] summing tables
hi, i have a table consisting of 4 integers. seq is for making the table ordered. (ORDER BY SEQ ASC) a,b,c maybe null seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | i am needing a sql statement to do c=a+b+"the c of the row with seq one less than myself" this statement has to run over the whole table, in seq order. how can this be acomplished??? cu&thanks erik -- Erik Thiele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] summing tables
On Tue, 15 Jul 2003 15:16:21 +0300 "Viorel Dragomir" <[EMAIL PROTECTED]> wrote: > update table_name > set c = a + b + (select c from table_name as x where x.seq = seq-1) > where c is null; hmmm. the query is run row by row, isn't it? but it will have different results depending on the order of those rows. look, the c value is set by one row-query and read by the row-query of the row below. does sql specify some "order is magically always as you expect it" rule? still i am a little confused. and i am sorry, i didn't initially specify that the "seq" are not gapless. i.e. seq-1 does not always exist. but seq-13 could be the next lower one! zeit=# select * from foo; seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | 6 | -1 | -2 | 5 | -2 | -2 | 4 | 0 | 1 | (7 rows) i created this (gapless for easiness) table and run your query: zeit=# update foo set c = a + b + (select c from foo as x where x.seq = seq-1) where c is null; UPDATE 6 6 updates??? really??? zeit=# select * from foo; seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | 6 | -1 | -2 | 5 | -2 | -2 | 4 | 0 | 1 | (7 rows) h. let's try the statement of the other reply to my initial mail: UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1) zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1); ERROR: parser: parse error at or near "t" h any clues? cu & thx erik > additional checks are required if you want to update c when c is not null > if all the c are null then this query will do nothing > > i have a table consisting of 4 integers. > > > > seq is for making the table ordered. (ORDER BY SEQ ASC) > > a,b,c maybe null > > > > > > seq | a | b | c > > -+++--- > >0 | 1 | 2 | 3 > >1 | 1 | 2 | > >2 | 5 | 7 | > >3 | -2 | -4 | > > > > > > i am needing a sql statement to do > > > > c=a+b+"the c of the row with seq one less than myself" > > > > this statement has to run over the whole table, in seq order. -- Erik Thiele ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to vacum
On Wed, 10 Sep 2003 20:43:25 +0800 "Richard Sydney-Smith" <[EMAIL PROTECTED]> wrote: > Tried to issue the command "vacum full" both from psql and the sql box in pgadmin > without success. seems like you also vacuumed the second 'U' out of the word :) vacuum full; does the trick. cya erik -- Erik Thiele ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] current_date timezone documentation suggestion
hi when i do select current_date; around midnight, when does the date switch? does it switch according local time zone, or does it switch by GMT ? i did not find docs about this near the docs of "current_date" or anywhere else. please do add it to the documentation. thanks for good work! erik -- Erik Thiele ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] triggers on commit
Hi! I am having a probably stupid question, but anyway I can't find it's solution. it should be a novice question, but lets see :)) I have 2 tables. calendar: person_id INTEGER when DATE type TEXT holiday: person_id INTEGER assigned_to DATE last_possible_assignment DATE the idea here is that workers have a contingent of holidays that they can assign in their calendar. this is stored in the holiday table. if they do not take their holidays, then once they waited until last_possible_assignment, this holiday is not assignable anymore, i.e. bad luck for the worker. the different jobs to do on a certain date are stored in calendar.type. there are things like "normal_work", "weekend", "half-time-work" etc. But also there is type "holiday". In this case there must also be one of the persons holiday days assigned to this date. so. if a program sets the type in a calendar table row to "holiday" then it also must set the assigned_to date in a row of the holiday table to calendar.when. now. what i wanted to do is ensure that this is done correctly and otherwise abort the transaction. of course this check needs to be done at the commit time, because if i do it on statement level, it cannot work, since i cannot change two tables at the same time. so my first idea was to create a trigger that easily correlates all entries in calendar with all entries in holiday and checks if everything is ok. if not, then it raises an exception. i did this in plpgsql. i now call this trigger on modification of calendar and on modification of holiday table. but the problem is the trigger is not deferrable until commit time, since i find no function to do it... i do not want to create a complicated trigger for update of holiday table after modification of calendar table. because then i also have to create a trigger for modification of calendar table after modification of holiday table. and i want to keep the logic in the client program, since it is easier encodable there. all i want to do is an additional check on database layer. is there no way to call triggers on commit time? even better, triggers just called if one of two tables was modified, but again at commit time? see, it is much easier to write a function to check if the database is ok, than it is to write functions to automatize updates across tables. and again i want to keep logic in client program to see what is really happening. my postgresql version is 7.2.1. but i also scanned 7.4 docs for the wanted functionality. cya & thx! erik -- Erik Thiele ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] triggers on commit
On Fri, 19 Mar 2004 10:13:56 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Erik Thiele <[EMAIL PROTECTED]> writes: > > now. what i wanted to do is ensure that this is done correctly and > > otherwise abort the transaction. > > of course this check needs to be done at the commit time, because if > > i do it on statement level, it cannot work, since i cannot change > > two tables at the same time. > > I believe "deferred AFTER" triggers are fired just before commit. i don't find this in the documentation. i only know triggers which run before insert/update/delete or directly (!) after, not waiting until commit. cya & thx! erik -- Erik Thiele ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] special integrity constraints
hi, i have two tables create table a (x integer); create table b (y real); i have the special constraint that the sum of the number of rows in table a plus the number of rows in table b must be even. so there is a posibility of adding one element to a and one element to b, and again the constraint is met. but this of course does not work, since between adding the element to a and adding the element to b, the constraint is not met. so i cannot use a trigger. what i need is the execution of a check procedure at commit time. is that somehow possible? my database has much more tables than just a and b, and most time the other tables are modified and not a or b, so it would be nice to execute the constraint checking procedure only if a or b was modified. yes :) this question i asked in my former mail too, but I think it looked like there was a workaround and my problem was wrong. so i created this new kind of problem here to make things clearer. cu Erik -- Erik Thiele ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 12:13:29 +0200 (EET) Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > Did you check out the DEFERRABLE option on the constraint? > DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. my constraint is not a foreign key constraint cya! erik > > hi, > > > > i have two tables > > > > create table a (x integer); > > create table b (y real); > > > > i have the special constraint that the sum of the number of rows in > > table a plus the number of rows in table b must be even. > > > > so there is a posibility of adding one element to a and one element > > to b, and again the constraint is met. > > > > but this of course does not work, since between adding the element > > to a and adding the element to b, the constraint is not met. > > > > so i cannot use a trigger. > > > > what i need is the execution of a check procedure at commit time. is > > that somehow possible? > > > > my database has much more tables than just a and b, and most time > > the other tables are modified and not a or b, so it would be nice to > > execute the constraint checking procedure only if a or b was > > modified. > > > > yes :) this question i asked in my former mail too, but I think it > > looked like there was a workaround and my problem was wrong. so i > > created this new kind of problem here to make things clearer. > > > > > > cu > > Erik > > > > > > > > -- > -Achilleus > -- Erik Thiele Horst Thiele Maschinenbau-Hydraulische Geräte GmbH Im Kampfrad 2 - 74196 Neuenstadt Tel.: 07139/4801-19 Fax.: 07139/4801-29 email: [EMAIL PROTECTED] Internet: http://www.thiele-hydraulik.de/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 05:26:04 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Mon, Mar 22, 2004 at 10:20:31 +0100, > Erik Thiele <[EMAIL PROTECTED]> wrote: > > hi, > > > > i have two tables > > > > create table a (x integer); > > create table b (y real); > > > > i have the special constraint that the sum of the number of rows in > > table a plus the number of rows in table b must be even. > > Does the above mean that the number of rows in a is equal to the > number of rows in b or that their sum is divisible by 2? it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > so there is a posibility of adding one element to a and one element > > to b, and again the constraint is met. > > > > but this of course does not work, since between adding the element > > to a and adding the element to b, the constraint is not met. > > > > so i cannot use a trigger. > > > > what i need is the execution of a check procedure at commit time. is > > that somehow possible? > > Do the real tables have candidate keys? If so you can use foreign key > references to pair a row in a to a row b (though I am not sure this > is what your real constraint is). no. it's not what i want. the example presented here is just for making it clear why i want to launch a postgresql function on commit time. i am looking for this command: IF TABLE A OR B MODIFIED DO CALL my_checking_function() BEFORE COMMIT; and of course this must be enforced on DB layer. user apps may not work around it. as a dirty hack the following would also work for a first: ON COMMIT CALL my_checking_function(); altough it does lots of unneccessary work if the tables were not accessed. the my_checking_function() throws an error and thus aborts the transaction if the consistency is not ok. (num(a)+num(b) odd) my_checking_function is so complicated that it is not workaroundable with other tools. i wanted to create this situation by introducing the funny constraint that the sum of the number of rows in the two tables is even. of course in my real db the constraint is a more useful one... cu erik -- Erik Thiele Horst Thiele Maschinenbau-Hydraulische Geräte GmbH Im Kampfrad 2 - 74196 Neuenstadt Tel.: 07139/4801-19 Fax.: 07139/4801-29 email: [EMAIL PROTECTED] Internet: http://www.thiele-hydraulik.de/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 06:21:28 -0800 (PST) Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Mon, 22 Mar 2004, Erik Thiele wrote: > > > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > > > Did you check out the DEFERRABLE option on the constraint? > > > > > > > DEFERRABLE > > NOT DEFERRABLE > > > > This controls whether the constraint can be deferred. A constraint that > > is not deferrable will be checked immediately after every command. > > Checking of constraints that are deferrable may be postponed until the > > end of the transaction (using the SET CONSTRAINTS command). NOT > > DEFERRABLE is the default. > > > > Only foreign key constraints currently accept > > this clause. All other constraint types are not deferrable. > > > > my constraint is not a foreign key constraint > > However, foreign keys are implemented using "constraint triggers". They're > really not documented much (because they're a not really separately > supported implementation detail), but you can theoretically make > triggers that are deferred to immediately before commit time. could you paste some code? create table a (x integer); create or replace function alwaysfail() returns opaque as ' begin raise exception ''no no i dont want to''; end; ' language 'plpgsql'; MAGIC COMMAND INSERT HERE, INSTALL CALL OF alwaysfail() ON MODIFICATION OF TABLE a AT COMMIT TIME; begin transaction; insert into a values(4); (ok) insert into a values(10); (ok) commit; (no no i dont want to) is that possible? cu erik -- Erik Thiele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 09:12:11 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Mon, Mar 22, 2004 at 14:10:42 +0100, > Erik Thiele <[EMAIL PROTECTED]> wrote: > > > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > OK, that means my FK suggestion won't help. The other suggestion about > putting triggers on "a" and "b" to update a count in another table > that has a deferred check constraint on it may be your best bet. > This will be a source of contention, but that may or may not be all > that important depending on how often you are updating "a" and "b". > there is no such thing as a deferred check constraint: DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. !!! Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. !!! cu erik -- Erik Thiele ---(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] fine grained trigger time specification...
On Tue, 23 Mar 2004 10:17:31 -0600
Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Tue, Mar 23, 2004 at 15:19:13 +0100,
> Erik Thiele <[EMAIL PROTECTED]> wrote:
> > now sadly i am getting this kind of problem:
> >
> >
> > zeit=> insert into a select
> > nextval('delmeseq'),personalnumber,datum,datum from calendar where
> > type=10409;
> > INSERT 0 581 <-- see, 581 inserts which is pretty much :)
> > zeit=> commit; <-- now i have to wait for 581 calls of alwayscheck
> >
> >
> > since alwayscheck is not the fastest function on earth, i'd like to have
> > it called on commit time if at least one of table a or b was modified,
> > but always only once per commit. is there a way to formulate this:
> >
> > ARRANGE FOR CALL OF FUNCTION alwayscheck() ON COMMIT WHEN AT LEAST ONE
> > OF TABLE a OR TABLE b WAS MODIFIED BUT CALL THE FUNCTION AT MOST ONE
> > TIME;
>
> Wouldn't changing your triggers to be for each statement instead of for
> each row fix this?
no, because
1) my postgresql version does not support "for each statement"
2) i am also inserting with multiple insert commands thousands of entries.
--
Erik Thiele
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Function To Log Changes
On Mon, 5 Apr 2004 13:01:39 -0400 (EDT) "Gavin" <[EMAIL PROTECTED]> wrote: > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? http://gborg.postgresql.org/project/tablelog/projdisplay.php is this what you are looking for? cu erik -- Erik Thiele ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] get sequence value of insert command
hi
create sequence mysequence;
create table foo(
id integer default nextval('mysequence'),
bla text,
wombat integer,
foobar date,
primary key(id)
);
insert into foo (wombat) values (88);
now how do i know the id of my newly inserted element? and
how can this be done in a completely concurrency safe way?
cya
erik
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] get sequence value of insert command
On Fri, 19 Nov 2004 10:57:12 -0500
"Passynkov, Vadim" <[EMAIL PROTECTED]> wrote:
> > -Original Message-
> > From: Erik Thiele [mailto:[EMAIL PROTECTED]
> > Sent: Friday, November 19, 2004 3:42 AM
> > To: [EMAIL PROTECTED]
> > Subject: [SQL] get sequence value of insert command
> >
> > create sequence mysequence;
> >
> > create table foo(
> > id integer default nextval('mysequence'),
> > bla text,
> > wombat integer,
> > foobar date,
> > primary key(id)
> > );
> >
> > insert into foo (wombat) values (88);
> >
> > now how do i know the id of my newly inserted element? and
> > how can this be done in a completely concurrency safe way?
>
> CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
> currval('mysequence') AS id LIMIT 1;
now that one is really great! you should definitly add it to the
faq. plus an additional explanation why the limit 1 is needed here.
thanks!
erik
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
