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