<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">Ye gods, Regina, you're amazing.<DIV><BR><DIV><DIV>On 3-Nov-06, at 2:14 PM, Pedro Doria Meunier wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite"><O:SMARTTAGTYPE namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PersonName"> <DIV class="Section1"><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Regina,<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN lang="EN-GB" style="font-size:10.0pt;font-family:Arial;color:navy">You’re absolutely the GREATEST! It worked like a charm!<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN lang="EN-GB" style="font-size:10.0pt;font-family:Arial;color:navy"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN lang="EN-GB" style="font-size:10.0pt;font-family:Arial;color:navy">Thank you VERY much!<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN lang="EN-GB" style="font-size:10.0pt;font-family:Arial;color:navy"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN lang="EN-GB" style="font-size:10.0pt;font-family:Arial;color:navy">All the best,<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN lang="EN-GB" style="font-size:10.0pt;font-family:Arial;color:navy"><O:P> </O:P></SPAN></FONT></P> <DIV><P class="MsoNormal"><U><FONT size="5" color="#0080c0" face="Bauhaus 93"><SPAN style="font-size:18.0pt;font-family:" bauhaus="" 93";color:#0080c0"="">Pedro Doria Meunier</SPAN></FONT></U><FONT color="navy"><SPAN style="color:navy"><O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">(351) 91 302 49 72 - (351) 96 247 99 12</SPAN></FONT><FONT color="navy"><SPAN style="color:navy"><O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">MSN - <A href="mailto:pdoriam@hotmail.com">pdoriam@hotmail.com</A></SPAN></FONT><FONT color="navy"><SPAN style="color:navy"><O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">ICQ - 308-182-126<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Skype: pdoriam</SPAN></FONT><FONT color="navy"><SPAN style="color:navy"><O:P></O:P></SPAN></FONT></P> </DIV><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"><O:P> </O:P></SPAN></FONT></P> <DIV> <DIV class="MsoNormal" align="center" style="text-align:center"><FONT size="3" face="Times New Roman"><SPAN lang="EN-US" style="font-size:12.0pt"> <HR size="2" width="100%" align="center" tabindex="-1"> </SPAN></FONT></DIV><P class="MsoNormal"><B><FONT size="2" face="Tahoma"><SPAN lang="EN-US" style="font-size:10.0pt;font-family:Tahoma;font-weight:bold">From:</SPAN></FONT></B><FONT size="2" face="Tahoma"><SPAN lang="EN-US" style="font-size:10.0pt;font-family:Tahoma"> postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] <B><SPAN style="font-weight:bold">On Behalf Of </SPAN></B>Obe, Regina<BR> <B><SPAN style="font-weight:bold">Sent:</SPAN></B> sexta-feira, 3 de Novembro de 2006 21:04<BR> <B><SPAN style="font-weight:bold">To:</SPAN></B> <ST1:PERSONNAME w:st="on">PostGIS Users Discussion</ST1:PERSONNAME><BR> <B><SPAN style="font-weight:bold">Subject:</SPAN></B> RE: [postgis-users] Help with query</SPAN></FONT><SPAN lang="EN-US"><O:P></O:P></SPAN></P> </DIV><P class="MsoNormal"><FONT size="3" face="Times New Roman"><SPAN style="font-size: 12.0pt"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">I'm going to assume your table has 2 fields</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">ignition_on which is a boolean</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">actiontime which is a timestamp</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="3" face="Times New Roman"><SPAN style="font-size: 12.0pt"> <O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">and your table is called ignition_log</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="3" face="Times New Roman"><SPAN style="font-size: 12.0pt"> <O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">I would try the following - haven't tested it so don't know if it will work</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="3" face="Times New Roman"><SPAN style="font-size: 12.0pt"> <O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">SELECT igoff.actiontime as ignition_off_time, MIN(igon.actiontime) as ignition_on_time, MIN(igon.actiontime) - igoff.actiontime as igdifference</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">FROM ignition_log igoff INNER JOIN ignition_log igon </SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue"> ON igoff.actiontime < igon.actiontime AND igoff.ignition_on = false AND igon.ignition_on = true</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" color="blue" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:blue">GROUP BY igoff.actiontime</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="3" face="Times New Roman"><SPAN style="font-size: 12.0pt"><O:P> </O:P></SPAN></FONT></P> <DIV class="MsoNormal" align="center" style="text-align:center"><FONT size="3" face="Times New Roman"><SPAN lang="EN-US" style="font-size:12.0pt"> <HR size="2" width="100%" align="center" tabindex="-1"> </SPAN></FONT></DIV><P class="MsoNormal" style="margin-bottom:12.0pt"><B><FONT size="2" face="Tahoma"><SPAN lang="EN-US" style="font-size:10.0pt;font-family:Tahoma;font-weight:bold">From:</SPAN></FONT></B><FONT size="2" face="Tahoma"><SPAN lang="EN-US" style="font-size:10.0pt;font-family:Tahoma"> postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] <B><SPAN style="font-weight:bold">On Behalf Of </SPAN></B>Pedro Doria Meunier<BR> <B><SPAN style="font-weight:bold">Sent:</SPAN></B> Friday, November 03, 2006 2:31 PM<BR> <B><SPAN style="font-weight:bold">To:</SPAN></B> '<ST1:PERSONNAME w:st="on">PostGIS Users Discussion</ST1:PERSONNAME>'<BR> <B><SPAN style="font-weight:bold">Subject:</SPAN></B> [postgis-users] Help with query</SPAN></FONT><SPAN lang="EN-US"><O:P></O:P></SPAN></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">This is probably not the best place to be asking such a (lame) question but perhaps someone is gracious enough to lend me a hand… ;-)<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">I have the following setup in a table:<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">The first record which is to be found (ok easy enough :D) with a timestamp meets a certain condition (ignition off)<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">The following record is for the event of ignition on again with a timestamp.<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">So the question here is: how can I compute the time difference between these two records in a single query?<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">Better yet: finding all the records that meet the first condition (ignition off) and the immediately following records as to compute the time difference. ;-)<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">Thanks in advance!<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"><O:P> </O:P></SPAN></FONT></P><P class="MsoNormal"><U><FONT size="5" color="#0080c0" face="Bauhaus 93"><SPAN style="font-size:18.0pt;font-family:" bauhaus="" 93";color:#0080c0"="">Pedro Doria Meunier</SPAN></FONT></U><O:P></O:P></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN style="font-size:10.0pt; font-family:Arial">(351) 91 302 49 72 - (351) 96 247 99 12</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN style="font-size:10.0pt; font-family:Arial">MSN - <A href="mailto:pdoriam@hotmail.com">pdoriam@hotmail.com</A></SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN style="font-size:10.0pt; font-family:Arial">ICQ - 308-182-126<O:P></O:P></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" face="Arial"><SPAN style="font-size:10.0pt; font-family:Arial">Skype: pdoriam</SPAN></FONT><O:P></O:P></P><P class="MsoNormal"><FONT size="3" face="Times New Roman"><SPAN style="font-size: 12.0pt"><O:P> </O:P></SPAN></FONT></P> </DIV><DIV><BR class="khtml-block-placeholder"></DIV><HR size="1"><DIV><BR class="khtml-block-placeholder"></DIV><BR><P><STRONG><BR> The substance of this message, including any attachments, may be<BR> confidential, legally<BR> privileged and/or exempt from disclosure pursuant to Massachusetts<BR> law. It is intended<BR> solely for the addressee. If you received this in error, please<BR> contact the sender and<BR> delete the material from any computer.<BR> </STRONG></P></O:SMARTTAGTYPE><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>