optimize/cleanup SQL
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.
SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM clockin))/3600 AS NUMERIC),2) AS hours
FROM timeclock;
The clockin and clockout columns are of type timestamp.
Is there a faster way to achieve this? If not faster, a cleaner way
that executes as fast?
--
Brandon
On Fri, 29 May 2009 08:13:32 -0500 (CDT)
Brandon Metcalf <brandon@geronimoalloys.com> wrote:
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM clockin))/3600 AS NUMERIC),2) AS
hours FROM timeclock;
satisfying?
template1=# select extract(days from ('2009-01-01'::timestamp -
'2008-01-01'::timestamp))+5::int;
?column?
----------
371
(1 row)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Brandon Metcalf <brandon@geronimoalloys.com> writes:
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.
SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM clockin))/3600 AS NUMERIC),2) AS hours
FROM timeclock;
The clockin and clockout columns are of type timestamp.
timestamp, or timestamptz? extract(epoch from timestamptz) is really
quite a cheap operation; there's no need to worry about performance
in that case. If what's bothering you is ugliness, encapsulate this
as an inlineable SQL function.
regards, tom lane