Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yi4oU-0003og-F8 for pgsql-sql@arkaria.postgresql.org; Tue, 14 Apr 2015 17:39:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yi4oT-00021G-Tm for pgsql-sql@arkaria.postgresql.org; Tue, 14 Apr 2015 17:39:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yi4oS-0001yy-LA for pgsql-sql@postgresql.org; Tue, 14 Apr 2015 17:39:52 +0000 Received: from andy.depesz.com ([88.198.47.100] helo=depesz.com) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yi4oP-0005sl-Hb for pgsql-sql@postgresql.org; Tue, 14 Apr 2015 17:39:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com; s=20140207; h=In-Reply-To:Content-Type:MIME-Version:References:Reply-To:Message-ID:Subject:Cc:To:Sender:From:Date; bh=sZlPqHyc+VFCbY579TbVcPmoZ3iA/c3sPd/0ngaLhZU=; b=b4/l8qa3kr2BC+yjGrVWjzlevIytvvlT4nJ24B9c/cvEMI3HHeSuqTmR7CidoscbEuJhJkHvnvs7hkE3cq5dlV7qfMKaW6dkre7sVL+SiGuoYUdj2ho/54MjoUZJ5zXx; Received: from andy.depesz.com ([88.198.47.100] helo=depesz.com) by depesz.com with esmtpa (Exim 4.84) (envelope-from ) id 1Yi4oM-0005fz-Iy; Tue, 14 Apr 2015 19:39:46 +0200 Date: Tue, 14 Apr 2015 19:39:46 +0200 From: hubert depesz lubaczewski To: Rishi Ranjan Cc: pgsql-sql@postgresql.org Subject: Re: pgsql-function Message-ID: <20150414173946.GA20194@depesz.com> Reply-To: depesz@depesz.com References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On Tue, Apr 14, 2015 at 03:39:38AM -0700, Rishi Ranjan wrote: > i have data like below and where ap_key is same for two different > first_occurence column . here i need to write a function which can > calculate the difference of two timestamp values for a Ap_Key and then with > difference it should multiply the severity > > AAA_key AP_KEY FIRSTOCCURRENCE SEVERITY > 111 418 3/4/2014 0:00 5 > 111 418 3/4/2014 0:05 0 > 112 12 3/4/2014 0:40 4 > 112 12 3/4/2014 0:45 0 > 113 13 3/4/2014 1:05 3 > 113 13 3/4/2014 1:10 0 > 114 114 3/4/2014 1:30 2 > 114 114 3/4/2014 1:35 0 > 115 35 3/4/2014 2:10 1 > 115 35 3/4/2014 2:15 0 > 116 116 3/4/2014 10:14 4 > 116 116 3/4/2014 10:19 0 > 117 127 3/4/2014 11:45 3 > 117 127 3/4/2014 11:49 0 > 118 118 3/4/2014 12:10 2 > 118 118 3/4/2014 12:14 0 > 119 19 3/4/2014 12:35 1 > 119 19 3/4/2014 12:39 0 > 119 120 3/4/2014 0:00 4 > 119 120 3/4/2014 1:40 0 Given this data, why don't you simply: select AAA_key, AP_KEY, max(SEVERITY), max(FIRSTOCCURRENCE) - min(FIRSTOCCURRENCE) from table group by AAA_key, AP_KEY; and then do whatever math you need on severity or FIRSTOCCURRENCE differences. depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql