Thread: CAST INTERVAL to INT??

Hi Guys

I am trying to cast a INTERVAL to an INT (or BIGINT) without much success.
The query I would like to do is (partially) the following:

UPDATE acct_table SET AcctSessionTime = (now()::timestamp with time zone -
AcctStartTime::timestamp with time zone -
'%{Acct-Delay-Time:-0}'::interval);

Obviously the %{variables}% are replaced with real values.
This code if for the FreeRadius Postgres DB backend. You can see it in
current CVS at:
http://www.freeradius.org/cgi-bin/cvsweb.cgi/radiusd/raddb/postgresql.conf

This query does exactly what I want, but it requires that the underlying
AcctSessionTime field be of type INTERVAL. This unfortunately breaks
backwards compatibility with alot of billing systems which use the same
code for MySQL/Postgres/Oracle etc...

What I need to do is the above calculation (and several others like it) but
store the resulting number of seconds in a BIGINT field. How is this
possible???

TIA
--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc

Peter Nixon <listuser@peternixon.net> writes:
> I am trying to cast a INTERVAL to an INT (or BIGINT) without much success.

"extract(epoch from <interval>)" will produce the interval's value in
seconds ... as a float8, but you can cast that to bigint ...

            regards, tom lane

Tom Lane wrote:

> Peter Nixon <listuser@peternixon.net> writes:
>> I am trying to cast a INTERVAL to an INT (or BIGINT) without much
>> success.
>
> "extract(epoch from <interval>)" will produce the interval's value in
> seconds ... as a float8, but you can cast that to bigint ...

Thanks

The following seems to do what I need.

AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone -
AcctStartTime::timestamp with time zone -
'%{Acct-Delay-Time:-0}'::interval)))::BIGINT

If not, I am sure some users will start screaming shortly :-)

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc