Thread: Elapsed time between timestamp variables in Function
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
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
------------------------------------------------------------------
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
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 > >