<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1543" name=GENERATOR></HEAD>
<BODY
style="WORD-WRAP: break-word; khtml-nbsp-mode: space; khtml-line-break: after-white-space">
<DIV dir=ltr align=left>
<DIV dir=ltr align=left>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2>That means a lo<SPAN class=143554014-06112006>t to
</SPAN>me coming from you. I think I cheated though - since I'm sure I've
seen that trick before.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial><FONT
color=#0000ff><FONT size=2>One thing I love about SQL is that you can do so much
with it with such little code. The trick is <SPAN
class=110254114-06112006>to </SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial><FONT
color=#0000ff><FONT size=2><STRONG><SPAN class=110254114-06112006>state
</SPAN>the problem in the shortest possible way you can and your statement is
the answer.</STRONG></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=148403814-06112006><FONT face=Arial
color=#0000ff size=2>In this case</FONT></SPAN></DIV></DIV></DIV>
<DIV> </DIV>
<DIV><SPAN class=110254114-06112006></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>G<SPAN class=110254114-06112006>ive me all the
ignition records that are
<STRONG>off</STRONG>.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT color=#0000ff><FONT size=2><SPAN
class=110254114-06112006></SPAN></FONT></FONT></FONT><SPAN
class=110254114-06112006></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>F<SPAN class=110254114-06112006>or each<STRONG> off</STRONG> record
give me the minimum <STRONG>on</STRONG> record that is still greater than my
<STRONG>off</STRONG>.</SPAN></FONT></FONT></FONT><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Paul
Ramsey<BR><B>Sent:</B> Friday, November 03, 2006 8:03 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Help with
query<BR></FONT><BR></DIV>
<DIV></DIV>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 name="PersonName"
namespaceuri="urn:schemas-microsoft-com:office:smarttags">
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Regina,<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">You’re absolutely the
GREATEST! It worked like a charm!<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Thank you VERY
much!<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">All the
best,<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<DIV>
<P class=MsoNormal><U><FONT face="Bauhaus 93" color=#0080c0 size=5><SPAN
style="FONT-SIZE: 18pt; FONT-FAMILY: " 93?;color:#0080c0?="" bauhaus="">Pedro
Doria Meunier</SPAN></FONT></U><FONT color=navy><SPAN
style="COLOR: navy"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">(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 face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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 face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">ICQ -
308-182-126<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Skype:
pdoriam</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><O:P></O:P></SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; 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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">I'm going to assume
your table has 2 fields</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">ignition_on which is
a boolean</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">actiontime which is a
timestamp</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">and your table is
called ignition_log</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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 face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">FROM ignition_log
igoff INNER JOIN ignition_log igon </SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">
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 face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">GROUP BY
igoff.actiontime</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><O:P></O:P></SPAN></FONT></P>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt"><B><FONT face=Tahoma
size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I have the following setup in a
table:<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks in
advance!<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><U><FONT face="Bauhaus 93" color=#0080c0 size=5><SPAN
style="FONT-SIZE: 18pt; FONT-FAMILY: " 93?;color:#0080c0?="" bauhaus="">Pedro
Doria Meunier</SPAN></FONT></U><O:P></O:P></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">(351) 91 302 49 72 - (351) 96 247
99 12</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ICQ -
308-182-126<O:P></O:P></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Skype:
pdoriam</SPAN></FONT><O:P></O:P></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><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: 0px">_______________________________________________</DIV>
<DIV style="MARGIN: 0px">postgis-users mailing list</DIV>
<DIV style="MARGIN: 0px"><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV>
<DIV style="MARGIN: 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>