Thread: Time handling in pgsql.

Time handling in pgsql.

From
"Jeff MacDonald"
Date:
Hi,

I have a table like so.

create table times (
  start datetime,
  end datetime,
  estimate fload);

I'd like to be able to do something like this

SELECT estimate::timestamp - (end-start) from times;

anyone have some suggestions ?

When i do "select end-start from times" it formats
the text in such a way that it's like this

"1 days 2:33:03:00"

is there a way taht i can cast that to say "x seconds"
or 26:33:03:00 something like that is much more plesant
to parse than the 1 days format.

Thanks.

Jeff.


Re: Time handling in pgsql.

From
Jeff Eckermann
Date:
--- Jeff MacDonald <jeff@interchange.ca> wrote:
> Hi,
>
> I have a table like so.
>
> create table times (
>   start datetime,
>   end datetime,
>   estimate fload);
>
> I'd like to be able to do something like this
>
> SELECT estimate::timestamp - (end-start) from times;

What makes you think this won't work?
If you use type "timestamp" it certainly will. "end -
start" will return an interval, which can be freely
added to or subtracted from another date/timestamp
value. Since "datetime" no longer works on current
versions, I assume that you are using an older version
of PostgreSQL, or else you are using version 7.3.x
with "datetime" mapped to "timestamp" by means of a
domain.  If the former, this may or may not work
depending on which operators are defined for
"datetime".  Best not to rely on that anyway.  If the
latter, you should be ok (based on my untested
assumption that the domain will inherit all of
"timestamp"'s operators).

>
> anyone have some suggestions ?
>
> When i do "select end-start from times" it formats
> the text in such a way that it's like this
>
> "1 days 2:33:03:00"
>
> is there a way taht i can cast that to say "x
> seconds"
> or 26:33:03:00 something like that is much more
> plesant
> to parse than the 1 days format.
>

No parsing necessary, at least for the example you gave.

__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com