Search code examples
sqlpostgresqldatetime

count difference in seconds between values from table and some moment in time using PostgreSQL

How to find difference in seconds between a passed moment in time and values from table using PostgreSQL?
For example a a passed moment in time is 2003-05-21 and table is:

name date_of_birth
Jane 2007-12-12
Bob 2003-09-20

I tried to use this following

SELECT EXTRACT(SECONDS FROM TIMESTAMP '2013-05-21') - EXTRACT(SECONDS FROM TIMESTAMP(SELECT date_of_birth FROM TABLE));

How to find how many second has gone from date of birth to the moment for every person from table?

Solution

  • Just extract the epoch from the difference: 

    select extract(epoch from timestamp '2013-05-21' - date_of_birth) 
from the_table;

    From the manual

    epoch
    [...] for interval values, the total number of seconds in the interval