Thread: Problem converting interval to seconds

Hello !

It is late night and I cannot think right anymore. Please help me to
convert a interval like '2 days 00:22:10.2905' in seconds.

Thank you very much in advance,
Liviu

Liviu BURCUSEL <liviu@voxline.ro> wrote:
>
> Hello !
>
> It is late night and I cannot think right anymore. Please help me to
> convert a interval like '2 days 00:22:10.2905' in seconds.

http://www.postgresql.org/docs/7.4/static/functions-datetime.html is
your friend:

epoch

    For date and timestamp values, the number of seconds since
    1970-01-01 00:00:00-00 (can be negative); for interval values,
    the total number of seconds in the interval

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
Result: 982384720

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800

And sure enough:

select extract(epoch from interval '2 days 00:22:10.2905');
  date_part
-------------
 174130.2905
(1 row)

Verifying...

$ bc -l
(2*24*60*60)+(22*60)+10+0.2905
174130.2905

If you want just the seconds:

select floor(extract(epoch from interval '2 days 00:22:10.5905'));
 floor
--------
 174130
(1 row)

    I used a slightly higher decimal part to illustrate the
    difference between "::int" and "floor()," since both would
    produce the same output for decimal parts < 0.5.

If you want just the seconds, but rounded, rather than truncated:

select extract(epoch from interval '2 days 00:22:10.5905')::int;
 date_part
-----------
    174131
(1 row)

HTH,
Jim