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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
