Postgres And Birthday Dates
Tags: postgres
Published: 2021-10-09 13:35:15

---
title: Postgres And Birthday Dates
published: true
description: How to process users dates of birthday
tags: postgres,postgresql
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qv1yc812dsoyl7j3dpxe.png
---

Processing user`s dates of birthday is a often task, but sometimes newbies has difficult with it. So, I decide to write a few snippets.

For ex. we have a table

```
CREATE TABLE users (
   id serial primary key,
   name varchar(100) not null,
   birthday date
);
```

### 1. Find users who have birthdays at this month

```
SELECT * FROM users WHERE date_part('month', birthday) = date_part('month', CURRENT_DATE)
```

### 2. Find users who have birthdays today

```
SELECT * FROM users 
WHERE date_part('day', birthday) = date_part('day', CURRENT_DATE) 
AND date_part('month', birthday) = date_part('month', CURRENT_DATE)
```
#### 2.1 Find users who have birthdays tomorrow

```
SELECT * FROM users 
WHERE 
   date_part('day', birthday) = date_part('day', CURRENT_DATE + INTERVAL '1day') 
    AND 
   date_part('month', birthday) = date_part('month', CURRENT_DATE + INTERVAL '1day')
```

### 3. Find users who have birthdays during some period, for. ex. in recent month


```
 WITH lastDayOfMonth as (
SELECT (date_trunc('month', CURRENT_DATE) + interval '1 month' - interval '1 day')::date as ldm
)
SELECT *
FROM users, lastDayOfMonth
WHERE (
   date_part('month', birthday) = date_part('month', CURRENT_DATE) AND
   date_part('day', birthday) BETWEEN date_part('day', CURRENT_DATE) AND date_part('day', ldm)
) 
OR
(
   date_part('month', birthday) = date_part('month', CURRENT_DATE + interval '1 month')
   AND date_part('day', birthday) BETWEEN 1 AND date_part('day', CURRENT_DATE)
);
```

But this method will be works only for month interval.
If we need birthdays in recent 10 days, or 45 days... for more flexible calculation will better to use a days number of year. But we need take care about valid handling between years - for ex. 20 days since 25 december - is an interval 25.12.2021 - 14.01.2022

```
WITH lastDayOfYear AS (
    SELECT (date_trunc('year', CURRENT_DATE) + interval '1 year' - interval '1 day')::date AS ldy
)
SELECT *
FROM users, lastDayOfYear
WHERE 
(
-- if year is same 
   date_part('year', CURRENT_DATE) = date_part('year', CURRENT_DATE + interval '20days')
   AND date_part('doy', birthday)
       BETWEEN date_part('doy', CURRENT_DATE) AND date_part('doy', CURRENT_DATE + interval '20days')
)
OR (
   date_part('year', CURRENT_DATE) < date_part('year', CURRENT_DATE + interval '20days')
   AND (
     date_part('doy', birthday) BETWEEN date_part('doy', CURRENT_DATE) AND date_part('doy', ldy) 
     OR date_part('doy', birthday) < (20 - date_part('day', age(ldy, CURRENT_DATE)))
   )
);
```


### 4. Find users with age in some interval, for example, users with age between 30 and 40 years old.

It is easy to solve on backend side, when you can prepare sql

```php
<?php
$from = date('Y-m-d', strtotime('-30years'));
$to =  date('Y-m-d', strtotime('-40years'));
$sql = "SELECT * FROM users WHERE birthday BETWEEN '$from' AND '$to'";
```

But sometimes direct query may be more useful - you can do it with postgres function `age` that calculates an interval between dates

```SQL
SELECT * FROM users WHERE age(birthday) BETWEEN interval '30 years' AND interval '40 years';
```

You can play with these queries in [Db-Fiddle](https://www.db-fiddle.com/f/6YNye44Kc9b73uNAsgfkbu/6)