[ADMIN] bizarre AGE behaviour
Hello all,
Many thanks for this fine product.
We began encountering some unexpected date related errors this week and
after further investigation found the following. We use the postgres AGE
function in a custom function. The AGE function has begun to throw some
unanticipated results back. This has not happened before and my first
guess is that it may be leap year related. Here are some examples.
This is good...
network=# select age('04-01-04','03-01-04');
age
---
1 mon
(1 row)
This isn't...
network=# select age('05-01-04','03-01-04');
age
-
1 mon 30 days 23:00
(1 row)
Now it gets really strange..
network=# select age('06-01-04','04-01-04');
age
-
1 mon 29 days 23:00
(1 row)
This may have been addressed in the past (I subscribe to this list) and
I just missed it, but is there a simple fix for this problem?
Thanks.
--
Bill MacArthur
Webmaster
The DHS Club, Inc.
The Best Is Yet To Come!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] bizarre AGE behaviour
On Wednesday 03 March 2004 9:19 am, DHS Webmaster wrote:
> We began encountering some unexpected date related errors this
week...
> This is good...
> network=# select age('04-01-04','03-01-04');
> age
> ---
> 1 mon
> (1 row)
>
> This isn't...
> network=# select age('05-01-04','03-01-04');
> age
> -
> 1 mon 30 days 23:00
> (1 row)
>
> Now it gets really strange..
> network=# select age('06-01-04','04-01-04');
> age
> -
> 1 mon 29 days 23:00
> (1 row)
>
> This may have been addressed in the past (I subscribe to this list)
> and I just missed it, but is there a simple fix for this problem?
> Thanks.
It was originally addressed long in the past (1784, Paris by Benjamin
Franklin): http://webexhibits.org/daylightsaving/
US Daylight Saving Time starts this year on April 4 when 0200 jumps to
0300. The answers PostgreSQL gave are correct.
Cheers,
Steve
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] bizarre AGE behaviour
Steve Crawford <[EMAIL PROTECTED]> writes: > US Daylight Saving Time starts this year on April 4 when 0200 jumps to > 0300. The answers PostgreSQL gave are correct. I suspect what the OP wants is non-timezone-aware behavior, which he could get by casting the inputs of age() to timestamp without time zone. As written the system is preferring to interpret them as timestamp with time zone. Also, if what's really wanted is just resolution to the day level, the date subtraction operator might be a lot better choice than age() anyway. regression=# select '06-01-04'::date - '04-01-04'::date; ?column? -- 61 (1 row) regards, tom lane ---(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
[ADMIN] Multiple inserts without COPY
Hello! I have a routine in my application where I have the potential to generate hundreds of inserts or deletes at one time. Right now, I issue each insert and delete separately. I'm hoping there might be a way of generating a single SQL statement to send to the backend for each. The deletes look something like delete from CL where CL_id = i where i could be a list of several hundred integers. Again, right now I iterate through the list. The inserts might look like insert into CL (CO_id, PE_ID) values (j, k) where j and k are also integers and I could have a list of several hundred pairs of j and k. MySQL has a multiple insert feature where you simply append a bunch of (j, k)'s separated by a comma. Does PostgreSQL have anything like this? I was hoping I might be able to use COPY, but I see that's really only for psql. Are there any options? Or, do I simply send a bunch of queries? Thanks! Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Multiple inserts without COPY
Mark Lubratt <[EMAIL PROTECTED]> writes: > The deletes look something like > delete from CL where CL_id = i > where i could be a list of several hundred integers. Again, right now > I iterate through the list. Consider delete from CL where CL_id in (i,j,k,...); If you have hundreds of target values, it might be better to put them in a temp table and go delete from CL where CL_id in (select id from temp_table); The latter should be reasonably quick in 7.4, but be warned that it'll suck in prior releases. > MySQL has a multiple insert feature where you simply append a bunch of > (j, k)'s separated by a comma. Does PostgreSQL have anything like > this? That is SQL-spec syntax, but we've not gotten around to implementing it. COPY is a lot faster for bulk inserts. > I was hoping I might be able to use COPY, but I see that's > really only for psql. Huh? You can use COPY FROM STDIN in most of our client libraries, certainly so with libpq. What are you using? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Multiple inserts without COPY
On Mar 3, 2004, at 10:22 PM, Tom Lane wrote: Mark Lubratt <[EMAIL PROTECTED]> writes: The deletes look something like delete from CL where CL_id = i where i could be a list of several hundred integers. Again, right now I iterate through the list. Consider delete from CL where CL_id in (i,j,k,...); If you have hundreds of target values, it might be better to put them in a temp table and go delete from CL where CL_id in (select id from temp_table); The latter should be reasonably quick in 7.4, but be warned that it'll suck in prior releases. Yeah, that's what I was looking for! I thought I might be able to do that. Cool. I was hoping I might be able to use COPY, but I see that's really only for psql. Huh? You can use COPY FROM STDIN in most of our client libraries, certainly so with libpq. What are you using? Actually, I'm using REALbasic. All the communication is happening through a TCP connection. I tried emulating what the command might look like in pgAdmin. But, of course, after the semi-colon, the parser got confused when it hit the actual data. I tried: COPY MyTable (id, val) FROM STDIN; 2 Hello There! \. It choked at the 2. I was just trying to see if the backend suspended parsing and would just start copying like psql does. But, I guess not. How does psql shovel a COPY at the backend? Oooh. I just remembered. There is a new method in the REALbasic classes that provide the PostgreSQL functionality. I'll have to check it out... I was hoping that there might be a syntax trick with INs or something like the delete command above. Something that might expand in the parser to do what I want to do. Thanks! Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Multiple inserts without COPY
Mark Lubratt <[EMAIL PROTECTED]> writes: >> Huh? You can use COPY FROM STDIN in most of our client libraries, >> certainly so with libpq. What are you using? > Actually, I'm using REALbasic. Um. I have no idea what sort of support they have, but you do need a client interface library that knows about the COPY data protocol. Typically there will be separate API calls for pushing COPY data through after you issue the COPY command. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Multiple inserts without COPY
On Mar 3, 2004, at 11:20 PM, Tom Lane wrote: Mark Lubratt <[EMAIL PROTECTED]> writes: Huh? You can use COPY FROM STDIN in most of our client libraries, certainly so with libpq. What are you using? Actually, I'm using REALbasic. Um. I have no idea what sort of support they have, but you do need a client interface library that knows about the COPY data protocol. Typically there will be separate API calls for pushing COPY data through after you issue the COPY command. I figured it out. They do provide a string to push COPY FROM STDIN through. Thanks anyway! Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
