DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=nk3QuV9T5qjfa1Uw5wkphuKYauTy8cSdMq70NVjm1ivmPC53CusS11ytjbQZxYR2W0
	aHZu39lDNTizLpGvy9gj8/BlI64luhbyvsUnN7CTBCXRWjl8XAmBQEqgw8k54pSqR4co
	GdON5VozFyJmGYqJsaJO3/PjscNmQwYNzcfTs=
Message-ID: <4B43FA01.2030406@gmail.com>
Date: Wed, 06 Jan 2010 13:48:33 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: getting extract to always return number of hours
References: <4B43E887.6040402@gmail.com>
In-Reply-To: <4B43E887.6040402@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chris wrote:
> Hi,
> 
> I'm trying to get extract() to always return the number of hours between 
> two time intervals, ala:
> 
> => create table t1(timestart timestamp, timeend timestamp);
> => insert into t1(timestart, timeend) values ('2010-01-01 00:00:00', 
> '2010-01-02 01:00:00');
> 
> => select timeend - timestart from t1;
>     ?column?
> ----------------
>  1 day 01:00:00
> (1 row)
> 
> to return 25 hours.

I ended up with

select extract('days' from x) * 24 + extract('hours' from x) from 
(select (timeend - timestart) as x from t1) as y;

mainly because t1 is rather large so I didn't want to run the end - 
start calculation multiple times.

-- 
Postgresql & php tutorials
http://www.designmagick.com/