DATE_FORMAT in Postgresql?

Question

I have this query using MySQL I need to convert it to PostgreSQL query

SELECT
    count(*) AS aggregate
FROM
    "contracts"
WHERE
    DATE_FORMAT(created_at, '%Y-%c') = '2022-1'

I got this error:

Query 1 ERROR: ERROR:  function date_format(timestamp without time zone, unknown) does not exist
LINE 6:  DATE_FORMAT(created_at, '%Y-%c') = '2022-1'

Could it be a duplicate of https://stackoverflow.com/questions/12052705/date-format-in-postgresql ? If that's the case you'll find your answer there. — bachinblack, Jan 23 '22 at 19:40
Also, I could very well be wrong but I believe there are better ways of filtering by month in both postgresql and mysql. You could look at https://dba.stackexchange.com/questions/190774/search-for-current-month-data-in-postgresql-9-2 for inspiration — bachinblack, Jan 23 '22 at 19:47

LukStorms · Accepted Answer · 2022-01-23T19:52:02.547

1

You could use TO_CHAR to format the date or timestamp.

SELECT count(*) AS aggregate
FROM contracts
WHERE TO_CHAR(created_at, 'yyyy-mm') = '2022-01'

Or use a sargable alternative

SELECT count(*) AS aggregate
FROM contracts
WHERE created_at >= '2022-01-01' 
  AND created_at <  '2022-02-01'

edited Jan 23 '22 at 19:52

answered Jan 23 '22 at 19:39

LukStorms

1 Answers1