Problem converting interval to seconds

[Liviu BURCUSEL]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Jim Seymour]

Liviu BURCUSEL <li***@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/s...-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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html