How can do Now and Between function in postgresql using Ecto?

Questions / Help
1

Hi
I would like to some thing like this using Ecto

SELECT p.Name, pp.price, pp.original_price
FROM Product p
INNER JOIN ProductPricing pp ON pp.productId = p.productId
WHERE NOW() BETWEEN pp.startDateTimeStamp AND pp.endDateTimeStamp

But I don’t know how to use Now and Between function in Ecto
How can I do this using Ecto?

2

AFAIK, there is no x BETWEEN y AND z in Ecto, but we could do where: y <= x and x <= z
However, with NOW() it might be a bit trickier. On one hand we could use DateTime.now_to_utc() and the resulting expression would be like

where: y <= ^DateTime.utc_now() and ^DateTime.utc_now() <= z

On the other hand, because in Postgres NOW() returns time with timezone, you gotta be careful that it all works as expected.

Or to just mimic given SQL we could use fragment as

where: fragment("NOW() BETWEEN ? AND ?", y, z)
2 Likes