Filter by full datetime

I have a datetime field that includes data that’s precise to the second. I’d like to filter records based on an accounting window that’s somewhat unusual (like 2pm Thursday to Thursday), rather than just Thursday to Wednesday for instance. I saw there’s an old forum question on this, but it’s about 5 years old. Is there any update on this topic?

Hi Scott,

the date field stores seconds, but there is no way to enter seconds at the moment. The calculated date fields “created time” or “modified time” store to the second. You’ll be able to find this out by using a formula field with the SECOND() formula. You’ll see that any date that you enter manually will be 0 while the automatically calculated fields have a second value.

Now to your question: The filter options in Airtable don’t include seconds at the moment and that hasn’t changed since the last post that you shared. There is a workaround however. In a similar way to SECOND(), there is MINUTE() etc. (Formula field reference).

You could make use of these formulas to be able to filter accordingly. Every record would for instance become year, month, day, hour, minute and second (this is, by the way, also the method for grouping acc. to year, month, …).

Hope that helps and let me know if you have questions re. this!

1 Like

Thanks Rupert!

I misremembered, and indeed that field is precise to the minute, not seconds. It looks like that workaround should work. I appreciate the support.

1 Like

There is no way to manually enter seconds in the date field directly. However, it is possible to enter seconds indirectly.

  • you can use scripting or the REST API to enter values that include seconds
  • you can use DATEADD() to add seconds to an existing date/time
  • you can use DATETIME_PARSE() to create a date/time with seconds
1 Like

You may even get some satisfaction from the Duration field type, which stores a length of time in seconds. You could use it to type in army time with seconds.

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.