Thread: pgsql age function showing 00:00:00 with year argument
Look the below 2 scenarios :-
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 ?
(b) Why group by didn't group all '00:00:00' ?
Regards,
Arup Rakshit
Arup Rakshit
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