Filtering Events by attribute with Different Time Conditions

Questions / Help
,
1

Hey y’all I’m trying to figure out how to write this more eloquently…

I need to query a table of events that are happening this month (including things that are happening from the start of today). I have 3 types of events based off of column :type and depending on what’s in that column will determing whether or not I check that starts_at or ends_at

I have 3 types of events, :event, :action, :other

How do I write this so I check the following
if e.type == event: :starts_at
if e.type == action: :ends_at
if e.type == other: :ends_at

This is what I have now for and as you can see it looks horrible and only queries the start time. I’m hoping to get it in one query, otherwise I’ll write it in two, but it’s not the most ideal obviously

def events_at_date(month, year) do
    timezone = "America/Los_Angeles"

    from(e in Event,
      where:
        fragment(
          "EXTRACT(YEAR FROM (starts_at AT TIME ZONE 'UTC' AT TIME ZONE ?)) = ?",
          ^timezone,
          ^year
        ),
      where:
        fragment(
          "EXTRACT(MONTH FROM (starts_at AT TIME ZONE 'UTC' AT TIME ZONE ?)) = ?",
          ^timezone,
          ^month
        )
    )
  end

The edge of my sql knowledge stops here. I’m super weak with times and timezones…

Any help would be appreciated

2

Still don’t like how much code there is so if anyone has any insight on how to make this better would love it, that being said, I think I’ve got it! The power of a 15m break

def events_at_date(month, year) do
    timezone = "America/Los_Angeles"

    from e in Event,
      where:
        fragment(
          """
          EXTRACT(YEAR FROM (
            CASE
              WHEN type = 'event' THEN starts_at
              ELSE ends_at
            END AT TIME ZONE 'UTC' AT TIME ZONE ?
          )) = ?
          """,
          ^timezone,
          ^year
        ),
      where:
        fragment(
          """
          EXTRACT(MONTH FROM (
            CASE
              WHEN type = 'event' THEN starts_at
              ELSE ends_at
            END AT TIME ZONE 'UTC' AT TIME ZONE ?
          )) = ?
          """,
          ^timezone,
          ^month
        )
  end
3

There are a few things here:

  • You need to switch based on type → that requires a CASE expression with sql.

  • This is ugly because many of the functions you’re using are not in the default ecto query api, so you end up with a huge fragment. Extracting those fragments to named functions/macros can help with better composability as well as making those things more readable: Ecto.Query.API — Ecto v3.11.0

  • Given you’re filtering based on a bunch of functions you cannot simply use an index here. It would be better to figure out a way to have the information you need in the db in the first place (either as a column or an index using expressions) and query based on that index – at least if this will grow to more events than just a few (hundred).

1 Like
4

The simplest way to get this running would probably be something like this:

date_trunc('month', (CASE WHEN type = 'event' THEN starts_at
     WHEN type = 'action' THEN ends_at
     WHEN type = 'other' THEN ends_at
END AT TIME ZONE 'UTC' AT TIME ZONE ?)) == ?
4 Likes
5

Wow thank you, this makes a lot of sense. I think making another column would work of just making an month-year string "01-24" and querying that instead, but not sure if that’s a good idea.

Considering it’s just filtering by date and year, is the “month_year” column a decent way to go about it?

Is there a certain way that I don’t need to use AT TIME ZONE and still be accurate?

Thanks for the help by the way I really appreciate it

6

I’d ignore the “month” part. What is a month changes by timezone. But you want a stable (indexable) column of what datetime is relevant for your filtering. Then you can do a “before x, after y” search in the index with x and y specific to the tz you’re interested in.

1 Like
7

Perfect! That’s crystal clear, and feels a lot better.