Date format with Postgres

[JRII972]

When i try use date column with postgres db, it fail on date format

(psycopg2.errors.UndefinedFunction) function date_format(date, unknown) does not exist

when i check the code, the 'date_format' function is hard coded

insights/insights/doctype/insights_query/insights_assisted_query.py

def add_format_options(result_column):
            result_column["format_options"] = {}
            result_column["type"] = get_inferred_column_type(result_column)
            for qc in query_columns:
                label_matches = qc.get("label") == result_column.get("label")
                alias_matches = qc.get("alias") == result_column.get("label")
                if not label_matches and not alias_matches:
                    continue
                result_column["label"] = qc.get("alias") or qc.get("label")
                # temporary fix until we change format_options in result columns from dict to str
                result_column["format_options"] = {"date_format": qc.get("granularity")}
                result_column["type"] = qc.get("type")
                break
            return frappe._dict(result_column)

[nextchamp-saqib]

Can you share the generated SQL for this query?

[JRII972]

Sorry i was in vacation

SELECT date_format(covid.jour, '%%Y-%%m-%%d 00:00') AS "Group by Jour",
       avg(covid.tx_prev_hosp) AS "Avg of Tx Prev Hosp"
  FROM covid AS covid
 GROUP BY date_format(covid.jour, '%%Y-%%m-%%d 00:00')
 LIMIT 500

date_format is the problem