CODEWITHSUNDEEP
sqlpostgresqldate
scotthanford
scotthanford

Reputation: 53

Rounding dates in SQL

I'd like to figure out the age of a person based on two dates: their birthday and the date they were created in a database.

The age is being calculated in days instead of years, though. Here's my query:

SELECT date_of_birth as birthday, created_at, (created_at - date_of_birth) as Age
FROM public.users
WHERE date_of_birth IS NOT NULL

The date_of_birth field is a date w/o a timestamp, but the created_at field is a date with a timestamp (e.g. 2017-05-06 01:27:40).

And my output looks like this:

0 years 0 mons 9645 days 1 hours 27 mins 40.86485 secs

Any idea how can I round/calculate the ages by the nearest year?

Using PostgreSQL.

Upvotes: 2

Views: 1495

Answers (2)

Manngo
Manngo

Reputation: 16433

In PostgreSQL, dates are handled very differently to MSSQL & MySQL. In fact it follows the SQL standard very well, even if it’s not always intuitive.

To actually calculate the age of something, you can use age():

SELECT age(date1,date1)

Like all of PostgreSQL’s functions, there are variations of data type, and you may need to do something like this:

SELECT age(date1::date,date1::date)

or, more formally:

SELECT age(cast(date1 as date),cast(date1 as date))

The result will be an interval, which displays as a string :

SELECT age(current_date::date,'1981-01-17'::date);
-- 36 years 3 mons 22 days

If you just want the age in years, you can use extract:

SELECT extract('year' from age(current_date::date,'1981-01-17'::date));

Finally, if you want it correct to the nearest year, you can apply the old trick of adding half an interval:

extract('year' from age(current_date::date,'1981-01-17'::date)+interval '.5 year');

It’s not as simple as some of the other DBMS products, but it’s much more flexible, if you can get your head around it.

Here are some references:

Upvotes: 2

dvirovec
dvirovec

Reputation: 41

If you are using MS SQLServer than you could 

CONVERT(DATE, created_at)

and than calculate difference in months like 

DATEDIFF(month, created_at, GETDATE())/12

means you can use reminder in months to add or substract one year.

Upvotes: 2

Related Questions