CODEWITHSUNDEEP
sqlpostgresqldate-arithmetic
Ollyj
Ollyj

Reputation: 1

Finding out the number of days between 2 timestamps in SQL

I'm struggling to work out how to work out the time difference between 2 timestamps

My goal -

A bunch of customers order on XXXX and then proceed to cancel on XXXX (different dates), I'm interested in finding out how many customers cancel within a year. So if a customer were to cancel on day 366 they wouldn't appear in my results.

I tried a few different functions, the nearest one I could find was INTERVAL

example - WHERE cancel date >= order_date (within 365 days)

I hope this makes sense and I'm not missing anything glaringly obvious!

Cheers Olly (an amateur)

Upvotes: 0

Views: 190

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You have to be very careful about what you call "a year". For example, consider the following data:

insert into some_table (id, order_date, cancel_date)
  values (1, now() - interval '30 day', now() - interval '10 day'),
         (2, now() - interval '2 year', now() - interval '18 month'),
         (3, now() - interval '3 year', now() - interval '1 year'),
         (4, date '2016-02-29',         date '2017-03-01'),
         (5, date '2016-02-29',         date '2017-02-28'),
         (6, date '2019-02-28',         date '2020-02-29'),
         (7, date '2016-02-01',         date '2017-02-01')

Now, depending on the definition of "one year" you choose you can have 2, 3, 4, 6, and perhaps other combinations of the above satisfy your selection criteria.

For example, if you choose to use interval '1 year' as shown here

select *
  from some_table t
  where t.cancel_date <= t.order_date + interval '1 year';

you'll find that the rows with id values of 1, 2, 5, and 7 are chosen.

If you replace the interval above with interval '364 day' then only the rows with id's of 1 and 2 are chosen.

Using an interval of interval '365 day' chooses rows 1, 2, and 5.

And using an interval of interval '366 day' (leap years, y'know :-) picks rows 1, 2, 4, 5, 6, and 7.

And then you can change <= to < and increase the confusion level even more!

So you have options. Choose wisely... :-)

db<>fiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The number of customers that cancel within a year would be:

select count(*)
from t
where cancel_date <= order_date + interval '365 day';

Upvotes: 1

Related Questions