Group By Jalali Date in Postgresql

All we need is an easy explanation of the problem, so here it is.

I want GROUP BY jalali’s month, all date save as gregorian format in my database, I found following sql query but group by gregorian date

SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
FROM reserverow
WHERE reserverow.used_date > '2000-09-28 01:00:00'
GROUP BY EXTRACT(MONTH FROM reserverow.used_date)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The easiest way to do this would be to use Team Appir’s jalali_utils extension for PostgreSQL. Then you could write your query like this:

SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
  FROM reserverow
 WHERE reserverow.used_date > '2000-09-28 01:00:00'
 GROUP BY JALALI_PART('month', reserverow.used_date)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply