Skip to main content

Questions about the ItemsQuery date filters

  • November 6, 2023
  • 9 replies
  • 657 views
E
  • EricM
  • Participating Frequently

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

monday API docs

"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}]

9 replies

A
  • anon29275264
  • Participating Frequently
  • November 6, 2023

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.

    E
    • EricM
    • Author
    • Participating Frequently
    • November 7, 2023

    Thanks @anon29275264 , 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.

    E
    • EricM
    • Author
    • Participating Frequently
    • November 8, 2023

    Any help on these questions?

    rachelatmonday
    Forum|alt.badge.img
    • rachelatmonday
    • monday.com Team Member
    • November 15, 2023

    Hi @EricM,

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

    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

      E
      • EricM
      • Author
      • Participating Frequently
      • November 16, 2023

      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.

        rachelatmonday
        Forum|alt.badge.img
        • rachelatmonday
        • monday.com Team Member
        • November 16, 2023

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

          E
          • EricM
          • Author
          • Participating Frequently
          • December 1, 2023

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

          rachelatmonday
          Forum|alt.badge.img
          • rachelatmonday
          • monday.com Team Member
          • December 1, 2023

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

            rachelatmonday
            Forum|alt.badge.img
            • rachelatmonday
            • monday.com Team Member
            • December 1, 2023

            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

              Terms & ConditionsCookie settingsAccessibility statement