Calculate the days to reach a certain date - PostgreSQL

Question

I need to create a query to calculate the difference in days until a date reach another date. Something like the "how many days until my birthday".

Current_date | Reach_date

2000-01-01 | 2015-01-03 -- Should Return: 2

2015-03-01 | 2021-03-05 -- Should Return: 4

The most similar built-in function I found to solve this problem, was using "age()", but it returns me "year, month and days":

select age(current_date,reach_date) from sample_table;

          age           
-------------------------
  3 years 10 mons 1 day

I also tried to use "extract()" trying to get the difference in days, but it just returns me the part of the age function of the days. At my last sample, instead of it returns me more than 1000 days, it returns me just 1.

please use 'yyyy-mm-dd' date format. also "01/01/2000 | 05/01/2001 -- Should Return: 4"? — jian, Jun 29 '22 at 15:33
I fixed the date format. Yes, it should return 4, because for this query I need to calculate something like the days until "my birthday". — devinho, Jun 29 '22 at 15:43

score 1 · Answer 1 · answered Jun 29 '22 at 16:21

SELECT
    d_date,
    '2021-01-01'::date - '2020-01-01'::date AS diff_2021_minus_2020,
    CASE WHEN (date_part('month', d_date)::integer) = 1
        AND (date_part('day', d_date)::integer) = 1 THEN
        (date_trunc('year', d_date) + interval '1 year')::date - date_trunc('year', d_date)::date
    WHEN ((d_date - (date_trunc('year', d_date))::date)) <= 182 THEN
        (d_date - (date_trunc('year', d_date))::date)
    ELSE
        365 - (d_date - (date_trunc('year', d_date))::date)
    END AS till_to_birthday
FROM (
    VALUES ('2021-12-01'::date),
        ('2021-06-01'::date),
        ('2020-01-01'::date),
        ('2021-01-01'::date),
        ('2021-09-01'::date),
        ('2021-11-01'::date),
        ('2020-06-01'::date)) s (d_date);

returns:

+------------+----------------------+------------------+
|   d_date   | diff_2021_minus_2020 | till_to_birthday |
+------------+----------------------+------------------+
| 2021-12-01 |                  366 |               31 |
| 2021-06-01 |                  366 |              151 |
| 2020-01-01 |                  366 |              366 |
| 2021-01-01 |                  366 |              365 |
| 2021-09-01 |                  366 |              122 |
| 2021-11-01 |                  366 |               61 |
| 2020-06-01 |                  366 |              152 |
+------------+----------------------+------------------+

really close to what I was needing, but you are only considering the first day of the year as birthday? I was needing to compare two variables as date. I updated the question. — devinho, Jun 29 '22 at 17:30

GoonerForLife · Accepted Answer · 2022-06-29T17:55:51.547

1

Try if this works for you. It checks where it's a leap year to calculate the difference correctly, and then uses different logic to calculate the difference between the dates depending on whether the dates are in the same year or not.

with cte as
(
  SELECT *,    
         CASE WHEN extract(year from CurrentDate)::INT % 4 = 0
                    and (extract(year from CurrentDate)::INT % 100 <> 0 
                          or extract(year from CurrentDate)::INT % 400 = 0)
                   THEN TRUE
              ELSE FALSE
         END AS isLeapYear,
         Extract(day from (Reach_date - CurrentDate)) AS diff_in_days
  FRoM test
)
SELECT CurrentDate,
       Reach_date,  
       CASE WHEN isLeapYear
                 THEN 
                      CASE WHEN diff_in_days < 366
                                THEN diff_in_days
                           ELSE  Extract(day from AGE(Reach_date, CurrentDate))
                      END
            ELSE CASE WHEN diff_in_days < 365
                                THEN diff_in_days
                           ELSE Extract(day from AGE(Reach_date, CurrentDate)) 
                      END
       END AS diff
FROM cte

Test here: SQL Fiddle

edited Jun 29 '22 at 17:55

answered Jun 29 '22 at 16:29

GoonerForLife

631
2
5

Gooner, it worked at SQL Fiddle, but if the difference is '1 year and 1 day / 367 days' my return should be 1, instead of 367. – devinho Jun 29 '22 at 16:50
Try the updated query. – GoonerForLife Jun 29 '22 at 17:01
Actually I still don't reach what I need: Taking '01/01/2000' as current_date and '01/05/2021' as reach_date my return should be 4, instead of 7309. It's like the biggest difference I can have until my birthday come is 365/366 days. If the difference is of 10 year and 4 days, this query should return just 4 days. – devinho Jun 29 '22 at 17:39
1

@Luan-torres- Give it a shot now and let me know if you see any issue. – GoonerForLife Jun 29 '22 at 18:03

score 0 · Answer 3 · answered Jun 29 '22 at 15:36

The behavior that you've got with using age() is because extract() only extract the amount of days but it won't convert months and years into days for you before extraction.

On a SQL Server you could use DATEDIFF() but in Postgre you have to compute it yourself by substracting dates, as shown in this answer.

There's also few examples with all the time units here.

Calculate the days to reach a certain date - PostgreSQL

3 Answers3