Search code examples
sqlpostgresqlmedianpercentile-cont

Find median of dates in PostgreSQL

I was wondering if there is a way to find the 'median' date in PostgreSQL. The goal is to obtain, according to the median, the date that is in the middle of all the dates.

I tried following modified median function:

select
    percentile_cont(0.5) within group (order by date)
from cte

By trying to do so I get the following error message:

SQL Error [42883]: ERROR: function percentile_cont(numeric, timestamp without time zone) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 12

As dates are not supported, I was wondering if there is another way to calculate the median value of a date.

Thank you for any inputs!

Solution

  • You can cast the date value to an integer and then use it for getting the median value using the percentile_cont function. Like so,

    SELECT 
percentile_cont(0.5) within group (ORDER by cast(extract(epoch from dateCol1) as integer))
FROM table1

    The above gives the median date, but in numeric value, to convert it back to a date type, use the to_timestamp function like so ,

    select to_timestamp(1638662400)::date
#gives 2021-12-05