Hey all, first post in here. Has anyone else noticed that when creating a view and setting the filters (particularly on conditions relating to a date field that includes a time) there is no way to set the filter to read hours rather than days?
My use case:
- I want to set up a view to trigger an external automation (zapier) exactly 48hrs before a specific date-time
- I set the filter to DATE = number_of_days_from_now = 2
However, that doesn't work as it doesn't include time in the calculation.
e.g. DATE = 2023-09-19 16:00:00
So then I want the filter to register a new row at: 2023-09-17 16:00:00
But instead, it ignores time, adding a new row at: 2023-09-17 00:00:00 (as soon as it reaches the correct day basically) despite this being 16 hours early (but technically correct by 'days' alone).
I've tried using DATETIME_FORMAT to extract just the time - but there isn't a way to read that usefully in the view.
So I'm probably going to have to do an awkward workaround like create a field that counts the hours until the date (DATETIME_DIFF 'hours') and set the view to include when <48.
But its kinda bizarre that the filters don't have a more granular setting than days built-in to date filtering.
Have I missed something? thanks, Alex
That gets me a little bit of the way there, but actually, each row has a different time it needs to be triggered at.
Your solution is great if I want a batch of rows to appear in the view at a single time defined by me.
But what I actually need is for each row to appear in the view at different times specified by a date-time in the row itself.
To explain, I have a table which holds data on a bunch of courses that are being run in international locations - different times and timezones for each course.
I have an automation I want to trigger exactly 48 hours before the course.
Yes I can have a separate field that does a countdown of the number of hours to go until the start OR I can set a delay within the automation itself (both effective workarounds).
But I'm baffled that AT doesn't let you configure a view to filter by hours rather than days.
It's pretty odd that you can have a date-time field (e.g. START_DATE - 2023-09-29T07:00:00+00:01) and you can configure a filter to include the row in a view when START_DATE is within 2 days - but doing so ignores the time part of the field and triggers as 00:00 UTC.
I'm surely not the first person to find the TIME part of a DATETIME field relevant to operation right?