sqlpostgresqldatedatetime

How to calculate the difference in seconds between a specific date and column values in PostgreSQL?

Suppose I have the following data:

name date_of_birth (DATE type)
Jane 2007-12-12
Bob 2003-09-20

The specific date is 2003-05-21, and I need to calculate the difference in seconds between this date and the date_of_birth column for every row in the 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