VIEWS: Filter by time (hours) not just date (days) in a single field

[Action_for_Happ]

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

[chrisbyrne]

If you want filter by time and need it for an automation:

1 add a formula field time with formula

hour(now())

2 create a view that has date=today and time=9

3 add an automation when record enters view.

Works like a charm.

PS assumes you have a field called date in ISO date format, YMD and UTC.

[Action_for_Happ]

Thanks Chris, 

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?

Thoughts? 

Alex

[TimC]

I definitely would love to have a way to filter by days.

My use case also involves Zapier. I want to be able to ping a Slack room with an alert only if a piece of content that is scheduled to be published in less than a certain number of hours (let's say 12) has just become ready for review. 

As things stand, I can only set it for within 1 day, and that creates more alerts than is truly useful with the way we operate. Because of field creep and general data overload to keep things loading smoothly, I would rather not have to create a separate formula field to calculate this. If we have days, surely hours is possible, so it would be a valuable feature for paying customers at least! Makes me tempted to explore other options. 

[Alex_Nunn]

Think I've managed to do that already Tim. 

If I want to trigger something a set number of hours form a date time then you can use DATETIME_DIFF to create an HOURS_UNTIL field using NOW() as one of the specifiers. 

It's fine if you only need a few of them, my post above though is about views because I have many many triggers like this and having a hours_until field for every one is just not practical. 

[TimC]

Yeah, that could be useful for a lot of situations, and yet I agree we need something more formal and built-in for this to be smooth across a wider spectrum of needs. 

I'd like to have many triggers as well, for any Airtable Product folks reading here. The demand should be enough to warrant adding this feature, especially since the "days" filter is stuck on changing at midnight UTC, and even customizing that does not really feel natural in practice. If it worked as "24 hours," then it would cause fewer instances of confusion, but all the more reason to add "hours" so that they can have discrete functions and thus someone who wants 24 hours could do that. 

[Chais_Meyer]

Great! We applied this and it worked like a champ, after we put the local timezone into the formula: 

HOUR(SET_TIMEZONE(NOW(), 'America/Chicago'))

Thanks for contributing to the collective!