Re: [SQL] Find periods for a given... action set?
Hello Mario Splivalo, I found some solution to your question but it is not exact one. ** SELECT a.user_id, a.action_time- (select a2.action_time from actions a2 where a2.user_id = (select a3.user_id from actions a3 where a3.user_id=a.user_id and a3.action_mark='BEGIN' and a3.action_time wrote: From: Mario Splivalo Subject: [SQL] Find periods for a given... action set? To: pgsql-sql@postgresql.org Date: Friday, 12 June, 2009, 1:54 PM I have a table where there are actinos for some user logged. It's part of the MPI system of some sort. For every user, action type and time of the action is logged. There are many action types but the ones which are of interest to me are BEGIN and END. I need to find the durations for all the periods between BEGINs and ENDs, for each user. Here is what the table looks like: CREATE TABLE actions ( user_id integer, action_mark character varying, action_time timestamp ) There are no PK constraints because those columns are excerpt from a action_log table, there is a message_id column which is a PK; user_id and action_mark are FKs to the users and actions tables. Now that I look at it, in the above table PK would be (user_id, action_time), but there are no PKs as for now :) Some example data: INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00'); INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22'); INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01'); INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00'); INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02'); INSERT INTO actions VALUES (1, 'END', '2009-02-02 13:21:01'); INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01'); INSERT INTO actions VALUES (2, 'END', '2009-02-02 16:11:21'); INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31'); INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12'); INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21'); INSERT INTO actions VALUES (2, 'END', '2009-02-02 19:00:01'); INSERT INTO actions VALUES (1, 'END', '2009-02-02 19:10:01'); INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01'); Now, for each user, i need to find all periods 'enclosed' with BEGIN/END action_type pairs. If I have a BEGIN and no END, than there is no period. So, if I have, for some user, BEGIN-END-BEGIN-END-BEGIN, there are only two periods. Also, if there are consecutive BEGINS, only the last one counts: BEGIN-END-BEGIN-BEGIN-BEGIN-END-BEGIN - again, only two periods. The results I would like to get look like this: user_id action_duration 1 01:21:01 1 00:57:40 2 04:09:20 2 00:48:49 User_id 3 has just the BEGIN - there is no period there, because I don't have and endpoint. Similarly, user_id 1 has BEGIN as the last action - just two periods for user_id 1, because last BEGIN denotes 'period in progress'. Also, user_id 1 has following actions happened, time ordered: BEGIN, END, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN canceled second BEGIN and all the actions between second and third BEGIN. Now, using some imperative Python, Perl, C, whatever, it's not that complicated to get what I want, but I would realy like to have it solved within plain SQL :) So, if anyone has any suggestions, I would greatly appreciate them. Mike -- 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] Bucketing Row Data in columns
Hello Mr. Sandeep Bandela, I have gone through your scenario and come up with the following solution. SELECT USER_ID, CREATE_TIMESTAMP::DATE, SUM(AMOUNT) FROM ORDERS GROUP BY USER_ID, CREATE_TIMESTAMP ORDER BY USER_ID, CREATE_TIMESTAMP; Maybe you need to do little modification on the query to get what you want. Best Regards James Kitambara Database Administrator -ORGINAL MESSAGE --- On Wed, 24/6/09, Sandeep wrote: From: Sandeep Subject: [SQL] Bucketing Row Data in columns To: pgsql-sql@postgresql.org Date: Wednesday, 24 June, 2009, 5:39 PM Hi all, I need help on creating a sql, not a problem even if its pl/sql I have orders table schema is as follow orders(order_id,user_id, create_timestamp, amount) and I want to generate a report like for the past 3 days bucketing purchases i.e SUM(amount) every day in columns i.e result will be having these columns. (user_id, amount_day1, amount_day2, amount_day3) ex: am leaving order_id assume they are auto incrementing and unique, date format dd/mm/ (user_id, create_timestamp, amount) (user1, 01/01/2009,100) (user1, 01/01/2009,100) (user2, 01/01/2009,100) (user2, 02/01/2009,100) (user2, 02/01/2009,100) (user1, 02/01/2009,100) (user2, 03/01/2009,100) (user2, 03/01/2009,100) (user3, 03/01/2009,100) result (user_id, amount_day1, amount_day2, amount_day3) (user1, 200, 200, 0) (user2, 100, 200, 200) (user3, 0, 0, 100) hope you guys got what I am trying to generate through sql. I could get this data in each row, but I want it in columns. Can anyone help me on this? lets assume the buckets are fixed i.e 3 only. but I wish to get them unlimited i.e day 1 to day 20. Regards Sandeep Bandela
Fw: Re: [SQL] help
--- On Thu, 27/5/10, James Kitambara wrote:
From: James Kitambara
Subject: Re: [SQL] help
To: "Nicholas I"
Date: Thursday, 27 May, 2010, 14:50
Hello Mr. Nicholas,
You can try the following:
THIS IS WHAT I TRIED TO SOLVE YOUR PROBLEM, BUT IN ORACLE DBMS
(SORRY I DON'T HAVE POSTGRESQL INSTALL ON MY COMPUTER)
i GUESS YOU CAN TRY TO CHANGE THE SQL COMMANDS IN POSTGRESQL
create table numbers (
id number not null primary key,
description varchar2(100)
);
insert into numbers values (300, 'Three hundred (300)');
insert into numbers values (350, 'Three hundred fifty (350)');
insert into numbers values (6709, 'Six thousand seven hundred and nine (6709)');
select id, description, substr(description, instr(description, '(') +1,
instr(description, ')')-(instr(description, '(') +1)) extracted_character
from numbers;
This is the Results:
ID DESCRIPTION EXTRACTED_CHARACTERS
300 Three hundred 300
350 Tree hundred fifty 350
6709 Six thousand seven hundred and nine 6709
But if you run the following query you will get exactlly what:
select substr(description, instr(description, '(') +1, instr(description,
')')-(instr(description, '(') +1)) extracted_character
from numbers:
This is the Results:
EXTRACTED_CHARACTERS
-
300
350
6709
James Kitambara
B.Sc. With Computer Science and Statistics (Hons),
--- On Wed, 5/5/10, Nicholas I wrote:
From: Nicholas I
Subject: [SQL] help
To: pgsql-sql@postgresql.org
Date: Wednesday, 5 May, 2010, 4:05
Hi,
I have a table in which the data's are entered like,
Example:
One (1)
Two (2)
Three (3)
I want to extract the data which is only within the parentheses.
that is
1
2
3
Thank You
Nicholas I
Re: [SQL] insert into help
Hello Guillaume Lelarge !
I suggest you try the following question:
RE-CREATE YOUR TABLES AS FOLLOW:
CREATE SEQUENCE table1_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 1000
START 1
CACHE 1;
CREATE TABLE TABLE1 (
ID INTEGER NOT NULL DEFAULT nextval('table1_id_seq'::regclass)
, NAME VARCHAR(200) NOT NULL
);
CREATE TABLE TABLE2 (
NAME VARCHAR(200) NOT NULL
);
INSERTING THE DATA--
INSERT INTO TABLE1 (NAME) SELECT NAME FROM TABLE2;
Note: The ID in Table1 will be generated automaticale because of
DEFAULT nextval('table1_id_seq'::regclass)
James Kitambara Computer System Analyst and Programmer
Bank of Tanzania,
P.O. Box 2939, Mobile : +255 71 3307632, Dar es Salaam, Tanzania.
--- On Wed, 22/9/10, Guillaume Lelarge wrote:
From: Guillaume Lelarge
Subject: Re: [SQL] insert into help
To: "Nicholas I"
Cc: pgsql-sql@postgresql.org
Date: Wednesday, 22 September, 2010, 8:35
Le 22/09/2010 09:32, Nicholas I a écrit :
> Hi,
>
> i have two tables,
> ---
> *table1
>
> id type serial, name varchar;*
> *--
> table 2
>
> name varchar;*
> ---
>
> i want to insert the values of table 2 into table 1, with automatic id's.
>
> insert into table1(select * from table2);
>
> is not working, how can i append the data to table 1 with auto incremented
> or nextval.
>
INSERT INTO table1 (name) SELECT name FROM table2;
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] MAY I HAVE YOUR ASSISTANCE
Please members of the PGSQL-SQL, I have one problem with the user table. I want to hide the password for the users. The table format is: user ( user_id, user_name, password) But I want the password to be encrypted so that when other users send the query:SELECT * FROM USER; The password must be gabbage. i.e. THE OUTPUT SHOULG LOOK LIKE THIS user_id | user_name | password - 0001 | ADMIN | %3g4767teghh890)* 0002 | USER | [EMAIL PROTECTED]&&^*8 0003 | IT-MANAGER | ^8Y3U(-43873GDG? I appriciate for your assistance in advance, Sincerely yours, James Kitambara, PostgreSQL user.
[SQL] May I have an assistance on CREATE TABLE Command
Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara
Re: [SQL] May I have an assistance on CREATE TABLE Command
Thank you ! But I think that there is a solution. If it happens that you have the following data in your tables REGION -- region_id | region_name -- 11| Dodoma 22| Tabora 99 | Dar es Salaam THIS ROW WAS SUPPOSED TO BE: '33', 'Dar es Salaam' DISTRICT dist_id | dist_name | region_id 001 | Kongwa | 11 002 | Ilala | 99 003 | Temeke |99 003 | Kinondoni | 99 For this UPDATE I wanted, when I change the region _id from '99' to '33' of the last ROW in REGION table AUTOMATICALLY to change the last three ROWS of the DISTRICT table which reference to '99', 'Dar es Salaam'. If I do this, I will get the error message "You can not change region_id other tables are reference to it. HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY) ---ORGINAL MESSAGE--- I think (one of) the point(s) of id fields is not to change them. You can update the region_name field (eg a correct a misspelling), but the id stays the same. That way the district stays connected to the same region. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>> Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara
[SQL] Problem with pg_connect() in PHP
Dear Members of
I have installed the Apache 2.0.61, PHP 5.2.4 and PostgreSQL 8.1 on my local
computer.
All three software were successfully tested. I changed
“;extension=php_pgsql.dll” to
“extension=php_pgsql.dll” in the php.ini file in order to enable PostgreSQL
in PHP.
The problem comes when I try to connect to the PostgreSQL Database using php
function pg_connect
$dbconn = pg_connect("host=".PG_HOST_NAME." port=".PG_PORT_NUM."
dbname=".PG_DB_NAME." user=".PG_USER." password=".PG_PASSWORD);
All the arguments in the function pg_connect() are defined.
Unfortunately I am getting the Fatal error: “Call to undefined function
pg_connect() in C:\Web\html\Staff_Management\example1.php on line 23”
C:\Web\html is my document root.
What could be the possible mistake?
Anyone to assist me!
Best regards,
James Kitambara
Re: [SQL] pg_dump in windows
Hello Ramasubramanian, You don't have to worry about the password. You need to enter the password of the person doing the backup. One the password is provided the backup is executed. I think that anyone executing the pg_dump must be a Super user. Best Regards, James Kitambara. ORGINAL MESSAGE-- --- On Fri, 19/9/08, Ramasubramanian <[EMAIL PROTECTED]> wrote: From: Ramasubramanian <[EMAIL PROTECTED]> Subject: [SQL] pg_dump in windows To: pgsql-sql@postgresql.org Date: Friday, 19 September, 2008, 11:18 AM HI all, I am facing a problem while taking a back up in windows. It is asking for a password while using pg_dump. Why it is asking for a password ?(it is not asking password in linux) Give some details about fe_sendauth in postgres . How i can avoid asking password in windows shile taking backup? Thanks and Regards, Ramasubramanian.G|Software Engineer - Delivery E-mail:[EMAIL PROTECTED] | Extn: 1607 Sobha Renaissance Information Technology (P) Ltd. An SEI-CMM, P-CMM & SSE-CMM Level 5 Company BS ISO/IEC 27001:2005 & ISO 9001:2000 Certified A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) | A Six Sigma Practice Company Phone: + 91 80 41951999 | Fax: + 91 80 41523300 A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) | A Six Sigma Practice Company Phone: + 91 80 41951999 | Fax: + 91 80 41523300 URL: www.renaissance-it.com | Video Conference: + 91 80 4125 ?
Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
Dear Srikanth, You can solve your problem by doing this THE SQL IS AS FOLLOWS ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE TABLE NAME time_interval COUNT (*) FROM (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" from time_interval where end_ts-start_ts >= '1 hour' and '2008-12-07 07:59:59' between start_ts and end_ts) AS COUNT ; --ORGINAL MESSAGE-- From: Richard Huxton To: Srikanth Cc: pgsql-sql@postgresql.org Sent: Tuesday, 17 March, 2009 18:06:09 Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Dear all, I have a table that records User Login Sessions with two timestamp fields. Basically Start of Session and End of a Session (start_ts and end_ts). Each row in the table identifies a session which a customer has used. Data from the table (session): - customer_id | log_session_id | start_ts | end_ts -+-++ 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182 100608 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07:15.947509 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 13:56:58.394577 The requirement is as follows, I have to find out how many User Sessions that were present in any given "1 HOUR TIME PERIOD". A single User Session can span across many days. Example: start_ts | end_ts 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218 - Let me explain a scenario, I have to find out the number of sessions during the interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'. If I have to find the number of sessions present at any instant time say '07/01/2009 11:25:25', i can easily find out using the following Query, select count(log_session_id) from session where '07/01/2009 11:25:25' between start_ts and end_ts ; But, I have to find the number of sessions present during the "HOUR INTERVAL" '07/01/2009 11:00:00' to '07/01/2009 11:59:59'. I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searching archives without any success either. I feel this is a general requirement and this topic should have already been discussed. Could someone help me solve this please ? Any lead would do, like some special postgres-function or any other means. Many Thanks,
Re: [SQL] Need help combining 2 tables together
Dear Richard Ekblom, I think Mr. Adrian Klaver gave you the solution. Mine is the similar solution SELECT message.id,topic.topic,message.message FROM topics, messages WHERE message.topic=topic.id order by message.id; After executing this query you will get the following: id | topic | message +--+-- 1 | My favorite food | I like lasagna! 2 | My favorite food | Pizza is also a favorite 3 | Are squares better then circles? | I like circles, they : remind me of pizza Best Regards, Muhoji James Kitambara Database Administrator, B.Sc. With Computer Science and Statistics (Hons), National Bureau of Statistics, P.O. Box 796, Tel : +255 22 2122722/3 Fax: +255 22 2130852, Mobile : +255 71 3307632, Dar es Salaam, Tanzania. -ORGINAL MESSAGE On Friday 22 May 2009 6:48:43 am Richard Ekblom wrote: > Hello > > I have frequently encountered the need of combining two tables into one. > First, please take a look at the following table setups... > > CREATE TABLE topics ( > id SERIAL PRIMARY KEY, > topic TEXT NOT NULL > ); > > CREATE TABLE messages ( > id SERIAL PRIMARY KEY, > topic INTEGER REFERENCES topics(id), > message TEXT NOT NULL > ); > > Example of a topics table: > ID TOPIC > 1 Are squares better then circles? > 2 My favorite food > > Example of a messages table: > ID TOPIC MESSAGE > 1 2 I like lasagna! > 2 2 Pizza is also a favorite > 3 1 I like circles, they remind me of pizza > > Notice that the number of topics may differ from the number of messages. > > Now I want to combine these tables with a single SELECT to get... > > Combined table: > ID TOPIC MESSAGE > 1 My favorite food I like lasagna! > 2 My favorite food Pizza is also a favorite > 3 Are squares better then circles? I like circles, they remind me > of pizza > > I have seen different examples of this with something called JOIN but > they always give me only two rows. How can I do this when the two tables > may have different sizes to produce exactly the combined table above??? > >
