Filter by full datetime

[Scott_Mullin]

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?

How to filter by time not only date Dates and timezones

Hi, I do have a datetime field containing,… well, date and time. I’m building a view now for a export and I need the filter to only show me certain entries prior to a special time (not only date). It looks like the filter does only take dates and not a time - at least I couldn’t find a syntax that works. How do I filter and show only entries that happened before a certain date AND time? Thanks, Frank

[Rupert_Hoffsch1 · Accepted solution]

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!

[Scott_Mullin]

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.

[kuovonne]

Rupert_Hoffschmidt:

the date field stores seconds, but there is no way to enter seconds at the moment.

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

[ScottWorld]

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.