Need help with ecto query

I have a query where I need to get all the records from last one year. I have written a sql query something like this

FROM Table t
ORDER BY date;

how can I use Date_format functions using ecto.

You can use fragment/1 (see: Fragments) to use raw SQL statements in your Ecto query.

1 Like

not many examples are there. Very little documentation about this

I did something like this

where: fragment("? >= date_format(date_add(now(), interval, -1), ‘%Y-%m-01’)",

but I’m getting this

(Postgrex.Error) ERROR 42703 (undefined_column) column “interval” does not exist

Afaics, the interval needs to be an expression, like so: fragment("? >= date_format(date_add(now(), interval '-1 year'), '%Y-%m-01')",

1 Like

hi @aziz thanks for help

I’m getting this issue now
(Postgrex.Error) ERROR 42883 (undefined_function) function date_add(timestamp with time zone, interval) does not exist

Is there any alternative for the solution which I’ve given?

You can write it as:
where: >= fragment("date_format(now() - interval '1year'), '%Y-%m-01'")

or if is a date or timestamp type:
where: >= fragment("date_trunc('month', now() - interval '1year')")