Thread: getting extract to always return number of hours
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 couldn't see anything in the docs and can't work out a way to do it. Any suggestions? Thanks! -- Postgresql & php tutorials http://www.designmagick.com/
On Tue, Jan 5, 2010 at 6:33 PM, Chris <dmagick@gmail.com> wrote: > I'm trying to get extract() to always return the number of hours between two > time intervals, ala: SELECT extract(epoch from timeend - timestart) / 3600 from t1; rls -- :wq
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/