<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1543" name=GENERATOR>
<STYLE>@font-face {
font-family: Bauhaus 93;
}
@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; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=PT vLink=purple link=blue>
<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 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>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… ;-)<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; COLOR: #0080c0; FONT-FAMILY: 'Bauhaus 93'">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></BODY></HTML>
<HTML><BODY><P><hr size=1></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></BODY></HTML>