[postgis-users] Help with query

[Pedro Doria Meunier]

Regina,

 

You're absolutely the GREATEST! It worked like a charm!

 

Thank you VERY much!

 

All the best,

 

Pedro Doria Meunier

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

MSN - pdoriam at hotmail.com

ICQ - 308-182-126

Skype: pdoriam

 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: sexta-feira, 3 de Novembro de 2006 21:04
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Help with query

 

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/2fe9cc60/attachment.html>