From: Aaron Bono <postgresql@aranya.com>
To: gdavis@refractions.net
Cc: pgsql-sql@postgresql.org
Subject: Re: formatting intervals with to_char
Date: Wed, 4 Oct 2006 23:39:40 -0500
Message-ID: <bf05e51c0610042139t54587d33q374932d62da47ce0@mail.gmail.com> (raw)
In-Reply-To: <4523F764.80705@refractions.net>
References: <4522EC42.1080500@refractions.net>
<4523F764.80705@refractions.net>
On 10/4/06, Graham Davis <gdavis@refractions.net> wrote:
>
> I haven't heard any replies from this, so in the meantime I've found a
> hacky way to get the output I desire. I'm basically calculating the
> hours on the fly and piecing together a formatted string with
> concatenations like this:
>
> SELECT
> (((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
> EXTRACT(minute from time_idle) || ':' || EXTRACT(second from
> time_idle))::interval AS myinterval
> FROM
> ( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp) AS time_idle) FROM_TABLE;
>
> If anyone knows a better/proper way to get this result, let me know.
That would be my approach though you could also replace:
EXTRACT(minute from time_idle) || ':' || EXTRACT(second from time_idle)
with:
to_char(time_idle, ':MM:SS')
If you really want to clean up your SQL you could create a function that
takes any timestamp and then make the function IMMUTABLE to tell postgresql
it doesn't need to rederive the results every time.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, gdavis@refractions.net
Subject: Re: formatting intervals with to_char
In-Reply-To: <bf05e51c0610042139t54587d33q374932d62da47ce0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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