Questions about the ItemsQuery date filters

I would appreciate some details on the various filters supported for the date type column.

"TODAY", "TOMORROW", "YESTERDAY", "THIS_WEEK", "ONE_WEEK_AGO", "ONE_WEEK_FROM_NOW", "THIS_MONTH", "ONE_MONTH_AGO", "ONE_MONTH_FROM_NOW", "PAST_DATETIME", "FUTURE_DATETIME", "UPCOMING", "OVERDUE", "DONE_ON_TIME", "DONE_OVERDUE", "$$blank$$"

    • What does `“$$blank$$” mean? / How is it used?
    • How is "OVERDUE", "DONE_ON_TIME", "DONE_OVERDUE" used? The date column does not seem to have this information.
    • If you use a filter like “TODAY”, how is the comparison done? Like does it find all date values within the “today range” for the user’s specific timezone or all “today” values in UTC?
    • Is there any way to filter with a specific date or date and time. Like
      query_params:{rules:[{column_id:"date1",compare_value:["2023-11-16 05:00:00"],operator:any_of}]

If a date column is configured using “set as deadline” it ties it to a status column which has an “is_done” property. My guess is that the data is not exposed in the API (yet - feature request time!), my guess is there are hidden deadline fields that are searchable.

1 Like

Thanks @codyfrisch , the additional information helps.

Anyone can answer the other questions over “$$blank$$” and how comparisons work?
I’m not sure how Monday even expects users to use any of these filters if they don’t provide any technical details on the filter’s exact data range. I greatly hope there is some way to filter by exact values instead of these arbitrary tags.

Any help on these questions?

Hi @EricM,

Apologies for the delay. I just heard back with some answers to your questions :slight_smile:

To start, are you trying to retrieve specific data? If so, let me know what you’re looking for and we can find a query that works for you!

On to your specific questions:

  1. $$blank$$ = blank values. If you want to return columns with blank values, you would use this compare value.
  2. These are only relevant if the date column is combined with status (i.e., deadline mode).
  • overdue: status is not done and today is after the specified date
  • done on time: status was marked as done before the specified date
  • done overdue: status was marked as done after the specified date
  1. Today is based on the user’s time zone defined on their profile.
  2. You can filter a specific date by entering that date as the compare value (see here). I am still waiting to confirm whether or not you can use a time as well.

We are working on documentation that outlines what each compare value does, so this should help with future questions. Please let us know if you have any other questions in the mean time.

Best,
Rachel

1 Like

Hi @rachelatmonday ,
Thank you very much for looking into this. That helps clear up a lot of details.

  • Today is based on the user’s time zone defined on their profile.

Somewhat related question, is this also true for the Hour ColumnValue? For example, based on the above provided details, I assume Early morning is also a time range in the user’s time zone? If not, can you provide the specific time ranges for the following labels:

  • "Early morning"
  • "Morning"
  • "Afternoon"
  • "Evening"
  • "Night"

I did find 4 similar labels for when filtering by “Items page by column values”:

  • MORNING (6:00-12:00)
  • AFTERNOON(12:00-16:00)
  • EVENING(16:00-20:00)
  • NIGHT(20:00-6:00)

However, as you can see ‘Early Morning’ is missing from here, so I assume the ranges are not the same.

1 Like

@EricM checking into this as well - I will update you as soon as I can!

In the mean time, I confirmed that you currently are not able to filter by an exact date and time.

2 Likes

Sorry, but is there any update on this (Hour ColumnValue filtering)?

Docs are being updated today! :slight_smile: I will share the new link/info when done.

2 Likes

Hi @EricM!

Here’s the list from the docs:

  • "Early morning" (4:00 - 7:00 AM)
  • "Morning" (7:00 AM - 12:00 PM)
  • "Afternoon" (12:00 - 8:00 PM)
  • "Evening" (8:00 - 11:00 PM)
  • "Night" (11:00 PM - 4:00 AM)

I am still trying to confirm a few things with the team about the ranges, but hopefully this is enough to clarify a few things upfront!

Best,
Rachel

1 Like