1

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!

Albin
  • 822
  • 1
  • 7
  • 25
  • 1
    The error message doesn't seem to match the function call actually being made in the above code. Are you sure you pasted the right SQL code? Also, what is your version of Postgres? – Tim Biegeleisen Jan 11 '22 at 07:48
  • You are right, the error message was from a BI tool. I corrected it. I'm using Postgres version 14.1 – Albin Jan 11 '22 at 07:56

1 Answers1

2

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
Anand Sowmithiran
  • 2,591
  • 2
  • 10
  • 22
  • By trying so I get a huge integer value. Can I convert this integer to a date? And how? – Albin Jan 11 '22 at 08:25
  • To get back the date, pass the numeric value to the `to_timestamp()` function like this `select to_timestamp(1638662400)::date` – Anand Sowmithiran Jan 11 '22 at 08:30
  • FYI, if you have really old dates (like 1600's for example), this method falls over with an integer error. – Cassova Aug 01 '23 at 16:04