Calculate the days to reach a certain date

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.

CodePudding user response：

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

CodePudding user response：

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 |
 ------------ ---------------------- ------------------

CodePudding user response：

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.