<html><div style='background-color:'><P><BR><BR></P>
<DIV>
<P><BR><BR></P><BR><BR><BR>
<DIV>
<P>YOUR PROBLEN </P>
<P>ALLSOLUTIONS</P>
<P><A href="http://es.facebox.com/andres_aragon">http://es.facebox.com/andres_aragon</A></P>
<P>SOLOESCRIVE</P>
<P>chauuu att:andres<IMG height=19 src="http://graphics.hotmail.com/i.p.emcool.gif" width=19></P></DIV>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #a0c6e5 2px solid; MARGIN-RIGHT: 0px"><FONT style="FONT-SIZE: 11px; FONT-FAMILY: tahoma,sans-serif">
<HR color=#a0c6e5 SIZE=1>
From: <I>"Obe, Regina" <robe.dnd@cityofboston.gov></I><BR>Reply-To: <I>PostGIS Users Discussion <postgis-users@postgis.refractions.net></I><BR>To: <I>"PostGIS Users Discussion" <postgis-users@postgis.refractions.net></I><BR>Subject: <I>RE: [postgis-users] Help with query</I><BR>Date: <I>Fri, 3 Nov 2006 16:04:14 -0500</I><BR><BR>
<META content="Microsoft SafeHTML" name=Generator>
<STYLE>
@page Section1
{size:595.3pt 841.9pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;}
P.MsoNormal
{font-size:12pt;margin:0cm 0cm 0pt;font-family:'Times New Roman';}
LI.MsoNormal
{font-size:12pt;margin:0cm 0cm 0pt;font-family:'Times New Roman';}
DIV.MsoNormal
{font-size:12pt;margin:0cm 0cm 0pt;font-family:'Times New Roman';}
A:link
{color:blue;text-decoration:underline;}
SPAN.MsoHyperlink
{color:blue;text-decoration:underline;}
A:visited
{color:purple;text-decoration:underline;}
SPAN.MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}
SPAN.EmailStyle17
{color:windowtext;font-family:Arial;}
DIV.Section1
{page:Section1;}
</STYLE>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>I'm going to assume your table has 2 fields</FONT></SPAN><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>ignition_on which is a boolean</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>actiontime which is a timestamp</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>and your table is called ignition_log</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>I would try the following - haven't tested it so don't know if it will work</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>SELECT igoff.actiontime as ignition_off_time, MIN(igon.actiontime) as ignition_on_time, MIN(igon.actiontime) - igoff.actiontime as igdifference</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>FROM ignition_log igoff INNER JOIN ignition_log igon </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2> ON igoff.actiontime < igon.actiontime AND igoff.ignition_on = false AND igon.ignition_on = true</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=479343719-03112006><FONT face=Arial color=#0000ff size=2>GROUP BY igoff.actiontime</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR>
<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>Pedro Doria Meunier<BR><B>Sent:</B> Friday, November 03, 2006 2:31 PM<BR><B>To:</B> 'PostGIS Users Discussion'<BR><B>Subject:</B> [postgis-users] Help with query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<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… ;-)</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></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:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></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)</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.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></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?</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. ;-)</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></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!</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN lang=EN-GB style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><U><FONT face="Bauhaus 93" color=#0080c0 size=5><SPAN style="FONT-SIZE: 18pt; COLOR: #0080c0; FONT-FAMILY: 'Bauhaus 93'">Pedro Doria Meunier</SPAN></FONT></U></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></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></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ICQ - 308-182-126</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Skype: pdoriam</SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<P>
<HR SIZE=1>
<P></P><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><BR>
<P>>_______________________________________________<BR>>postgis-users mailing list<BR>>postgis-users@postgis.refractions.net<BR>>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR>
<P></FONT></P></BLOCKQUOTE></DIV></div><br clear=all><hr>Las mejores tiendas, los precios mas bajos, entregas en todo el mundo, YupiMSN Compras: <a href="http://g.msn.com/8HMAES/2749??PS=47575" target="_top">Haz clic aquí...</a> </html>