Thread: Comments on earlier age() post.
I haven't heard from anyone on my earlier age() post. If someone could test
it on their database and let me know if the behavior they get is expected, I
would greatly appreciate it.
The queries again :
hhs=# SELECT age('Sun Dec 03 08:00:00 2000 EST','Tue Oct 10 08:00:00 2000
EDT') as esec;
esec
------------------------
@ 1 mon 24 days 1 hour
(1 row)
Ok, but if I turn right around and add that value back , I get :
hhs=# SELECT ('Tue Oct 10 08:00:00 2000 EDT'::timestamp + '1 mon 24 days 1
hour'::interval);
?column?
------------------------------
Mon Dec 04 08:00:00 2000 EST
(1 row)
Thanks!!!
-Mitch
"I'd change the world if God would just give me the source code."
"Mitch Vincent" <mitch@venux.net> writes:
> hhs=# SELECT age('Sun Dec 03 08:00:00 2000 EST','Tue Oct 10 08:00:00 2000
> EDT') as esec;
> esec
> ------------------------
> @ 1 mon 24 days 1 hour
> (1 row)
> Ok, but if I turn right around and add that value back , I get :
> hhs=# SELECT ('Tue Oct 10 08:00:00 2000 EDT'::timestamp + '1 mon 24 days 1
> hour'::interval);
> ?column?
> ------------------------------
> Mon Dec 04 08:00:00 2000 EST
> (1 row)
This is more Thomas' bailiwick than mine, but it seems to me that these
operations are inherently rather ill-defined. Consider: counting
forward from Oct 10 to Dec 3, one would naturally call the interval
"1 month + 23 days" (1 month takes you to Nov 10, from which it's
23 days to Dec 3, no?). But counting backwards from Dec 3 to Oct 10
looks like "1 month + 22 days" (1 month takes you to Nov 3, from which
it's 22 days back to Oct 12). The trouble is that Oct and Nov have
different numbers of days, so you get different answers depending on
what your referent for "1 month" is.
There may indeed be a bug here --- it bothers me that counting on my
fingers gives 22/23 days where the system says 23/24. But I'm not
sure there's anything wrong with the fact that (A-B)+B != A, given
the way type interval is defined.
Maybe we need to offer a different kind of interval that avoids the
symbolic "month" rigmarole and just counts honest-to-god seconds.
regards, tom lane
> This is more Thomas' bailiwick than mine, but it seems to me that these > operations are inherently rather ill-defined. Consider: counting > forward from Oct 10 to Dec 3, one would naturally call the interval > "1 month + 23 days" (1 month takes you to Nov 10, from which it's > 23 days to Dec 3, no?). But counting backwards from Dec 3 to Oct 10 > looks like "1 month + 22 days" (1 month takes you to Nov 3, from which > it's 22 days back to Oct 12). The trouble is that Oct and Nov have > different numbers of days, so you get different answers depending on > what your referent for "1 month" is. I see what you mean.. > There may indeed be a bug here --- it bothers me that counting on my > fingers gives 22/23 days where the system says 23/24. But I'm not > sure there's anything wrong with the fact that (A-B)+B != A, given > the way type interval is defined. Indeed, I'm not so sure now that I think about it either -- still, I need to find the number of days (or amount of time I should say) I need to add to A to get to B. I wanted to do it in the database becuae I had hoped that timezones, leap years and anything else that might require more calculations would be taken into account. I'm experimenting with a few other ways of doing this but I'm running into some more innaccuracies.. > Maybe we need to offer a different kind of interval that avoids the > symbolic "month" rigmarole and just counts honest-to-god seconds. Certainly an idea! Thanks Tom!
Hi Mitch, >> This is more Thomas' bailiwick than mine, but it seems to me that these >> operations are inherently rather ill-defined. Consider: counting >> forward from Oct 10 to Dec 3, one would naturally call the interval >> "1 month + 23 days" (1 month takes you to Nov 10, from which it's >> 23 days to Dec 3, no?). But counting backwards from Dec 3 to Oct 10 >> looks like "1 month + 22 days" (1 month takes you to Nov 3, from which >> it's 22 days back to Oct 12). The trouble is that Oct and Nov have >> different numbers of days, so you get different answers depending on >> what your referent for "1 month" is. [...] >> Maybe we need to offer a different kind of interval that avoids the >> symbolic "month" rigmarole and just counts honest-to-god seconds. I don't know if that will help but this is the way I have work for a few years now : dates are stored as floats with integer part= julian date (number of days since some special date like epoch) and fractionnal part is a portion of 1 day (that is 0.25 is 6am, 1/86400=1 second, etc.) In oracle you can write : select trunc(sysdate-mydate) as diffdays from mytable; I haven't found out --yet-- how to do such calculations with Postgresql. Thay also have a few very clever functions that should not be too hard to code in pg if we can gain access to date arithmetics. Anyone already found interesting things ? -- Jean-Christophe Boggio cat@thefreecat.org Independant Consultant and Developer Delphi, Linux, Oracle, Perl
Hi,
I've recently started with pgsql after having used mysql. Oddly, a problem
the latter seems to have followed me.
When I set up a field with a default value to use a sequence (with "nextval"),
It doesn't work automatically. Both using DBI and the pgsql cli, if I do
INSERT INTO foo VALUES (NULL);
or
INSERT INTO foo VALUES ('');
I get an error or null is inserted in the first case, if the field is set
non-null, or a 0 in the second case.
Of course without this, a good part of the convenince of auto-incrementing is
defeated. I can work around it by looking up the appropriate value, but then
I need to do locking as well, which is likely slower and definitely more
annoying.
Is there some settable option that determines what will cause a default value
to be inserted? Is this a compile-time option of some sort? I've been
searching around for a couple of weeks on this, with no luck in the docs. I
would be very grateful for any assistance.
--JB
--
------------------------------------------------------------------
J B Bell | /~\
Systems Administrator | ASCII \ / Against
Internet Gateway | Ribbon X HTML
| Campaign / \ Mail
J B Bell <jbbell@intergate.ca> writes:
> When I set up a field with a default value to use a sequence (with "nextval"),
> It doesn't work automatically. Both using DBI and the pgsql cli, if I do
>
> INSERT INTO foo VALUES (NULL);
>
> or
>
> INSERT INTO foo VALUES ('');
>
> I get an error or null is inserted in the first case, if the field is set
> non-null, or a 0 in the second case.
>
> Of course without this, a good part of the convenince of auto-incrementing is
> defeated. I can work around it by looking up the appropriate value, but then
> I need to do locking as well, which is likely slower and definitely more
> annoying.
Doing:
INSERT INTO foo(col1, col2, col4) VALUES(val1, val2, val4);
will do what you want, given that col3 is the auto-incrementing
column. I.e. tell PostgreSQL to only set the other columns.
Tomas
* J B Bell <jbbell@intergate.ca> [001012 10:13] wrote:
> Hi,
>
> I've recently started with pgsql after having used mysql. Oddly, a problem
> the latter seems to have followed me.
>
> When I set up a field with a default value to use a sequence (with "nextval"),
> It doesn't work automatically. Both using DBI and the pgsql cli, if I do
>
> INSERT INTO foo VALUES (NULL);
>
> or
>
> INSERT INTO foo VALUES ('');
>
> I get an error or null is inserted in the first case, if the field is set
> non-null, or a 0 in the second case.
>
> Of course without this, a good part of the convenince of auto-incrementing is
> defeated. I can work around it by looking up the appropriate value, but then
> I need to do locking as well, which is likely slower and definitely more
> annoying.
>
> Is there some settable option that determines what will cause a default value
> to be inserted? Is this a compile-time option of some sort? I've been
> searching around for a couple of weeks on this, with no luck in the docs. I
> would be very grateful for any assistance.
This is because you're abusing INSERT, the porper way to do insert is
to specifify which columns explicitly:
INSERT INTO foo (col1, col3) VALUES ( 5, 6 );
now, if col2 has a default value it will be set properly.
-Alfred
On Thu, 12 Oct 2000, J B Bell wrote:
> I've recently started with pgsql after having used mysql. Oddly, a problem
> the latter seems to have followed me.
>
> When I set up a field with a default value to use a sequence (with "nextval"),
> It doesn't work automatically. Both using DBI and the pgsql cli, if I do
>
> INSERT INTO foo VALUES (NULL);
>
> or
>
> INSERT INTO foo VALUES ('');
>
> I get an error or null is inserted in the first case, if the field is set
> non-null, or a 0 in the second case.
It should auto-increment if you don't specify the field at all:
create sequence myseq;
create table mytable (
id int4 PRIMARY KEY default nextval('myseq'),
descr text
);
insert into mytable(descr) values ('some text');
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Is a tattoo real, like a curb or a battleship? Or are we suffering in Safeway?
On Thu, Oct 12, 2000 at 10:22:10AM -0700, Alfred Perlstein wrote:
>> [JB asks about why INSERT INTO foo VALUES ( 0, ... ) doesn't trip the
>> default value]
>
> This is because you're abusing INSERT, the porper way to do insert is
> to specifify which columns explicitly:
>
> INSERT INTO foo (col1, col3) VALUES ( 5, 6 );
>
> now, if col2 has a default value it will be set properly.
I feel so dirty. I only hope the INSERT statement can forgive me, and that
I can make reparations for my sins. :-)
> -Alfred
Thanks Alfred, and to the several other people who responded so quickly
to my, ahem, query. No need to cc: me, btw, I'm on the list and hope to
learn more from it.
--JB
--
------------------------------------------------------------------
J B Bell | /~\
Systems Administrator | ASCII \ / Against
Internet Gateway | Ribbon X HTML
| Campaign / \ Mail