[postgis-users] Help with query

[Obe, Regina]

Thanks,
 
That means a lot to me coming from you.  I think I cheated though -
since I'm sure I've seen that trick before.
 
One thing I love about SQL is that you can do so much with it with such
little code.  The trick  is to 
 
state the problem in the shortest possible way you can and your
statement is the answer.
 
In this case
 
Give me all the ignition records that are off.
For each off record give me the minimum on record that is still greater
than my off.

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Friday, November 03, 2006 8:03 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Help with query


Ye gods, Regina, you're amazing. 

On 3-Nov-06, at 2:14 PM, Pedro Doria Meunier wrote:


	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.
	

	_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net
	http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061106/ad3c0ad0/attachment.html>