Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group