Need help with ecto query

Questions / Help
1

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

SELECT *
FROM Table t
WHERE
   t.date >= DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 YEAR),'%Y-%m-01')
ORDER BY date;

how can I use Date_format functions using ecto.

2

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

1 Like
3

not many examples are there. Very little documentation about this

4

I did something like this

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

but I’m getting this

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

5

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

1 Like
6

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?

7

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

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

2 Likes