Re: optimize/cleanup SQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brandon Metcalf <brandon(at)geronimoalloys(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: optimize/cleanup SQL
Date: 2009-05-29 15:24:19
Message-ID: 17998.1243610659@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Brandon Metcalf <brandon(at)geronimoalloys(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Emi Lu 2009-05-29 16:10:22 Re: How can I look at a recursive table dependency tree?
Previous Message Grzegorz Jaśkiewicz 2009-05-29 15:18:41 Re: Converting each item in array to a query result row