How to compare DateTime objects in Ecto query where clause?

Question

I have a query, where I'd like to grab all records still in the future.

  def get_scheduled_notification_pushes_from_date(date\\DateTime.utc_now()) do                                                                         
    query = from s in ScheduledPush,                                                                                                                   
      where: s.status not in ["completed", "cancelled"] and s.utc_schedule_time >= ^date                                                               
                                                                                                                                                       
    Repo.all(query)                                                                                                                                    
  end

I thought that this would possibly get converted or cast for postgres - but it must be doing something weird.

I also tried comparing ~U[] objects.. but that's a bad idea...

iex(37)> ~U[2020-08-15 20:39:00.000000Z] >= ~U[2020-08-15 20:36:10.367654Z]

warning: invalid comparison with struct literal ~U"2020-08-15 20:39:00.000000Z". Comparison operators (>, <, >=, <=) perform structural and not semantic comparison. Comparing with a struct literal is unlikely to give a meaningful result. Modules typically define a compare/2 function that can be used for semantic comparison

false

How do I look for records based on a value that is ahead of DateTime.utc_now() with Ecto?

The type for utc_schedule_time is utc_datetime_usec

user1230795 user1230795 · Accepted Answer · 2020-08-16T02:13:10

==== SOLUTION - PROBLEM SOLVED - any ideas why ? ======

The original query I thought was giving me problems was:

query = from s in ScheduledPush,                                                                                                                       
      where: s.utc_schedule_time > ^date and s.status not in ["completed", "cancelled"]

Not realizing that Ecto would cast the date types appropriately to play nicely with postgres/SQL I went the terminal and did the ~U[some date] < ~U[some date] - silly and I got the gotcha notification to remind me.

I thought this is what was probably happening in the query and I need to cast those values manually.

Turns out if you have null values in a column and you try and string match - some how this does something weird where it doesn’t evaluate the record maybe? I don’t know… all I know is that the moment I do try and string match on a field which was null to begin with this row is no longer considered even if all the conditions pass.

I migrated all the values to “pending” to check this… and

Voila:

[debug] QUERY OK source="scheduled_notification_pushes" db=1.1ms queue=0.8ms idle=9384.5ms
SELECT s0."id", s0."body", s0."channel_id", s0."timezone", s0."title", s0."user_ids", s0."utc_schedule_time", s0."local_schedule_time", s0."utc_send_time", s0."status", s0."confirmed_send_count", s0."inserted_at", s0."updated_at" FROM "scheduled_notification_pushes" AS s0 WHERE ((s0."utc_schedule_time" > $1) AND NOT (s0."status" IN ('completed','cancelled'))) [~U[2020-08-16 02:01:56Z]]

["2020-08-19 16:05:00Z pending", "2020-08-19 17:05:00Z pending",
 "2020-08-19 21:05:00Z pending", "2020-08-19 15:05:00Z pending",
 "2020-08-16 02:09:00Z pending", "2020-08-16 04:33:00Z pending",
 "2020-08-16 05:41:00Z pending", "2020-08-19 01:41:00Z pending",
 "2020-08-19 05:41:00Z pending", "2020-08-18 23:41:00Z pending",
 "2020-08-19 00:41:00Z pending"]

My final though was maybe this might be a validate_required field in the schema and hence the result from the query being removed - nope, it’s not a require field either.

UPDATE(answered in elixir forum):

"WHERE (NOT (s0.status IN ('completed','cancelled'))" ... The problem is the NULL - NULL IN ('completed', 'canceled') doesn’t evaluate to false it evaluates to NULL, and NOT(NULL) is still NULL.

How to compare DateTime objects in Ecto query where clause?

1 Answers