[SQL] interval as hours or minutes ?
Hi all, Could anyone please tell an easy way to get total hours or minutes from an interval ? SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE user_id = 1; tot_time - 2 days 14:08:44 I'd like to have this like ... AS tot_hours ... tot_hours - 62 and ... AS tot_minutes ... tot_minutes - 3728 Maybe even ... AS tot_hours_minutes_seconds tot_hours_minutes_seconds - 62:08:44 start_date_time and stop_date_time are stored as timestamp without time zone, using Pg 8.1.5 on CentOs 4.4 ??? Thanks, -- Aarni Ruuhimäki ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] interval as hours or minutes ?
Aarni Ruuhimäki wrote: Hi all, Could anyone please tell an easy way to get total hours or minutes from an interval ? SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE user_id = 1; tot_time - 2 days 14:08:44 I'd like to have this like ... AS tot_hours ... tot_hours - 62 and ... AS tot_minutes ... tot_minutes - 3728 Maybe even ... AS tot_hours_minutes_seconds tot_hours_minutes_seconds - 62:08:44 start_date_time and stop_date_time are stored as timestamp without time zone, using Pg 8.1.5 on CentOs 4.4 ??? Thanks, I have been using the following function (watch for line wrap) CREATE OR REPLACE function convert_interval(interval,text) returns text as $$ declare retval TEXT; my_interval INTERVAL := $1; my_type TEXT := $2; qry TEXT; begin if my_type ~* 'hour' then select into retval extract(epoch from my_interval::interval)/3600 || ' hours'; elsif my_type ~* 'min' then select into retval extract(epoch from my_interval::interval)/60 || ' minutes'; elsif my_type ~* 'day' then select into retval extract(epoch from my_interval::interval)/86400 || ' days'; elsif my_type ~* 'sec' then select into retval extract(epoch from my_interval::interval) || ' seconds'; end if; RETURN retval; end; $$ language plpgsql strict immutable; pqsl=# select convert_interval(now() - (now()-interval '1 day 4 hours 6 minutes'),'minutes') as minutes; minutes -- 1686 minutes There may be something built-in now, but I haven't looked recently. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] interval as hours or minutes ?
am Wed, dem 07.02.2007, um 19:03:35 +0200 mailte Aarni Ruuhimäki folgendes: > Hi all, > > Could anyone please tell an easy way to get total hours or minutes from an > interval ? > > SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times > WHERE > user_id = 1; Perhaps. You can, for instance, with extract(epoch from stop_date_time) retrieve the number of seconds and work with this. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] metaphone and nysiis in postgres
Microsoft SQL server has two extended stored procedures that I need in Postgres: xp_nysiis and xp_metaphone. They do fuzzy matching on strings. Are there Postgres alternatives for these? Or maybe some other way to do phonetic/fuzzy matching that would be as effective? -Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: Wednesday, February 07, 2007 11:45 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] interval as hours or minutes ? am Wed, dem 07.02.2007, um 19:03:35 +0200 mailte Aarni Ruuhimäki folgendes: > Hi all, > > Could anyone please tell an easy way to get total hours or minutes > from an interval ? > > SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM > work_times WHERE user_id = 1; Perhaps. You can, for instance, with extract(epoch from stop_date_time) retrieve the number of seconds and work with this. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] metaphone and nysiis in postgres
"Demel, Jeff" <[EMAIL PROTECTED]> writes: > Microsoft SQL server has two extended stored procedures that I need in > Postgres: xp_nysiis and xp_metaphone. They do fuzzy matching on > strings. Are there Postgres alternatives for these? Never heard of nysiis, but there's metaphone code in contrib/fuzzystrmatch (along with a few other alternatives). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] metaphone and nysiis in postgres
Ah! Cool. Contrib/fuzzystrmatch has metaphone. Looks like it has soundex and levenschtein too. Thanks! -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 07, 2007 2:09 PM To: Demel, Jeff Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] metaphone and nysiis in postgres "Demel, Jeff" <[EMAIL PROTECTED]> writes: > Microsoft SQL server has two extended stored procedures that I need in > Postgres: xp_nysiis and xp_metaphone. They do fuzzy matching on > strings. Are there Postgres alternatives for these? Never heard of nysiis, but there's metaphone code in contrib/fuzzystrmatch (along with a few other alternatives). regards, tom lane This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] metaphone and nysiis in postgres
Can this be installed easily on Windows? -Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Demel, Jeff Sent: Wednesday, February 07, 2007 2:13 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] metaphone and nysiis in postgres Ah! Cool. Contrib/fuzzystrmatch has metaphone. Looks like it has soundex and levenschtein too. Thanks! -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 07, 2007 2:09 PM To: Demel, Jeff Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] metaphone and nysiis in postgres "Demel, Jeff" <[EMAIL PROTECTED]> writes: > Microsoft SQL server has two extended stored procedures that I need in > Postgres: xp_nysiis and xp_metaphone. They do fuzzy matching on > strings. Are there Postgres alternatives for these? Never heard of nysiis, but there's metaphone code in contrib/fuzzystrmatch (along with a few other alternatives). regards, tom lane This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] interval as hours or minutes ?
On Wed, Feb 07, 2007 at 19:03:35 +0200, Aarni Ruuhimäki <[EMAIL PROTECTED]> wrote: > > Could anyone please tell an easy way to get total hours or minutes from an > interval ? Extract the epoch from the interval and divide by the number of seconds in the period of time that applies and apply appropiate rounding. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] interval as hours or minutes ?
On 7 Feb 2007 at 19:03, Aarni Ruuhimäki wrote: > Hi all, > > Could anyone please tell an easy way to get total hours or minutes from an > interval ? > > SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE > user_id = 1; > tot_time > - > 2 days 14:08:44 > > I'd like to have this like ... AS tot_hours ... > tot_hours > - > 62 > > and ... AS tot_minutes ... > tot_minutes > - > 3728 > > Maybe even ... AS tot_hours_minutes_seconds > tot_hours_minutes_seconds > - > 62:08:44 > > > start_date_time and stop_date_time are stored as timestamp without time zone, > using Pg 8.1.5 on CentOs 4.4 select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:00')) as num_seconds; num_seconds 185040 (1 row) select (extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:00')) * interval '1 second') as hours_minutes_seconds; hours_minutes_seconds --- 51:24:00 (1 row) select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:01'))/60 as minutes; minutes -- 3083.983 (1 row) select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:01'))/60) as minutes; minutes - 3084 (1 row) select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:01'))/360) as hours; hours --- 514 (1 row) ---(end of broadcast)--- TIP 1: 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] Seeking quick way to clone a row, but give it a new pk.
I need to create some nearly identical copies of rows in a complicated table. Is there a handy syntax that would let me copy a existing row, but get a new primary key for the copy? I'd then go in an edit the 1 or 2 additional columns that differ. The duplicate would be in the same table as the original. This would save me a bunch of typing. Can it be done? Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Odd PL/PgSQL Error -- relation "X" does not exist when using index expression
Hi, I've tried this on 8.2.1, .2 and .3: I'm having a strange problem with a PL/PGSQL query that executes some dynamic SQL code. The code basically creates a dynamically named table, some indexes, etc. The problem seems to be the an index expression. If I remove it and do a plain index on the column, all works correctly. If I keep it, I get a "relation does not exist" error. If I were to take the generated code and run it manually, it works fine. It only fails when run inside the stored procedure. --- -- This one works CREATE OR REPLACE FUNCTION init_testdata_a(sourceid_ integer) RETURNS void AS $DELIM$ DECLARE sqlquery_ varchar; BEGIN sqlquery_ := ' DROP TABLE IF EXISTS testdata_' || sourceid_ || ' CASCADE; CREATE TABLE testdata_' || sourceid_ || ' ( id SERIAL PRIMARY KEY, data text NOT NULL ); CREATE INDEX testdata_' || sourceid_ || '_content_idx on testdata_' || sourceid_ || ' (data); '; --RAISE NOTICE '%', sqlquery_; EXECUTE sqlquery_; END; $DELIM$ LANGUAGE PLPGSQL; -- Adding the lower() causes it to not work CREATE OR REPLACE FUNCTION init_testdata_b(sourceid_ integer) RETURNS void AS $DELIM$ DECLARE sqlquery_ varchar; BEGIN sqlquery_ := ' DROP TABLE IF EXISTS testdata_' || sourceid_ || ' CASCADE; CREATE TABLE testdata_' || sourceid_ || ' ( id SERIAL PRIMARY KEY, data text NOT NULL ); CREATE INDEX testdata_' || sourceid_ || '_content_idx on testdata_' || sourceid_ || ' ( lower(data) ); '; --RAISE NOTICE '%', sqlquery_; EXECUTE sqlquery_; END; $DELIM$ LANGUAGE PLPGSQL; --- For example, running: => select init_testdata_a(1); works => select init_testdata_b(2); " PL/pgSQL function "init_testdata_b" line 13 at execute statement ERROR: relation "testdata_2" does not exist CONTEXT: SQL statement " ... Any thoughts? -Greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Seeking quick way to clone a row, but give it a new pk.
I need to create some nearly identical copies of rows in a complicated table. Is there a handy syntax that would let me copy a existing row, but get a new primary key for the copy? I'd then go in an edit the 1 or 2 additional columns that differ. The duplicate would be in the same table as the original. This would save me a bunch of typing. Can it be done? INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123; Or something close to that... I suspect if you changed the '*' to the columns you wanted you could also work in the other columns you want to change as well... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] question on passing parameter in sql query
Hi, I don't want to compare with Oracle and postgres. But I have a situation. I am using psql command line tool supplied by postgres. In Oracle I can say select * from emp where emp_id = &1 Oracle will ask: Enter a value for 1: If I enter 10, then Oracle will get the empid=10 What is the equal command in postgres ? Regards skarthi _ Invite your Hotmail contacts to join your friends list with Windows Live Spaces http://clk.atdmt.com/MSN/go/msnnkwsp007001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] question on passing parameter in sql query
On 2/7/07, Karthikeyan Sundaram <[EMAIL PROTECTED]> wrote: I don't want to compare with Oracle and postgres. But I have a situation. I am using psql command line tool supplied by postgres. In Oracle I can say select * from emp where emp_id = &1 Oracle will ask: Enter a value for 1: If I enter 10, then Oracle will get the empid=10 What is the equal command in postgres ? It can be done, but it is a bit "different" and this method is UNIX dependent: test=# create table data (x integer not null primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "data_pkey" for table "data" CREATE TABLE test=# insert into data values (1),(2),(3),(4); INSERT 0 4 test=# \set foo `head -1` 3 test=# \echo :foo 3 test=# select * from data where x = :foo; x --- 3 (1 row) I don't believe it automatically prompts the way Oracle does. It would be nice if there was a built-in "\prompt [VARIABLE] [TEXT]". -- Chad http://www.postgresqlforums.com/
Re: [SQL] Seeking quick way to clone a row, but give it a new pk.
I have a table that describes some properties. It is logically equivalent to: pk userid favorite_color time_zone count 122100 red Pacific7 145101 blue Eastern 7 For various reasons I need to manually add a few rows to this table. Manually copying one users row, then editing it, would be easier than starting from scratch. Hiltibidal, Robert wrote: > What are you trying to accomplish? > > The more normalized a database is the faster it operates, the more > efficient it will be. What you are describing creates a lot of in > efficiencies within a database. > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Seeking quick way to clone a row, but give it a new pk.
Philip Hallstrom wrote: >> I need to create some nearly identical copies of rows in a complicated >> table. >> >> Is there a handy syntax that would let me copy a existing row, but get a >> new primary key for the copy? I'd then go in an edit the 1 or 2 >> additional columns that differ. The duplicate would be in the same >> table as the original. >> >> This would save me a bunch of typing. Can it be done? > > INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123; > > Or something close to that... I suspect if you changed the '*' to the > columns you wanted you could also work in the other columns you want > to change as well... But that will violate the unique primary key constraint: insert into xx_plan_rule select * from xx_plan_rule where rule_id=9; ERROR: duplicate key violates unique constraint "xx_plan_rule_pkey" -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Seeking quick way to clone a row, but give it a new pk.
Στις Πέμπτη 08 Φεβρουάριος 2007 09:19, ο/η Bryce Nesbitt έγραψε: > Philip Hallstrom wrote: > >> I need to create some nearly identical copies of rows in a complicated > >> table. > >> > >> Is there a handy syntax that would let me copy a existing row, but get a > >> new primary key for the copy? I'd then go in an edit the 1 or 2 > >> additional columns that differ. The duplicate would be in the same > >> table as the original. > >> > >> This would save me a bunch of typing. Can it be done? > > > > INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123; > > > > Or something close to that... I suspect if you changed the '*' to the > > columns you wanted you could also work in the other columns you want > > to change as well... > > But that will violate the unique primary key constraint: > > insert into xx_plan_rule select * from xx_plan_rule where rule_id=9; > ERROR: duplicate key violates unique constraint "xx_plan_rule_pkey" If you have that requirement often i would recommend writing a program taking the tablename,id as args, read DB metadata and act accordingly. -- Achilleas Mantzios ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
