Re: pgsql age function showing 00:00:00 with year argument

Arup Rakshit wrote:
> yelloday-staging::YELLOW=> select id, dob from users;
> id | dob
> ----+---------------------
> 13 |
> 9 | 1967-02-13 14:00:00
> 10 |
> 11 |
> 8 | 1977-06-05 14:00:00
> 15 |
> 21 |
> 14 | 2014-05-25 14:00:00
> 37 |
> 22 |
> 26 | 2014-05-06 14:00:00
> 32 |
> 35 |
> 7 |
> 6 | 2014-05-10 14:00:00
> (15 rows)
>
> yelloday-staging::YELLOW=> select date_trunc('year', age(dob)) as age, count(id) from users group by
> dob ;
> age | count
> ------------+-------
> | 10
> 00:00:00 | 1
> 47 years | 1
> 00:00:00 | 1
> 37 years | 1
> 00:00:00 | 1
> (6 rows)
>
> Question -
>
> (a) How to convert age to 1 year when age will come as 00:00:00 or calculate the age in years in with
> rounding like 0.4, 0.5, 47.3 years like that ?

You mean '0 years', right?

You could use
extract (YEAR FROM date_trunc('year', age(dob)))
to get the number of years as integer.

> (b) Why group by didn't group all '00:00:00' ?

Because you grouped by "dob", and the values are different.
You could "GROUP BY 1" to group by the same expression as the first column.

Yours,
Laurenz Albe