Thread: Elapsed time between timestamp variables in Function

Elapsed time between timestamp variables in Function

From
"Nico Callewaert"
Date:
Hi !
 
I saw previous postings about elapsed time between 2 timestamps, using SELECT EXTRACT...
I have similar question, but it's not in a select statement, but between 2 variables in a function.
 
To keep it simple, I have 2 variables, let's say A and B, both TimeStamp.  Now I would like to know the absolute value of elapsed seconds between the 2 timestamps.  Has to be absolute value, because can be positive or negative, depends if A > B or A < B.
I tried with age(A, B), but that gives me something like 00:00:01, not really numeric value for number of seconds.
 
Many thanks in advance !
Nico

Re: Elapsed time between timestamp variables in Function

From
Raymond O'Donnell
Date:
On 04/02/2009 21:59, Nico Callewaert wrote:

> To keep it simple, I have 2 variables, let's say A and B, both
> TimeStamp.  Now I would like to know the absolute value of elapsed
> seconds between the 2 timestamps.  Has to be absolute value, because
> can be positive or negative, depends if A > B or A < B. I tried with
> age(A, B), but that gives me something like 00:00:01, not really
> numeric value for number of seconds.

I had to do something similar recently (interval to minutes), and rolled
my own:

  create or replace function
  interval_to_minutes(interval)
  returns integer
  as
  $$
    select
      cast(
        (
          extract(hour from $1) * 60
          + extract(minute from $1)
        ) as integer
      );
  $$
  language sql stable;

Then you can do:

  select abs(interval_to_minutes(A - B));

HTH,

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: Elapsed time between timestamp variables in Function

From
Osvaldo Kussama
Date:
2009/2/4 Nico Callewaert <callewaert.nico@telenet.be>:
> Hi !
>
> I saw previous postings about elapsed time between 2 timestamps, using
> SELECT EXTRACT...
> I have similar question, but it's not in a select statement, but between 2
> variables in a function.
>
> To keep it simple, I have 2 variables, let's say A and B, both TimeStamp.
> Now I would like to know the absolute value of elapsed seconds between the 2
> timestamps.  Has to be absolute value, because can be positive or negative,
> depends if A > B or A < B.
> I tried with age(A, B), but that gives me something like 00:00:01, not
> really numeric value for number of seconds.
>
> Many thanks in advance !
> Nico


 EXTRACT(EPOCH FROM age(A,B)) ?

Osvaldo

Re: Elapsed time between timestamp variables in Function

From
"Nico Callewaert"
Date:
Thanks a lot to everybody for the help !


----- Original Message -----
From: "Osvaldo Kussama" <osvaldo.kussama@gmail.com>
To: "Nico Callewaert" <callewaert.nico@telenet.be>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, February 05, 2009 2:59 AM
Subject: Re: [GENERAL] Elapsed time between timestamp variables in Function


> 2009/2/4 Nico Callewaert <callewaert.nico@telenet.be>:
>> Hi !
>>
>> I saw previous postings about elapsed time between 2 timestamps, using
>> SELECT EXTRACT...
>> I have similar question, but it's not in a select statement, but between
>> 2
>> variables in a function.
>>
>> To keep it simple, I have 2 variables, let's say A and B, both TimeStamp.
>> Now I would like to know the absolute value of elapsed seconds between
>> the 2
>> timestamps.  Has to be absolute value, because can be positive or
>> negative,
>> depends if A > B or A < B.
>> I tried with age(A, B), but that gives me something like 00:00:01, not
>> really numeric value for number of seconds.
>>
>> Many thanks in advance !
>> Nico
>
>
> EXTRACT(EPOCH FROM age(A,B)) ?
>
> Osvaldo
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>