From elrich.marx@rorotika.com Thu May  24 09:02:11 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id DF5B81643863
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 06:02:11 -0300 (ADT)
Received: from mail-yw0-f46.google.com ([209.85.213.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <elrich.marx@rorotika.com>) id 1SXTvt-0006ds-KI
	for pgsql-sql@postgresql.org; Thu, 24 May 2012 09:02:11 +0000
Received: by yhmm54 with SMTP id m54so7515086yhm.19
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 02:01:56 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:mime-version:x-mailer:thread-index:date:message-id:subject:to
	:content-type:x-gm-message-state;
	bh=1SAKLrmtXUv06ZHzG7KDGwUCGfNghvaEJsv9LONbEj4=;
	b=mr6Pod5uwbi3VwL3SzAxwA4WuH7idnf8+qE2qyDebCtRa0WVhqB402UV5zPeJCPC32
	pij8p0LHj6N/5xCcTojqNtx7yObYt8xfjKxMYSMqkAJzyKO0bub1IOE2SnXUsInUTUHv
	WnXc8t36uMf9BoSMz918rd9RVikvt9wtYO7DUSnTOCk6LATaPvPCSMB2IXYDVLzI8mYJ
	XiNoSsp5oy2JwQCDEie/RhPlPManBVAau675LDyBMGeS5yMX3j50Ti+n47TiUaGauJZ0
	OKWlDxFP64+yyxV3+gvg7TCSW+Zdf6adroytKZT3qf/DTXk4hNEwRiNQOkTFVDniofGn
	CLqA==
Received: by 10.50.135.4 with SMTP id po4mr15677626igb.60.1337850116100; Thu,
	24 May 2012 02:01:56 -0700 (PDT)
From: Elrich Marx <elrich.marx@rorotika.com>
MIME-Version: 1.0
X-Mailer: Microsoft Office Outlook 12.0
thread-index: Ac05i96insb05IpvTiSXMeJkFAajtQ==
Date: Thu, 24 May 2012 11:01:56 +0200
Message-ID: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
Subject: Flatten table using timestamp and source
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=e89a8f503764eed12a04c0c480c2
X-Gm-Message-State: 
 ALoCoQnIczYPiUJfPY/CFgSQxt9DHDedsJq8XiWlDE34rbyHOU3yCppwZGYel0dipUz4oNO210Xb
X-Pg-Spam-Score: -0.7 (/)
X-Archive-Number: 201205/78
X-Sequence-Number: 36626

--e89a8f503764eed12a04c0c480c2
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Good day.



I am quite new to Postgres, so please bear with me.



I  have a table with data in the following format:



Table name : Time_Source_Table

Source , Stime

1, "2012-05-24 13:00:00"

1, "2012-05-24 13:01:00"

1, "2012-05-24 13:02:00"

2, "2012-05-24 13:03:00"

2, "2012-05-24 13:04:00"

1, "2012-05-24 13:05:00"

1, "2012-05-24 13:06:00"



I=92m trying to get to a result  that flattens the results based on source,
to look like this:

Source, Stime, Etime

1, "2012-05-24 13:00:00","2012-05-24 13:02:00"

2, "2012-05-24 13:03:00","2012-05-24 13:04:00"

1, "2012-05-24 13:05:00","2012-05-24 13:06:00"



Where  Etime is the last Stime for the same source.



Any suggestions would be much appreciated.



Regards

El

--e89a8f503764eed12a04c0c480c2
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; charset=
=3Dus-ascii"><meta name=3D"Generator" content=3D"Microsoft Word 12 (filtere=
d medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style></head><body lang=3D"EN-ZA" link=3D"blue" vlink=3D"purple"><div =
class=3D"WordSection1"><p class=3D"MsoNormal">Good day.</p><p class=3D"MsoN=
ormal">=A0</p><p class=3D"MsoNormal">I am quite new to Postgres, so please =
bear with me.</p>
<p class=3D"MsoNormal">=A0</p><p class=3D"MsoNormal">I =A0have a table with=
 data in the following format:</p><p class=3D"MsoNormal">=A0</p><p class=3D=
"MsoNormal">Table name : Time_Source_Table</p><p class=3D"MsoNormal">Source=
 , Stime</p>
<p class=3D"MsoNormal">1, &quot;2012-05-24 13:00:00&quot;</p><p class=3D"Ms=
oNormal">1, &quot;2012-05-24 13:01:00&quot;</p><p class=3D"MsoNormal">1, &q=
uot;2012-05-24 13:02:00&quot;</p><p class=3D"MsoNormal">2, &quot;2012-05-24=
 13:03:00&quot;</p>
<p class=3D"MsoNormal">2, &quot;2012-05-24 13:04:00&quot;</p><p class=3D"Ms=
oNormal">1, &quot;2012-05-24 13:05:00&quot;</p><p class=3D"MsoNormal">1, &q=
uot;2012-05-24 13:06:00&quot;</p><p class=3D"MsoNormal">=A0</p><p class=3D"=
MsoNormal">
I=92m trying to get to a result=A0 that flattens the results based on sourc=
e, to look like this:</p><p class=3D"MsoNormal">Source, Stime, Etime</p><p =
class=3D"MsoNormal">1, &quot;2012-05-24 13:00:00&quot;,&quot;2012-05-24 13:=
02:00&quot;</p>
<p class=3D"MsoNormal">2, &quot;2012-05-24 13:03:00&quot;,&quot;2012-05-24 =
13:04:00&quot;</p><p class=3D"MsoNormal">1, &quot;2012-05-24 13:05:00&quot;=
,&quot;2012-05-24 13:06:00&quot;</p><p class=3D"MsoNormal">=A0</p><p class=
=3D"MsoNormal">
Where =A0Etime is the last Stime for the same source.</p><p class=3D"MsoNor=
mal">=A0</p><p class=3D"MsoNormal">Any suggestions would be much appreciate=
d.</p><p class=3D"MsoNormal">=A0</p><p class=3D"MsoNormal">Regards</p><p cl=
ass=3D"MsoNormal">
El</p></div></body></html>

--e89a8f503764eed12a04c0c480c2--



From raju@linux-delhi.org Thu May  24 12:00:15 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id B25DD1D29E0
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 09:00:15 -0300 (ADT)
Received: from etc.kandalaya.org ([83.143.86.98] helo=images.kandalaya.org)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <raju@linux-delhi.org>) id 1SXWiA-0006cc-Pd
	for pgsql-sql@postgresql.org; Thu, 24 May 2012 12:00:13 +0000
Received: from mail.linux-delhi.org ([120.59.37.175])
	by images.kandalaya.org (8.14.3/8.14.3/Debian-9) with ESMTP id
	q4OBxg7a016177
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 17:29:45 +0530
Received: from mail.linux-delhi.org (raju@localhost [127.0.0.1])
	by mail.linux-delhi.org (8.14.4/8.14.4/Debian-2) with ESMTP id
	q4OBxL0P021069
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 17:29:23 +0530
From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Flatten table using timestamp and source
Date: Thu, 24 May 2012 17:29:20 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
In-Reply-To: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201205241729.21270.raju@linux-delhi.org>
X-Spam-Status: No, score=-7.5 required=5.0 tests=AWL,BAYES_00,
	DNS_FROM_RFC_BOGUSMX, LOCAL_FROM_RAJU, RCVD_IN_PBL, RCVD_IN_XBL,
	RDNS_NONE autolearn=ham version=3.2.5
X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on
 images.kandalaya.org
X-Virus-Scanned: clamav-milter 0.95.2 at images.kandalaya.org
X-Virus-Status: Clean
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/79
X-Sequence-Number: 36627

On Thursday 24 May 2012, Elrich Marx wrote:
> I am quite new to Postgres, so please bear with me.
>=20
> I  have a table with data in the following format:
>=20
> Table name : Time_Source_Table
>=20
> Source , Stime
> 1, "2012-05-24 13:00:00"
> 1, "2012-05-24 13:01:00"
> 1, "2012-05-24 13:02:00"
> 2, "2012-05-24 13:03:00"
> 2, "2012-05-24 13:04:00"
> 1, "2012-05-24 13:05:00"
> 1, "2012-05-24 13:06:00"
>=20
> I=E2=80=99m trying to get to a result  that flattens the results based on
> source, to look like this:
>=20
> Source, Stime, Etime
> 1, "2012-05-24 13:00:00","2012-05-24 13:02:00"
> 2, "2012-05-24 13:03:00","2012-05-24 13:04:00"
> 1, "2012-05-24 13:05:00","2012-05-24 13:06:00"
>=20
> Where  Etime is the last Stime for the same source.

How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00)=20
and not (1, 13:01:00)?

Regards,

=2D- Raj
=2D-=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F



From elrich.marx@rorotika.com Thu May  24 12:10:42 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 17EF71D29E4
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 09:10:42 -0300 (ADT)
Received: from mail-ob0-f174.google.com ([209.85.214.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <elrich.marx@rorotika.com>) id 1SXWsK-0001Ha-UC
	for pgsql-sql@postgresql.org; Thu, 24 May 2012 12:10:42 +0000
Received: by obbtb18 with SMTP id tb18so12451609obb.19
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2012 05:10:28 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:references:in-reply-to:mime-version:x-mailer:thread-index:date
	:message-id:subject:to:content-type:content-transfer-encoding
	:x-gm-message-state;
	bh=xbtpofLDQnClTBeF8LOFUbmtywRZo54gzH2rUhQlpLk=;
	b=kgbmH2aaSAgc6ePAyalHdPs50uNLPCyE3How8aAKMlOiFjHlv8QTh9RawLyjXTwmSP
	Ab1Sf2P+WziOIqCvNPcc6HtMu2PXG5DO0ojLl/KoxILvs5X25Q8rEeySF+kJmHZzAFrc
	2AbtQrsdvjDpxhAEWbEJiPiVb5mFaFoEBDmgAChzaQJ5+ieswg84KJdOsNmkRzb72PaU
	0/+BZxUgownMoMVaF5p9ZvTImLqwQYEKC+gKZiKQmtfpjRU68041cBBe1e0jajsTHpyB
	9tr+T7ipTrsXrVVDqe/35zXUwjhb9WpKFgoeRvhfwpF2zFezKj8nHZfhQyjNuDsFuACV
	VAZA==
Received: by 10.182.207.41 with SMTP id lt9mr30071099obc.41.1337861428176;
	Thu, 24 May 2012 05:10:28 -0700 (PDT)
From: Elrich Marx <elrich.marx@rorotika.com>
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
	<201205241729.21270.raju@linux-delhi.org>
In-Reply-To: <201205241729.21270.raju@linux-delhi.org>
MIME-Version: 1.0
X-Mailer: Microsoft Office Outlook 12.0
thread-index: Ac05pNEmKhpVBZhzQZO4SyJiaB+zvQAAVq4g
Date: Thu, 24 May 2012 14:10:28 +0200
Message-ID: <43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com>
Subject: Re: Flatten table using timestamp and source
To: raju@linux-delhi.org, pgsql-sql@postgresql.org
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Gm-Message-State: 
 ALoCoQkrulxwNT7cda6YODKPunm9HGzqY9sTtjLx71hf/PrkbG41ksVGekiVj25+rf7zUAPEoVqk
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201205/80
X-Sequence-Number: 36628

HI Raj

If source changes, in this case from 1 to 2, then etime would be the last
value of stime for source =3D1; So for source 1 it starts at stime 13:00 an=
d
continues till 13:02 (etime).

This should result in 3 records, because source is 1, then 2, then 1 again.
I hope this explains ?

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org=
]
On Behalf Of Raj Mathur (??? ?????)
Sent: 24 May 2012 01:59 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Flatten table using timestamp and source

On Thursday 24 May 2012, Elrich Marx wrote:
> I am quite new to Postgres, so please bear with me.
>
> I  have a table with data in the following format:
>
> Table name : Time_Source_Table
>
> Source , Stime
> 1, "2012-05-24 13:00:00"
> 1, "2012-05-24 13:01:00"
> 1, "2012-05-24 13:02:00"
> 2, "2012-05-24 13:03:00"
> 2, "2012-05-24 13:04:00"
> 1, "2012-05-24 13:05:00"
> 1, "2012-05-24 13:06:00"
>
> I=92m trying to get to a result  that flattens the results based on
> source, to look like this:
>
> Source, Stime, Etime
> 1, "2012-05-24 13:00:00","2012-05-24 13:02:00"
> 2, "2012-05-24 13:03:00","2012-05-24 13:04:00"
> 1, "2012-05-24 13:05:00","2012-05-24 13:06:00"
>
> Where  Etime is the last Stime for the same source.

How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and
not (1, 13:01:00)?

Regards,

-- Raj
--=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes
to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



From raju@linux-delhi.org Fri May  25 05:51:20 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 9CA47B53E84
	for <pgsql-sql@postgresql.org>; Fri, 25 May 2012 02:51:20 -0300 (ADT)
Received: from etc.kandalaya.org ([83.143.86.98] helo=images.kandalaya.org)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <raju@linux-delhi.org>) id 1SXnQj-0002wG-E0
	for pgsql-sql@postgresql.org; Fri, 25 May 2012 05:51:19 +0000
Received: from mail.linux-delhi.org ([120.56.168.226])
	by images.kandalaya.org (8.14.3/8.14.3/Debian-9) with ESMTP id
	q4P5ot5I029737
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
	for <pgsql-sql@postgresql.org>; Fri, 25 May 2012 11:20:58 +0530
Received: from mail.linux-delhi.org (raju@localhost [127.0.0.1])
	by mail.linux-delhi.org (8.14.4/8.14.4/Debian-2) with ESMTP id
	q4P5okKL025059
	for <pgsql-sql@postgresql.org>; Fri, 25 May 2012 11:20:46 +0530
From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Flatten table using timestamp and source
Date: Fri, 25 May 2012 11:20:45 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
	<201205241729.21270.raju@linux-delhi.org>
	<43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com>
In-Reply-To: <43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="windows-1252"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201205251120.46272.raju@linux-delhi.org>
X-Spam-Status: No, score=-10.8 required=5.0 tests=AWL,BAYES_00,
	DNS_FROM_RFC_BOGUSMX, LOCAL_FROM_RAJU, RCVD_IN_PBL,
	RDNS_NONE autolearn=ham version=3.2.5
X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on
 images.kandalaya.org
X-Virus-Scanned: clamav-milter 0.95.2 at images.kandalaya.org
X-Virus-Status: Clean
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/85
X-Sequence-Number: 36633

On Thursday 24 May 2012, Elrich Marx wrote:
> If source changes, in this case from 1 to 2, then etime would be the
> last value of stime for source =3D1; So for source 1 it starts at
> stime 13:00 and continues till 13:02 (etime).
>=20
> This should result in 3 records, because source is 1, then 2, then 1
> again. I hope this explains ?

I think I understand.  Here's a partially working example -- it doesn't=20
compute the last interval.  Probably amenable to some severe=20
optimisation too, but then I don't claim to be an SQL expert :)

QUERY
=2D----
with
  first_last as
  (
    select * from
    (
      select
	source,
	time,
	case
	  when
	    lag(source) over (order by time) !=3D source
	    or lag(source) over (order by time) is null
	  then 1 else 0
	end as is_first,
	case
	  when
	    lead(source) over (order by time) !=3D source
	    or lead(source) over (order by time) is null
	  then 1 else 0
	end as is_last
      from
	p
    ) foo
    where
      is_first !=3D 0 or is_last !=3D 0
  )
select
  t1.source,
  start_time,
  end_time
from
  (
    select
      source,
      time as start_time
    from
      first_last
    where
      is_first =3D 1
  ) t1
  join
  (
    select
      source,
      time as end_time,
      is_last
    from
      first_last
    where
      is_last =3D 1
  ) t2
  on
  (
    t1.source =3D t2.source
    and t2.end_time > t1.start_time
    and
      t2.end_time <=20
      (
        select
	  time
	from
	  first_last
	where
	  source !=3D t2.source
	  and time > t1.start_time
	order by
	  time
	limit
	  1
      )
  )
;

DATA SET
=2D-------
 source |        time        =20
=2D-------+---------------------
      1 | 1970-01-01 05:30:01
      1 | 1970-01-01 05:31:01
      1 | 1970-01-01 05:32:01
      6 | 1970-01-01 05:33:01
      6 | 1970-01-01 05:34:01
      6 | 1970-01-01 05:35:01
      6 | 1970-01-01 05:36:01
      6 | 1970-01-01 05:37:01
      2 | 1970-01-01 05:38:01
      2 | 1970-01-01 05:39:01
      2 | 1970-01-01 05:40:01
      2 | 1970-01-01 05:41:01
      6 | 1970-01-01 05:42:01
      6 | 1970-01-01 05:43:01
      6 | 1970-01-01 05:44:01
      6 | 1970-01-01 05:45:01
      6 | 1970-01-01 05:46:01
      4 | 1970-01-01 05:47:01
      4 | 1970-01-01 05:48:01
      4 | 1970-01-01 05:49:01
      4 | 1970-01-01 05:50:01
      4 | 1970-01-01 05:51:01
      0 | 1970-01-01 05:52:01
      0 | 1970-01-01 05:53:01
      0 | 1970-01-01 05:54:01
      0 | 1970-01-01 05:55:01
      7 | 1970-01-01 05:56:01
      7 | 1970-01-01 05:57:01
      7 | 1970-01-01 05:58:01
      8 | 1970-01-01 05:59:01
      8 | 1970-01-01 06:00:01
      8 | 1970-01-01 06:01:01
      8 | 1970-01-01 06:02:01
      8 | 1970-01-01 06:03:01
      1 | 1970-01-01 06:04:01
      1 | 1970-01-01 06:05:01
      1 | 1970-01-01 06:06:01
      1 | 1970-01-01 06:07:01
      1 | 1970-01-01 06:08:01
      1 | 1970-01-01 06:09:01
      1 | 1970-01-01 06:10:01
      8 | 1970-01-01 06:11:01
      8 | 1970-01-01 06:12:01
      8 | 1970-01-01 06:13:01
      6 | 1970-01-01 06:14:01
      6 | 1970-01-01 06:15:01
      6 | 1970-01-01 06:16:01
      4 | 1970-01-01 06:17:01
      4 | 1970-01-01 06:18:01
      9 | 1970-01-01 06:19:01
      9 | 1970-01-01 06:20:01
      9 | 1970-01-01 06:21:01
      9 | 1970-01-01 06:22:01
      2 | 1970-01-01 06:23:01
      2 | 1970-01-01 06:24:01
      2 | 1970-01-01 06:25:01
      1 | 1970-01-01 06:26:01
      1 | 1970-01-01 06:27:01
      1 | 1970-01-01 06:28:01
      1 | 1970-01-01 06:29:01
      4 | 1970-01-01 06:30:01
      4 | 1970-01-01 06:31:01
      4 | 1970-01-01 06:32:01
      4 | 1970-01-01 06:33:01
      4 | 1970-01-01 06:34:01
      0 | 1970-01-01 06:35:01
      0 | 1970-01-01 06:36:01
      0 | 1970-01-01 06:37:01
      9 | 1970-01-01 06:38:01
      9 | 1970-01-01 06:39:01
      9 | 1970-01-01 06:40:01
      9 | 1970-01-01 06:41:01
      9 | 1970-01-01 06:42:01
      1 | 1970-01-01 06:43:01
      1 | 1970-01-01 06:44:01
      1 | 1970-01-01 06:45:01
      8 | 1970-01-01 06:46:01
      8 | 1970-01-01 06:47:01
      8 | 1970-01-01 06:48:01
      8 | 1970-01-01 06:49:01
      8 | 1970-01-01 06:50:01
      0 | 1970-01-01 06:51:01
      0 | 1970-01-01 06:52:01
      0 | 1970-01-01 06:53:01
      0 | 1970-01-01 06:54:01
      0 | 1970-01-01 06:55:01
      0 | 1970-01-01 06:56:01
      0 | 1970-01-01 06:57:01
      2 | 1970-01-01 06:58:01
      2 | 1970-01-01 06:59:01
      2 | 1970-01-01 07:00:01
      2 | 1970-01-01 07:01:01
      2 | 1970-01-01 07:02:01
      2 | 1970-01-01 07:03:01
      2 | 1970-01-01 07:04:01
      2 | 1970-01-01 07:05:01
      4 | 1970-01-01 07:06:01
      4 | 1970-01-01 07:07:01
      2 | 1970-01-01 07:08:01
      2 | 1970-01-01 07:09:01
      2 | 1970-01-01 07:10:01
      2 | 1970-01-01 07:11:01
      2 | 1970-01-01 07:12:01
      7 | 1970-01-01 07:13:01
      7 | 1970-01-01 07:14:01
      9 | 1970-01-01 07:15:01
      9 | 1970-01-01 07:16:01
      9 | 1970-01-01 07:17:01
      7 | 1970-01-01 07:18:01
      7 | 1970-01-01 07:19:01
      7 | 1970-01-01 07:20:01
      7 | 1970-01-01 07:21:01

RESULT
=2D-----
 source |     start_time      |      end_time      =20
=2D-------+---------------------+---------------------
      1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01
      6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01
      2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01
      6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01
      4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01
      0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01
      7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01
      8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01
      1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01
      8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01
      6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01
      4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01
      9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01
      2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01
      1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01
      4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01
      0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01
      9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01
      1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01
      8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01
      0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01
      2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01
      4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01
      2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01
      7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01
      9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01

Regards,

=2D- Raj

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Raj Mathur (???
> ?????)
> Sent: 24 May 2012 01:59 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Flatten table using timestamp and source
>=20
> On Thursday 24 May 2012, Elrich Marx wrote:
> > I am quite new to Postgres, so please bear with me.
> >=20
> > I  have a table with data in the following format:
> >=20
> > Table name : Time_Source_Table
> >=20
> > Source , Stime
> > 1, "2012-05-24 13:00:00"
> > 1, "2012-05-24 13:01:00"
> > 1, "2012-05-24 13:02:00"
> > 2, "2012-05-24 13:03:00"
> > 2, "2012-05-24 13:04:00"
> > 1, "2012-05-24 13:05:00"
> > 1, "2012-05-24 13:06:00"
> >=20
> > I=92m trying to get to a result  that flattens the results based on
> > source, to look like this:
> >=20
> > Source, Stime, Etime
> > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00"
> > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00"
> > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00"
> >=20
> > Where  Etime is the last Stime for the same source.
>=20
> How do you figure out that the Etime for (1, 13:00:00) is (1,
> 13:02:00) and not (1, 13:01:00)?

=2D-=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F



From raju@linux-delhi.org Sat May  26 03:29:21 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id A52E5FAB309
	for <pgsql-sql@postgresql.org>; Sat, 26 May 2012 00:29:21 -0300 (ADT)
Received: from etc.kandalaya.org ([83.143.86.98] helo=images.kandalaya.org)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <raju@linux-delhi.org>) id 1SY7KC-0008Mc-Ql
	for pgsql-sql@postgresql.org; Sat, 26 May 2012 03:05:56 +0000
Received: from mail.linux-delhi.org (triband-del-59.177.64.214.bol.net.in
	[59.177.64.214] (may be forged))
	by images.kandalaya.org (8.14.3/8.14.3/Debian-9) with ESMTP id
	q4Q35VFw011388
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
	for <pgsql-sql@postgresql.org>; Sat, 26 May 2012 08:35:35 +0530
Received: from mail.linux-delhi.org (raju@localhost [127.0.0.1])
	by mail.linux-delhi.org (8.14.4/8.14.4/Debian-2) with ESMTP id
	q4Q2utaG006578
	for <pgsql-sql@postgresql.org>; Sat, 26 May 2012 08:27:15 +0530
From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Flatten table using timestamp and source
Date: Sat, 26 May 2012 08:26:54 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
	<43a608eb27c08adad0ae99c3409eb1fd@mail.gmail.com>
	<201205251120.46272.raju@linux-delhi.org>
In-Reply-To: <201205251120.46272.raju@linux-delhi.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201205260826.54985.raju@linux-delhi.org>
X-Spam-Status: No, score=-7.9 required=5.0 tests=AWL,BAYES_00,
	DNS_FROM_RFC_BOGUSMX,LOCAL_FROM_RAJU,RCVD_IN_PBL,RCVD_IN_SORBS_DUL,
	RCVD_IN_XBL,RDNS_NONE autolearn=ham version=3.2.5
X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on
 images.kandalaya.org
X-Virus-Scanned: clamav-milter 0.95.2 at images.kandalaya.org
X-Virus-Status: Clean
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/92
X-Sequence-Number: 36640

On Friday 25 May 2012, Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=E0=
=A4=BE=E0=A4=A5=E0=A5=81=E0=A4=B0) wrote:
> On Thursday 24 May 2012, Elrich Marx wrote:
> > If source changes, in this case from 1 to 2, then etime would be
> > the last value of stime for source =3D1; So for source 1 it starts
> > at stime 13:00 and continues till 13:02 (etime).
> >=20
> > This should result in 3 records, because source is 1, then 2, then
> > 1 again. I hope this explains ?
>=20
> I think I understand.  Here's a partially working example -- it
> doesn't compute the last interval.  Probably amenable to some severe
> optimisation too, but then I don't claim to be an SQL expert :)

With the last interval computation:

QUERY
=2D----
with
  first_last as
  (
    select * from
    (
      select
	source,
	time,
	case
	  when
	    lag(source) over (order by time) !=3D source
	    or lag(source) over (order by time) is null
	  then 1 else 0
	end as is_first,
	case
	  when
	    lead(source) over (order by time) !=3D source
	    or lead(source) over (order by time) is null
	  then 1 else 0
	end as is_last
      from
	p
    ) foo
    where
      is_first !=3D 0 or is_last !=3D 0
  )
select
  t1.source,
  start_time,
  end_time
from
  (
    select
      source,
      time as start_time
    from
      first_last
    where
      is_first =3D 1
  ) t1
  join
  (
    select
      source,
      time as end_time,
      is_last
    from
      first_last
    where
      is_last =3D 1
  ) t2
  on
  (
    t1.source =3D t2.source
    and t2.end_time > t1.start_time
    and
    (
      (
	t2.end_time <=20
	(
	  select
	    time
	  from
	    first_last
	  where
	    source !=3D t2.source
	    and time > t1.start_time
	  order by
	    time
	  limit
	    1
	)
      )
      or
      (
	t1.start_time =3D=20
	(
	  select
	    time
	  from
	    first_last
	  where
	    is_first =3D 1
	  order by
	    time desc
	  limit
	    1
	)
	and t2.end_time =3D
	(
	  select
	    time
	  from
	    first_last
	  where
	    is_last =3D 1
	  order by
	    time desc
	  limit
	    1
	)
      )
    )
  )
;

RESULT (with same data set as before)
=2D-----
 source |     start_time      |      end_time      =20
=2D-------+---------------------+---------------------
      1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01
      6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01
      2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01
      6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01
      4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01
      0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01
      7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01
      8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01
      1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01
      8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01
      6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01
      4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01
      9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01
      2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01
      1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01
      4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01
      0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01
      9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01
      1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01
      8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01
      0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01
      2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01
      4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01
      2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01
      7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01
      9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01
      7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01

Would be interested in seeing how to shorten and/or optimise this query.

Regards,

=2D- Raj
=2D-=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F



From raju@linux-delhi.org Sat May  26 05:44:06 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id D00B54E4EE3
	for <pgsql-sql@postgresql.org>; Sat, 26 May 2012 02:44:06 -0300 (ADT)
Received: from etc.kandalaya.org ([83.143.86.98] helo=images.kandalaya.org)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <raju@linux-delhi.org>) id 1SY9nH-0002kw-6l
	for pgsql-sql@postgresql.org; Sat, 26 May 2012 05:44:05 +0000
Received: from mail.linux-delhi.org ([120.59.35.223])
	by images.kandalaya.org (8.14.3/8.14.3/Debian-9) with ESMTP id
	q4Q5hjmn007588
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
	for <pgsql-sql@postgresql.org>; Sat, 26 May 2012 11:13:48 +0530
Received: from mail.linux-delhi.org (raju@localhost [127.0.0.1])
	by mail.linux-delhi.org (8.14.4/8.14.4/Debian-2) with ESMTP id
	q4Q5hbGZ001697
	for <pgsql-sql@postgresql.org>; Sat, 26 May 2012 11:13:37 +0530
From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Flatten table using timestamp and source
Date: Sat, 26 May 2012 11:13:36 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <73a6b75b83b37093e5e2751fc59c4323@mail.gmail.com>
	<201205251120.46272.raju@linux-delhi.org>
	<201205260826.54985.raju@linux-delhi.org>
In-Reply-To: <201205260826.54985.raju@linux-delhi.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201205261113.36822.raju@linux-delhi.org>
X-Spam-Status: No, score=-7.5 required=5.0 tests=AWL,BAYES_00,
	DNS_FROM_RFC_BOGUSMX, LOCAL_FROM_RAJU, RCVD_IN_PBL, RCVD_IN_XBL,
	RDNS_NONE autolearn=ham version=3.2.5
X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on
 images.kandalaya.org
X-Virus-Scanned: clamav-milter 0.95.2 at images.kandalaya.org
X-Virus-Status: Clean
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/93
X-Sequence-Number: 36641

On Saturday 26 May 2012, Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=
=E0=A4=BE=E0=A4=A5=E0=A5=81=E0=A4=B0) wrote:
> On Friday 25 May 2012, Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=
=E0=A4=BE=E0=A4=A5=E0=A5=81=E0=A4=B0) wrote:
> > On Thursday 24 May 2012, Elrich Marx wrote:
> > > If source changes, in this case from 1 to 2, then etime would be
> > > the last value of stime for source =3D1; So for source 1 it starts
> > > at stime 13:00 and continues till 13:02 (etime).
> > >=20
> > > This should result in 3 records, because source is 1, then 2,
> > > then 1 again. I hope this explains ?
> >=20
> > I think I understand.  Here's a partially working example -- it
> > doesn't compute the last interval.  Probably amenable to some
> > severe optimisation too, but then I don't claim to be an SQL
> > expert :)
>=20
> With the last interval computation:

Wokeh, much better solution (IMNSHO).  Results are the same as earlier,=20
probably still amenable to optimisation and simplification.

Incidentally, thanks for handing out the problem!  It was a good brain-
teaser (and also a good opportunity to figure out window functions,=20
which I hadn't worked with earlier).

QUERY
=2D----
=2D-
=2D- Compute rows that are the first or the last in an interval.
=2D-
with
  first_last as
  (
    select * from
    (
      select
	source,
	time,
	case
	  when
	    lag(source) over (order by time) !=3D source
	    or lag(source) over (order by time) is null
	  then 1 else 0
	end as is_first,
	case
	  when
	    lead(source) over (order by time) !=3D source
	    or lead(source) over (order by time) is null
	  then 1 else 0
	end as is_last
      from
	p
    ) foo
    where
      is_first !=3D 0 or is_last !=3D 0
  )
=2D-
=2D- Main query
=2D-
select
  source,
  start_time,
  end_time
from
(
=2D- Get each row and the time from the next one
  select
    source,
    time as start_time,
    lead(time)
      over(order by time) as end_time,
    is_first
  from
    first_last
) bar
=2D- Discard rows generated by the is_last row in the inner query
where
  is_first =3D 1;
;

> RESULT (with same data set as before)
> ------
>  source |     start_time      |      end_time
> --------+---------------------+---------------------
>       1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01
>       6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01
>       2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01
>       6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01
>       4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01
>       0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01
>       7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01
>       8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01
>       1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01
>       8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01
>       6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01
>       4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01
>       9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01
>       2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01
>       1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01
>       4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01
>       0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01
>       9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01
>       1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01
>       8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01
>       0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01
>       2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01
>       4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01
>       2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01
>       7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01
>       9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01
>       7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01

Regards,

=2D- Raj
=2D-=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F