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
