[postgis-users] Help with query

[Obe, Regina]

I'm going to assume your table has 2 fields
ignition_on which is a boolean
actiontime which is a timestamp
 
and your table is called ignition_log
 
I would try the following - haven't tested it so don't know if it will
work
 
SELECT igoff.actiontime as ignition_off_time, MIN(igon.actiontime) as
ignition_on_time,  MIN(igon.actiontime) - igoff.actiontime as
igdifference
FROM ignition_log igoff INNER JOIN ignition_log igon 
        ON igoff.actiontime < igon.actiontime AND igoff.ignition_on =
false AND igon.ignition_on = true
GROUP BY igoff.actiontime

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Pedro Doria Meunier
Sent: Friday, November 03, 2006 2:31 PM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] Help with query



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... ;-)

 

I have the following setup in a table:

 

The first record which is to be found (ok easy enough :D) with a
timestamp meets a certain condition (ignition off)

The following record is for the event of ignition on again with a
timestamp.

 

So the question here is: how can I compute the time difference between
these two records in a single query?

Better yet: finding all the records that meet the first condition
(ignition off) and the immediately following records as to compute the
time difference. ;-)

 

Thanks in advance!

 

Pedro Doria Meunier

(351) 91 302 49 72 - (351) 96 247 99 12

MSN - pdoriam at hotmail.com

ICQ - 308-182-126

Skype: pdoriam

 




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061103/d79903bc/attachment.html>