Ecto with dates comparison?

Phoenix Forum Questions / Help
,
1

Hello,

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

1 Like
2

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.

3

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.

4

That other query also looks like it should work. :thinking: 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?

5

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]                                                                   
    )
6

=======================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.

7

======================= 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.

8 
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.

1 Like
9

Ah yes, I see. Thanks for the help!