CODEWITHSUNDEEP
sqlpostgresqldate
Arya
Arya

Reputation: 8975

Difference in years between two dates

I have a table in postgresql which has a column datatype TIMESTAMP named birthdate, is it possible to write a sql statement that would return the difference in years from the birthdate stored in the table and the current date? How would this be done? My server is Postgresql

This is what I have tried but it is giving me a syntax error at the second select

select DATEDIFF(yy, NOW(), select birthdate from match where match_id = '550856d8560a64ed180416d1556f5435f4bb054c68930040')

Upvotes: 0

Views: 1159

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

There are number of things wrong here.

select DATEDIFF
       ^^^^
       PostgreSQL doesn't have a datediff function.

regress-> \df datediff
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)

I think you want the - operator, the extract function, justify_interval and the to_char function. Also, write current_timestamp instead of now(), it's the standard spelling.

Also this:

(yy, NOW(), select birthdate ...)
            ^^^

is a syntax error because you didn't wrap the subquery in (parentheses), it should be:

(yy, NOW(), (select birthdate  ...))

but in this case no subquery is necessary because you can just flatten it into the outer query, which gives you this when the subquery is fixed but nothing else is:

select DATEDIFF(yy, NOW(), birthdate)
from match where match_id = '550856d8560a64ed180416d1556f5435f4bb054c68930040';

and for the date subtraction:

regress=> SELECT extract(year FROM justify_interval(current_timestamp - DATE '2008-02-01'));
 date_part 
-----------
         7
(1 row)

giving something like:

SELECT extract(year FROM justify_interval(current_timestamp - birthdate))
from match where match_id = '550856d8560a64ed180416d1556f5435f4bb054c68930040';

Upvotes: 6

Related Questions