[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
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] why SELECT query needs relationname (schemaName.tableName)
Hi All, I have a query regarding database. I have database 'customer' which is having schema 'cust.' I have added my tables and data into 'cust'. But when I try to run the sql queries against this schema I need to add 'cust' as a relationName for every query. But same is not applicable for public schema. Same is applicable when I query with jdbc. Can any one guide me how can I make my 'cust' schema to work like public schema means for all the sqls I don't need to append schema name as relationanme. Thanks Amar
Re: [SQL] why SELECT query needs relationname (schemaName.tableName)
You need to modify the search_path for that user. Read the first section of this page very carefully: http://www.postgresql.org/docs/8.4/static/runtime-config-client.html The schema that is used for new objects that don't have an explicit schema is always the first schema in the search_path. You can permanently alter the search_path for the user like this: alter user set search_path to cust, other_schema, public, pg_catalog; There's more info on search_path here: http://www.postgresql.org/docs/8.4/static/ddl-schemas.html On Mon, Feb 7, 2011 at 10:56 PM, Amar Dhole wrote: > Hi All, > > > > I have a query regarding database. > > > > I have database ‘customer’ which is having schema ‘cust.’ > > > > I have added my tables and data into ‘cust’. But when I try to run the sql > queries against this schema I need to add ‘cust’ as a relationName for every > query. But same is not applicable for public schema. > > > > Same is applicable when I query with jdbc. > > > > Can any one guide me how can I make my ‘cust’ schema to work like public > schema means for all the sqls I don’t need to append schema name as > relationanme. > > > Thanks > > Amar >
