0

I have a problem with a query on PostgreSQL. I have a case for school where I need calculate the age of a person when he register for school on 1st Septembre of 2005. In a file I have their date of birth and only the YEAR of school registration.

The year registration is a int type ('2005') but I need to convert it to 01-09-2005 (DD-MM-YYYY) which is the start of the school year. How can I do this ?

  • Should I convert it necessary into a date type if I want to calculate the difference between the date of registration and the date of birth ?

  • If yes, how can I convert it in order for the date of birth to be on the date type and have a format '01-09-2005'.

I have an answer but that's not the way I should do it :

SELECT AGE('01/09/2005', auditeur.date_nais)
FROM auditeur
JOIN inscription ON auditeur.id_auditeur = inscription.id_auditeur

What I want is to replace the '01/09/2005' with annee ('2005') which is an int type.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jil0
  • 9
  • 2
  • 1
    Why not [ISO 8601 format `YYYY-MM-DD`](https://en.wikipedia.org/wiki/ISO_8601) instead of this ambiguous form? That could be read as January 9th or September 1st. – tadman Mar 30 '21 at 22:58
  • Because in France we read it differently dates are on a format DD-MM-YYYY and even in programming our teacher wants us to do it this way.. – Jil0 Apr 04 '21 at 13:57
  • Your programming teacher is being stubborn. The entire industry has standardized on ISO 8601. They should get with the program. The reason for this standardization is so we don't have to deal with weird nonsense like France having a preference for a particular date that conflicts with other countries that prefer MM-DD-YYYY for whatever reason. – tadman Apr 05 '21 at 20:49

2 Answers2

0

That's how you get the desired date from an integer:

(2005 || '-09-01')::date

The concatenation force-converts 2005 to text. Use ISO 8601 format YYYY-MM-DD. Then the cast to date does not depend on the current datestyle setting.

SELECT age((i.annee || '-09-01')::date, a.date_nais) AS age_at_registration
FROM   auditeur a
JOIN   inscription i USING (id_auditeur)
WHERE  a.id = 123; -- select person somehow

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

There is no need to generate a string to then convert to a date. You have 3 integers: annee from a table and constant values 09 for month and 01 for day. With these just use the make_date function which takes 3 integers and returns a date.

select  age( make_date (annee,09,01), auditeur.date_nais)
from auditeur 
join inscription 
  on auditeur.id_auditeur = inscription.id_auditeur
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Thank you it also worked ! I’ve tried your query as well and it worked so thanks a lot – Jil0 Apr 04 '21 at 13:59