How to Calculate Date Difference in Postgresql

How to calculate DATE Difference in PostgreSQL?

Your calculation is correct for DATE types, but if your values are timestamps, you should probably use EXTRACT (or DATE_PART) to be sure to get only the difference in full days;

EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference

An SQLfiddle to test with. Note the timestamp difference being 1 second less than 2 full days.

How to get difference of days/months/years (datediff) between two dates?

SELECT
  AGE('2012-03-05', '2010-04-01'),
  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,
  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,
  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;

This will give you full years, month, days ... between two dates:

          age          | years | months | days
-----------------------+-------+--------+------
 1 year 11 mons 4 days |     1 |     11 |    4

More detailed datediff information.

Calculate difference between dates - Postgres

In order to achieve the date difference in days,

SELECT DATE_PART('day', '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp);

OR

this also works pretty well

select '2015-01-12'::date - '2015-01-01'::date;

Now, you only need to use this logic and satisfy your requirement.

Postgresql: How to find difference between 2 dates and times in decimal hours?

select extract (EPOCH from (enddate + endtime - (startdate + starttime))) / (60 * 60) from a;

Error calculating the number of days between two dates in PostgreSQL

If you want to include both date boundaries, adding + 1 is actually the right thing to do. There is one day between '2019-1-1' and '2019-1-1' according to your own definition.

Since you speak of dates, not timestamps, there's a simpler way:

SELECT date '2019-12-31' - date '2019-1-1' + 1;

 ?column? 
----------
      365

Subtracting dates returns integer.

If your input is timestamp values, just cast to date to truncate the time component:

SELECT ts1::date - ts2::date + 1;

How to calculate difference between two days in hours in postgreSQL and return as days?

You can use epoch arithmetic:

select ceiling(extract(epoch from date2) - extract(epoch from date1)) / (24 * 60 * 60)
from t;

Note that this particular formulation counts anything longer than 1 day as 2 days. I think that is the intention of your question. However, if you really do have a 1 hour buffer, the logic could be tweaked to handle that.

How to calculate mean date difference with Postgres

Use the lag() window function to calculate your differences. Once you have those differences, use the avg() aggregation function.

with diffs as (
  select agent_id, quoted_at, 
         quoted_at - lag(quoted_at) over (partition by agent_id
                                              order by quoted_at) as diff_days
    from your_table
)
select agent_id, avg(diff_days) as mean
  from diffs
 where diff_days is not null;

The check for null diff_days is necessary since the diff_days for the first record for an agent is null, and you do not want that in the avg() aggregation.

---

Related Topics