Extract seconds from interval table / Cast record to interval?

Question

Im getting interval times via:

SELECT time_col - lag(time_col) OVER (ORDER BY whatever) 
FROM myTable where conditions

This returns a table like this:

00:00:38
00:05:10
00:02:05
...

I want to have the time in seconds like this:

Here is my approach:

 SELECT EXTRACT(epoch from dt) from (SELECT time_col - lag(time_col) OVER (ORDER BY whatever) FROM myTable where conditions) as dt

dt should be the table with the difference times (intervals). However I get the following error:

Error:  Function pg_catalog.date_part(unknown, record) does not exist

So I have to cast record (the table 'dt') to interval? How do I do that? Or is this completely wrong? Sorry Im new to database queries....

When posting a follow-up question please link back to the previous question for context. (Use the "share" button to get the link). Remember, **always include your exact PostgreSQL version in every question**. (+1 this time for including the exact error and query, though) — Craig Ringer, May 22 '13 at 23:55

score 1 · Accepted Answer · answered May 22 '13 at 14:44

1

Either this

SELECT EXTRACT(epoch from dt)
from (
    SELECT time_col - lag(time_col) OVER (ORDER BY whatever) dt
    FROM myTable
    where conditions
) as dt

Or this

SELECT
    extract(epoch from time_col - lag(time_col) OVER (ORDER BY whatever))
FROM myTable
where conditions

answered May 22 '13 at 14:44

Clodoaldo Neto

So i was very close. Can you explain me the second 'dt'? We select the time and name this column dt. In addition to that the whole result table is named dt? I dont get this part – Anonymous May 22 '13 at 14:51
1

@Anonymous It is the same as `SELECT EXTRACT(epoch from dt.dt)` – Clodoaldo Neto May 22 '13 at 14:55

1 Answers1