Question 89537 in PostgreSQL Question posted in
The official documentation can be found here.

How do I know the age of the employee at the time of the query in Postgres? – Postgresql

We have a birthday column in the format DD.MM.YYYY (for example 25.04.1990) of string type. How can we find out the employee’s age at the time of the query?

I do not know how to change to date type and how to find the age at the time of the request, without specifying today’s date

Tags:

2

Answers


  1. 0

    You need to convert that string to a proper date, then you can e.g. use the age() function:

    select age(to_date(birthday, 'dd.mm.yyyy'))
from the_table
    Login or Signup to reply.
  2. 0

    how to change to date type

    ALTER your "string type" column to the appropriate type date first:

    ALTER TABLE tbl ALTER COLUMN birthday TYPE date USING to_date(birthday, 'DD.MM.YYYY');

    and how to find the age at the time of the request, without specifying today’s date

    Postgres uses the system time of the underlying OS. Compute an "age" with the age() function, either providing an end-timestamp explicitly with the 2-parameter form, or defaulting to the system time implicitly with the 1-parameter form:

    SELECT age(localtimestamp, birthday) AS age_now2
     , age(birthday)                 AS age_now1
FROM   tbl;

    The function takes timestamp. But date (or timestamptz) are coerced automatically.

    Use EXTRACT() (or to_char()) to get to the age as customary integer (or text):

    SELECT date_part('year', age(birthday))::int AS age_int
     , to_char(age(birthday), 'FMYYYY')      AS age_text
FROM   tbl;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search