Querying date/time on 2 columns

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)

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

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

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

1 Like