[SQL] Cumulative result with increment
Hello, I am struggling with what is probably a simple problem but I am coming up blank. In the interest of full disclosure I am not a very savy programmer by any stretch. I have a table of data from an external source which contains numbers of events per operating group per hour per day, kind of like this: Group | events | duration | timestamp The Group, events, and duration (milliseconds) are all integers in char format. Timestamp is as stated. The records are at 1 hour increments. I need to sum the events and duration fields in 8 hour (shifts, mid to 8AM, 8AM to 4PM, etc). Id like the output to be in table format ala: Group | events | duration | date| shift --+-++---+--- 100 | 26 |00:00:25.00 |2011-01-01 | Day I have a function to convert the duration to an interval already and can make one to do the shift labeling. Its the rest that has me stumped. Any suggestions or direction? As always, your help is greatly appreciated. -- Sincerely, Shawn Tayler -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cumulative result with increment
Thanks Steve, That did the trick! I appreciate the help Shawn On Mon, 2011-02-07 at 14:13 -0800, Steve Crawford wrote: > On 02/07/2011 01:11 PM, Shawn Tayler wrote: > > Hello, > > > > I am struggling with what is probably a simple problem but I am coming > > up blank. In the interest of full disclosure I am not a very savy > > programmer by any stretch. > > > > I have a table of data from an external source which contains numbers of > > events per operating group per hour per day, kind of like this: > > > > Group | events | duration | timestamp > > > > The Group, events, and duration (milliseconds) are all integers in char > > format. Timestamp is as stated. The records are at 1 hour increments. > > I need to sum the events and duration fields in 8 hour (shifts, mid to > > 8AM, 8AM to 4PM, etc). > > > > Id like the output to be in table format ala: > > > > Group | events | duration | date| shift > > --+-++---+--- > > 100 | 26 |00:00:25.00 |2011-01-01 | Day > > > > I have a function to convert the duration to an interval already and can > > make one to do the shift labeling. Its the rest that has me stumped. > > > > Any suggestions or direction? > > > > As always, your help is greatly appreciated. > > > > I'm not sure exactly what you want but it sounds like you just want an > output column that has the shift instead of timestamp. You can then sum > on that column. Don't know what your shifts are called, but this will > give you dog-, day- and night-shifts based on your times: > > case > when extract(hour from '2011-01-12 1600'::timestamptz)< 8 then 'dog' > when extract(hour from now())< 16 then 'day' > else 'night' > end as shiftname > > This can be used for grouping as well as display. > > Cheers, > Steve > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Creating a function with single quotes
Hello, This has me befuddled. I am trying create a simple experiment, rather new to SQL and I am running into an issue with single quotes. All I can find on creating a function states the procedure should be contained within single quotes. My problem comes when I want to use a textual representation of an interval. create function csd_interval(integer) returns interval as 'BEGIN RETURN $1 * interval '1 msec' END;' LANGUAGE 'plpgsql'; it always fails at the '1 msec' point. Suggestions? -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or near "END" LINE 1: SELECT ( $1 * interval '1 msec') END ^ QUERY: SELECT ( $1 * interval '1 msec') END CONTEXT: SQL statement in PL/PgSQL function "f_csd_interval" near line2 edacs=# The error at or near END is curious. There must be something wrong in the line before it but I can't see it. Suggestions? In case it matters, the server is v8.2.11 compiled from source on Slackware 11.0 and the terminal is v8.3.6 running on Ubuntu v8.10. On Fri, 2009-02-20 at 08:11 +, Jasen Betts wrote: > On 2009-02-19, Shawn Tayler wrote: > > Hello, > > > > This has me befuddled. I am trying create a simple experiment, rather > > new to SQL and I am running into an issue with single quotes. All I can > > find on creating a function states the procedure should be contained > > within single quotes. My problem comes when I want to use a textual > > representation of an interval. > > > > create function csd_interval(integer) returns interval as > > 'BEGIN > > RETURN $1 * interval '1 msec' > > END;' > > LANGUAGE 'plpgsql'; > > > > it always fails at the '1 msec' point. > > > > Suggestions? > > you need to quote the inner quotes, > > create function csd_interval(integer) returns interval as > 'BEGIN > RETURN $1 * interval ''1 msec'' > END;' > LANGUAGE 'plpgsql'; > > when the function itself uses single quotes in literals this quickly > becomes confusing, and so "dollar quoting" was invented. > > create function csd_interval(integer) returns interval as > $$BEGIN > RETURN $1 * interval '1 msec' > END;$$ > LANGUAGE 'plpgsql'; > -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
Hi Leif! Thank you to you and the group. Worked like a charm. The SQL language was the key Shawn On Fri, 2009-02-20 at 15:12 +0100, Leif B. Kristensen wrote: > You should place a semicolon at the end of the RETURN line, and > remove > the one after END, > > BTW, simple functions as this are better written in the SQL language. > I > can't speak for the validity of the code itself, but you can rewrite > it > as > > create function f_csd_interval(integer) returns interval as $$ > SELECT $1 * interval '1 msec' > $$ LANGUAGE SQL; -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: Fwd: Re: [SQL] Creating a function with single quotes
Ah.. Missed that one. Thank you Adrian! Shawn On Fri, 2009-02-20 at 06:27 -0800, Adrian Klaver wrote: > Actually you need both semicolons. One after the RETURN statement and > one after > the END statement > See below for full details: > http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] varchar value comparisons not working?
Hello, I'm doing a quick comparison between a couple tables, trying to cleanup some inconsistencies, and what should be a simple check between 2 tables doesn't seem to be working. psql is 8.3.7 and server is 8.2.13. I run the following: select sfd.lid as sflid,sd.lid as slid,sfd.serial from sfd,shawns_data sd where sfd.serial = sd.serial_number order by sfd.lid; the lid columns in both tables should be identical, but as you see in this sample, they do differ: sflid | slid | serial ---+---+-- 14056 | 14056 | 9614583 14057 | | 9614984 14058 | 14058 | 9614737 14059 | 14059 | 9614579 14060 | | 9614827 14061 | 14061 | 9614726 14062 | 14062 | 9614966 14063 | 14063 | 9615079 So running this query: select count(*) from sfd,shawns_data sd where sfd.serial = sd.serial_number and sfd.lid != sd.lid; I should show some rows that do not match, at least 2 (there are more than shown). But instead I get this: count --- 0 (1 row) What am I doing wrong? -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
