Home Querying all past and future round birthdays
Reply: 0

Querying all past and future round birthdays

user3885
1#
user3885 Published in June 21, 2018, 8:44 am

I got the birthdates of users in a table and want to display a list of round birthdays for the next n years (starting from an arbitrary date x) which looks like this:

 +----------------------------------------------------------------------------------------+
 | Name   | id | birthdate  | current_age | birthday   | year | month | day | age_at_date |
 +----------------------------------------------------------------------------------------+
 | User 1 | 1  | 1958-01-23 | 59          | 2013-01-23 | 2013 | 1     | 23  | 55          | 
 | User 2 | 2  | 1988-01-29 | 29          | 2013-01-29 | 2013 | 1     | 29  | 25          | 
 | User 3 | 3  | 1963-02-12 | 54          | 2013-02-12 | 2013 | 2     | 12  | 50          | 
 | User 1 | 1  | 1958-01-23 | 59          | 2018-01-23 | 2018 | 1     | 23  | 60          | 
 | User 2 | 2  | 1988-01-29 | 29          | 2018-01-29 | 2018 | 1     | 29  | 30          | 
 | User 3 | 3  | 1963-02-12 | 54          | 2018-02-12 | 2018 | 2     | 12  | 55          | 
 | User 1 | 1  | 1958-01-23 | 59          | 2023-01-23 | 2023 | 1     | 23  | 65          | 
 | User 2 | 2  | 1988-01-29 | 29          | 2023-01-29 | 2023 | 1     | 29  | 35          | 
 | User 3 | 3  | 1963-02-12 | 54          | 2023-02-12 | 2023 | 2     | 12  | 60          | 
 +----------------------------------------------------------------------------------------+

As you can see, I want to be "wrap around" and not only show the next upcoming round birthday, which is easy, but also historical and far future data.

The core idea of my current approach is the following: I generate via generate_series all dates from 1900 till 2100 and join them by matching day and month of the birthdate with the user. Based on that, I calculate the age at that date to select finally only that birthdays, which are round (divideable by 5) and yield to a nonnegative age.

WITH
  test_users(id, name, birthdate) AS (
    VALUES
      (1, 'User 1', '23-01-1958' :: DATE),
      (2, 'User 2', '29-01-1988'),
      (3, 'User 3', '12-02-1963')
  ),
  dates AS (
    SELECT
      s                     AS date,
      date_part('year', s)  AS year,
      date_part('month', s) AS month,
      date_part('day', s)   AS day
    FROM generate_series('01-01-1900' :: TIMESTAMP, '01-01-2100' :: TIMESTAMP, '1 days' :: INTERVAL) AS s
  ),
  birthday_data AS (
    SELECT
      id                                                                                AS member_id,
      test_users.birthdate                                                              AS birthdate,
      (date_part('year', age((test_users.birthdate)))) :: INT                           AS current_age,
      date :: DATE                                                                      AS birthday,
      date_part('year', date)                                                           AS year,
      date_part('month', date)                                                          AS month,
      date_part('day', date)                                                            AS day,
      ROUND(extract(EPOCH FROM (dates.date - birthdate)) / (60 * 60 * 24 * 365)) :: INT AS age_at_date
    FROM test_users, dates
    WHERE
      dates.day = date_part('day', birthdate) AND
      dates.month = date_part('month', birthdate) AND
      dates.year >= date_part('year', birthdate)
  )

SELECT
  test_users.name,
  bd.*
FROM test_users
LEFT JOIN birthday_data bd ON bd.member_id = test_users.id
WHERE
  bd.age_at_date % 5 = 0 AND
  bd.birthday BETWEEN NOW() - INTERVAL '5' YEAR AND NOW() + INTERVAL '10' YEAR
ORDER BY bd.birthday;

My current approach seems to be very inefficient and rather complicated: It takes >100ms. Does anybody have an idea for a more compact and performant query? I am using Postgresql 9.5.3. Thank you!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.345878 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO