Re: [SQL] diference in dates in minutes
Discussions along this line should stay on the list so that other people
can learn from and add comments to the discussion.
On Sat, Feb 26, 2005 at 16:57:15 -0500,
Joel Fradkin <[EMAIL PROTECTED]> wrote:
> You sure?
> I thought date1::date - date2::date returns an integer of day's diff, but
> date -date returns an interval (least I can do a to_char on it and see day's
> hours etc that were correct.
Then your "date" column is most likely a timestamp, not a date. That is
what you want anyway if you are trying to get a time difference in minutes.
That wouldn't make much sense for dates.
> Why are they depreciating the ability to look at an interval as a string
> anyhow? Is there an approved method of looking at an interval as a string
> replacing it?
I think because the current version does some odd things and no one has put
together a spec to replace it. You can ge formatted output using EXTRACT
and suitable further manipulation.
>
> Joel Fradkin
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
> [EMAIL PROTECTED]
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
> -Original Message-
> From: Bruno Wolff III [mailto:[EMAIL PROTECTED]
> Sent: Saturday, February 26, 2005 4:16 PM
> To: Joel Fradkin
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] diference in dates in minutes
>
> On Sat, Feb 26, 2005 at 15:14:02 -0500,
> Joel Fradkin <[EMAIL PROTECTED]> wrote:
> > You probably want to convert the dates to timestamps, subtract them to
> > get an interval, extract the epoch to get timme in seconds and then divide
> > by 60 to get time in minutes.
> >
> > The converting date to timestamp part isn't trivial. You need to decide
> > on what you mean when you do this. If you really have timestamps in the
> > first place, then you can skip the covernsion step.
> >
> > They are dates and I did find I could do date - date to give me an
> interval
> > date_part('epoch',date-date) returns in secs so /60
>
> date - date won't give you an interval, it will give you an integer of some
> sort.
>
> > This appeared to work ok without converting to time stamps, but maybe I am
> > missing it if it is not correct as the example I looked at was a large
> > difference. The app is analyzing Tlogs and the difference should never be
> > too large, so I will further analyze it with real data.
> > As always I appreciate the help.
> > My real question is this an interval then and will it be depreciated soon?
>
> The Interval type won't be depreciated. Using to_char to convert intervals
> to strings is being depreciated. This won;t cause a problem for extract
> or similar functions.
>
---(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
[SQL] Serial and Index
I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 that when a serial is created then an index is created on the column. However I can't seem to find this anywhere in the PoistgreSQL manual. Is this true? Thanks. -Sam ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Serial and Index
On Sun, Feb 27, 2005 at 12:54:52 +, Sam Adams <[EMAIL PROTECTED]> wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. That was true with the first release that had the serial pseudo type. That was changed in later versions to provide flexibility for DBAs. In common use, a serial column is declared as the primary key and you get a unique index created without any other special declarations. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Serial and Index
On Sun, Feb 27, 2005 at 12:54:52PM +, Sam Adams wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 That copy of the FAQ is over two years old according to the "Last updated" text at the top of the page. You can find the current FAQ here: http://www.postgresql.org/files/documentation/faqs/FAQ.html > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. Not since 7.3. Here's an excerpt from the 7.3 Release Notes: * No longer automatically create index for SERIAL column (Tom) You can confirm this for yourself by trying it and observing what happens. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] AutoCommit and DDL
I know it's not failing, I have the server logging the commands and there are no errors. The only change made was turning AutoCommit on. -Don On Sat, 26 Feb 2005 21:20:43 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sat, Feb 26, 2005 at 02:56:52PM -0600, Don Drake wrote: > > > I turned AutoCommit on and re-ran the perl script and the tables get > > created. Why won't these table's get created when AutoCommit is off? > > Why do I have to commit DDL? > > If you're using DBI, are you using PrintError or RaiseError or > otherwise checking the success of each command? My first guess > would be that some command is failing (e.g., a DROP of an object > that doesn't exist), so the transaction is automatically rolled > back even though you requested a commit. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Serial and Index
On Sun, 27 Feb 2005 12:54:52 +, Sam Adams <[EMAIL PROTECTED]> wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. The FAQ entry is incorrect. If you make your SERIAL column the PRIMARY KEY of the table, however, a UNIQUE index will be created. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Serial and Index
Documentation at http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL says: "In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic." and "*Note: * Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type." Miroslav Åulc, CEO StartNet s.r.o. VrchlickÃho 161/5, Praha 5, 150 00, Äeskà republika ICQ: 60144073 web: www.startnet.cz, www.novavystavba.cz mobil: +420 603 711 413 telefon: +420 257 225 602 Sam Adams wrote: I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 that when a serial is created then an index is created on the column. However I can't seem to find this anywhere in the PoistgreSQL manual. Is this true? Thanks. -Sam ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Serial and Index
On Sun, 2005-02-27 at 12:54 +, Sam Adams wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. no, this is not true. howewer defining the serial column as UNIQUE will. test=# create table q2 (a serial unique,b text); NOTICE: CREATE TABLE will create implicit sequence "q2_a_seq" for "serial" column "q2.a" NOTICE: CREATE TABLE / UNIQUE will create implicit index "q2_a_key" for table "q2" CREATE TABLE test=# feel free to inform the webmaster of that site, that the FAQ has been updated since 2002. here is a more authoritative copy: http://www.postgresql.org/files/documentation/faqs/FAQ.html gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Serial and Index
"Sam Adams" <[EMAIL PROTECTED]> writes: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. It was once true. Perhaps you should be reading a more up-to-date copy of the FAQ. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] AutoCommit and DDL
I did some traces and it all looks OK.
The problem has to do with multiple concurrent connections to the
server causing problems. I've removed the concurrent connections and
now this works. Strange.
-Don
On Sun, 27 Feb 2005 12:54:34 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Sun, Feb 27, 2005 at 11:55:37AM -0600, Don Drake wrote:
>
> > I know it's not failing, I have the server logging the commands and
> > there are no errors.
> >
> > The only change made was turning AutoCommit on.
>
> Have you used any of DBI's tracing capabilities? Could you post a
> simple test case? The following works for me with Perl 5.8.6, DBI
> 1.47, DBD::Pg 1.32, and PostgreSQL 7.4.7 on FreeBSD 4.11-STABLE:
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=test", "mfuhr", "", {AutoCommit => 0});
> $dbh->do("CREATE TABLE foo (x integer)");
> $dbh->commit;
> $dbh->disconnect;
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
