Ecto with dates comparison?

This looks like it should work - how does it fail?

The behavior of >= on ~U is a standard gotcha - >= compares things in Erlang term order, which compares structs field-by-field, alphabetically. Since this is not what you likely want, the compiler emits a warning.

Hi,

Yeh so I just realised they’re seen as maps and the keys are what are being compared apparently.|

I swapped the above out with this:

 query = from s in ScheduledPush, where: s.status not in ["completed", "cancelled"] and s.utc_schedule_time > from_now(1, "microsecond") 

From the docs here

Still get no results and there are results in the DB to pull.

That other query also looks like it should work. Does anything unusual show up when you convert it to SQL with Ecto.Adapters.SQL.to_sql?

Double-check that the data that comes back when you do Repo.all(ScheduledPush) matches what you expect; maybe there’s a errant UTC conversion offsetting things?

The schema type for that field is :utc_datetime_usec.

SQL: [{"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 (NOT (s0.\"status\" IN ('completed','cancelled')) AND (s0.\"utc_schedule_time\" < $1::timestamp + interval '1 microsecond'))", [~U[2020-08-16 01:26:47.451151Z]]}

I think it’s because it’s still trying to compare with a ~U object.

If I run the plain SQL I get the correct results it seems but this returns my results in a random List format which is insanely difficult to map and work with.

i.e

# This returns the right results it seems, bu the format sucks

date = DateTime.utc_now()

    Repo |> Ecto.Adapters.SQL.query!(                                                                                                                      
     "select * from scheduled_notification_pushes where utc_schedule_time > $1;", [date]                                                                   
    ) 

=======================UPDATE =====================

This works:

query = from s in ScheduledPush, where: s.utc_schedule_time > datetime_add(^date, 1, "microsecond")

This doesn’t

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

This also doesn’t work

query = from s in ScheduledPush, where: s.utc_schedule_time > datetime_add(^date, 1, "microsecond")
 and s.status != "completed" and s.status != "cancelled"

Looks like adding in an and with more conditions change the datetime part of the query somehow.

I can’t see anything obvious still though.

======================= SOLUTION - PROBLEM SOLVED =====================

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.

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.

Ah yes, I see. Thanks for the help!