# Ecto with dates comparison?

**URL:** <https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762>\
**Category:** Questions\
**Tags:** ecto, phoenix, datetime\
**Created:** [August 15, 2020, 8:47pm UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762 "2020-08-15T20:47:09Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![9Dave9](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/9dave9/32/18748_2.png) [@9Dave9](https://forum.elixirforum.com/u/9Dave9)\
**Post date:** [August 15, 2020, 8:47pm UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/1 "2020-08-15T20:47:09Z")

</div>

Hello,

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

```elixir
  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…

```elixir
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`

---

<div class="post-metadata">

**Author:** ![al2o3cr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/al2o3cr/32/3457_2.png) [@al2o3cr](https://forum.elixirforum.com/u/al2o3cr)\
**Post date:** [August 16, 2020, 12:59am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/2 "2020-08-16T00:59:35Z")

</div>

> [@9Dave9](#):
>
> ```elixir
> query = from s in ScheduledPush,                                                                                                                   
> where: s.status not in ["completed", "cancelled"] and s.utc_schedule_time >= ^date                                                               
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![9Dave9](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/9dave9/32/18748_2.png) [@9Dave9](https://forum.elixirforum.com/u/9Dave9)\
**Post date:** [August 16, 2020, 1:05am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/3 "2020-08-16T01:05:34Z")

</div>

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:

```elixir
 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](https://hexdocs.pm/ecto/Ecto.Query.API.html#from_now/2)

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

---

<div class="post-metadata">

**Author:** ![al2o3cr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/al2o3cr/32/3457_2.png) [@al2o3cr](https://forum.elixirforum.com/u/al2o3cr)\
**Post date:** [August 16, 2020, 1:18am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/4 "2020-08-16T01:18:04Z")

</div>

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`](https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#to_sql/3)?

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?

---

<div class="post-metadata">

**Author:** ![9Dave9](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/9dave9/32/18748_2.png) [@9Dave9](https://forum.elixirforum.com/u/9Dave9)\
**Post date:** [August 16, 2020, 1:31am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/5 "2020-08-16T01:31:22Z")

</div>

> [@al2o3cr](#):
>
> [`Ecto.Adapters.SQL.to_sql`](https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#to_sql/3)

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

```elixir
# 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]                                                                   
    ) 

```

---

<div class="post-metadata">

**Author:** ![9Dave9](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/9dave9/32/18748_2.png) [@9Dave9](https://forum.elixirforum.com/u/9Dave9)\
**Post date:** [August 16, 2020, 1:52am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/6 "2020-08-16T01:52:06Z")

</div>

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

This works:

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

```

This doesn’t

```elixir
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

```elixir
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.

---

<div class="post-metadata">

**Author:** ![9Dave9](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/9dave9/32/18748_2.png) [@9Dave9](https://forum.elixirforum.com/u/9Dave9)\
**Post date:** [August 16, 2020, 2:07am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/7 "2020-08-16T02:07:08Z")

</div>

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

The original query I thought was giving me problems was:

```elixir
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:

```elixir
[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.

---

<div class="post-metadata">

**Author:** ![al2o3cr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/al2o3cr/32/3457_2.png) [@al2o3cr](https://forum.elixirforum.com/u/al2o3cr)\
**Post date:** [August 16, 2020, 2:53am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/8 "2020-08-16T02:53:21Z")

</div>

```elixir
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`.

---

<div class="post-metadata">

**Author:** ![9Dave9](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/9dave9/32/18748_2.png) [@9Dave9](https://forum.elixirforum.com/u/9Dave9)\
**Post date:** [August 16, 2020, 11:12am UTC](https://forum.elixirforum.com/t/ecto-with-dates-comparison/33762/9 "2020-08-16T11:12:02Z")

</div>

Ah yes, I see. Thanks for the help!