Querying date/time on 2 columns

Questions / Help
,
1

I have a column date and column time on my PostgreSQL table. I wish to make a query, to filter rows that are not expired based on date and time. I tried this, but it does not works and returns an error Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near :

from q in Line, where: fragment("date ? + time ? > NOW()", q.date, q.time)
2

Hi @franckstifler! Welcome to ElixirForum.

Something like this should work:

from q in Line,
  where:
    fragment(
      "EXTRACT(EPOCH FROM (date + time)) > ?",
      ^(DateTime.utc_now() |> DateTime.to_unix())
    )

Let us know if you run into any more issues.

3 Likes
3

If you have the option, I’d recommend storing the values together in a single datetime column. Keeping them separate is going to make most things harder. Also, the time column by itself has no semantic meaning. It’s effectively 10AM on any day for example.

3 Likes
4

Sorry for the late reply, your answer worked. actually just doing fragment("date + time > NOW()") worked.

1 Like