pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting extract to always return number of hours
3+ messages / 2 participants
[nested] [flat]
* getting extract to always return number of hours
@ 2010-01-06 01:33 Chris <dmagick@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Chris @ 2010-01-06 01:33 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: getting extract to always return number of hours
@ 2010-01-06 01:50 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Chris <dmagick@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rosser Schwarz @ 2010-01-06 01:50 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: getting extract to always return number of hours
@ 2010-01-06 02:48 Chris <dmagick@gmail.com>
parent: Chris <dmagick@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Chris @ 2010-01-06 02:48 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-01-06 02:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-06 01:33 getting extract to always return number of hours Chris <dmagick@gmail.com>
2010-01-06 01:50 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2010-01-06 02:48 ` Chris <dmagick@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox