Ecto: How to use PG generate_series with fragment()?

Phoenix Forum Questions / Help
,
1

Hi there,

I Need help: I have a query that contains (Postgres):

cross_join: dates in fragment("generate_series('2019-06-01', '2019-06-07', '1 day'::interval)")

if I now replace this with:

startdate = "2019-06-01";enddate = "2019-06-07"
cross_join: dates in fragment("generate_series(?, ?, '1 day'::interval)", ^startdate, ^enddate)

(note the missing ') Postgrex says: Postgrex expected %DateTime{}, got “2019-06-01”
but if I supply DateTime I don’t get results (I guess postgresql can’t handle it.

And if I use:

cross_join: dates in fragment("generate_series('?', '?', '1 day'::interval)", ^startdate, ^enddate)

(again with ') I get: (invalid_datetime_format) invalid input syntax for type timestamp with time zone: “$2”

What’s the correct way to do this?

Unfortunately I’m unsure how I can create a temporary collection that I can use in “from” to provide a standalone example.
Is there any way to do something like “from i in [1,2,3], cross_join…” ?

2 Likes
2

think you are dealing with “type” issues… eg you need to specify type or “cast”…

in a hurry here… byt try (with various inputs heh)

cross_join: dates in fragment("generate_series(?, ?, '1 day'::interval)", Ecto.Date.cast!(^startdate), Ecto.Date.cast!(^enddate))

or

cross_join: dates in fragment("generate_series((?)::date, (?)::date, '1 day'::interval)", ^startdate, ^enddate)

or use the to_date() in postgres…

3

Thanks!
I figured out this works:

{:ok, startdate} = Date.from_iso8601("2019-06-01")
{:ok, enddate} = Date.from_iso8601("2019-06-08")
...
cross_join: dates in fragment("generate_series((?)::date, (?)::date, '1 day'::interval)", ^startdate, ^enddate),
4 Likes