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

From: Arup Rakshit <aruprakshit(at)rocketmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: pgsql age function showing 00:00:00 with year argument
Date: 2014-06-23 07:29:49
Message-ID: 1403508589.43836.YahooMailNeo@web193902.mail.sg3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oliver 2014-06-23 08:31:06 Re: Postgresql not getting assigned memory
Previous Message Jürgen Fuchsberger 2014-06-23 07:12:33 Taking rsynced base-backup without wal-archiving enabled