pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference between two times as a numeric value in a stored procedure.
4+ messages / 3 participants
[nested] [flat]
* Difference between two times as a numeric value in a stored procedure.
@ 2004-06-04 08:11 Stijn Vanroye <s.vanroye@Farcourier.com>
2004-06-04 14:11 ` Re: Difference between two times as a numeric value in a stored procedure. Tom Lane <tgl@sss.pgh.pa.us>
2004-06-04 15:23 ` Re: Difference between two times as a numeric value in a stored procedure. Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 4+ messages in thread
From: Stijn Vanroye @ 2004-06-04 08:11 UTC (permalink / raw)
To: pgsql-sql
Hello List,
I am writing two stored procedure which alternatively returns the dayhours and nighthours of two times. (nighthours are considered between 00:00 and 06:00).
As an example here is the getdayhours function:
------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.getdayhours(time, time)
RETURNS interval AS
'DECLARE
begintime ALIAS FOR $1;
endtime ALIAS FOR $2;
begindate timestamp;
enddate timestamp;
tmpresult interval;
BEGIN
IF endtime = time \'00:00\' THEN
enddate := (current_date+1)+endtime;
ELSE
enddate := current_date+endtime;
END IF;
IF begintime < time \'06:00\' THEN
begindate := current_date + time \'06:00\';
ELSE
begindate := current_date+begintime;
END IF;
tmpresult := enddate-begindate;
IF tmpresult<\'00:00\' THEN
return \'00:00\';
ELSE
return tmpresult;
END IF;
END;'
LANGUAGE 'plpgsql' VOLATILE;
------------------------------------------------------------------------------------------------
The working of the functions is not the problem, but the return type is. I can't seem to find a way to substract two time values (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. For example, in stead of 4:30 i would like 4.5 as a result. I have searched the documentation but could not find any way to substract time/timestamp values and get a numeric/float as a result. When I try to CAST the interval to a numeric or float value I get an error (cannot cast time without tz to ...). Same goes for trying to cast the beginvalues and then substract them. Does anyone have any idea how I can solve/circumvent this problem? Is there a function I can use?
I don't know if it helps but I'm going to use the functions like this:
SELECT workhour_id, employee_id, task_id, whdate, begintime, endtime, getdayhours(begintime,endtime), getnighthours(begintime,endtime) FROM workhour
Thanks in advance.
Stijn Vanroye
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Difference between two times as a numeric value in a stored procedure.
2004-06-04 08:11 Difference between two times as a numeric value in a stored procedure. Stijn Vanroye <s.vanroye@Farcourier.com>
@ 2004-06-04 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-06-04 14:11 UTC (permalink / raw)
To: Stijn Vanroye <s.vanroye@Farcourier.com>; +Cc: pgsql-sql
"Stijn Vanroye" <s.vanroye@Farcourier.com> writes:
> I can't seem to find a way to substract two time values (or
> timestamp values) and get a numeric/float value. I always get the
> INTERVAL datatype.
extract(epoch from interval) may help.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Difference between two times as a numeric value in a stored procedure.
2004-06-04 08:11 Difference between two times as a numeric value in a stored procedure. Stijn Vanroye <s.vanroye@Farcourier.com>
@ 2004-06-04 15:23 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-06-04 15:23 UTC (permalink / raw)
To: Stijn Vanroye <s.vanroye@Farcourier.com>; +Cc: pgsql-sql
On Fri, Jun 04, 2004 at 10:11:57 +0200,
Stijn Vanroye <s.vanroye@Farcourier.com> wrote:
> The working of the functions is not the problem, but the return type is. I can't seem to find a way to substract two time values (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. For example, in stead of 4:30 i would like 4.5 as a result. I have searched the documentation but could not find any way to substract time/timestamp values and get a numeric/float as a result. When I try to CAST the interval to a numeric or float value I get an error (cannot cast time without tz to ...). Same goes for trying to cast the beginvalues and then substract them. Does anyone have any idea how I can solve/circumvent this problem? Is there a function I can use?
You can use EXTRACT to extract the EPOCH from the interval which will
be the number of seconds in the interval. You can then use arithmetic
(/60.) on that number to get fractional hours.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Difference between two times as a numeric value in a stored procedure. [Scanned]
@ 2004-06-04 14:44 Stijn Vanroye <s.vanroye@Farcourier.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stijn Vanroye @ 2004-06-04 14:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> "Stijn Vanroye" <s.vanroye@Farcourier.com> writes:
> > I can't seem to find a way to substract two time values (or
> > timestamp values) and get a numeric/float value. I always get the
> > INTERVAL datatype.
>
> extract(epoch from interval) may help.
>
> regards, tom lane
Indeed. I think I should kick myself for missing that, I have read that section af the manual four times or so, and missed it over and over again :)
Thanks for the help.
Regards,
Stijn Vanroye.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-06-04 15:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-04 08:11 Difference between two times as a numeric value in a stored procedure. Stijn Vanroye <s.vanroye@Farcourier.com>
2004-06-04 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-04 15:23 ` Bruno Wolff III <bruno@wolff.to>
2004-06-04 14:44 Re: Difference between two times as a numeric value in a stored procedure. [Scanned] Stijn Vanroye <s.vanroye@Farcourier.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