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?
Solved
Filter by full datetime
+10Best answer by Rupert_Hoffsch1
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!
+21Hi 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!
+10Thanks 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.
+29Hi 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!
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
+35You 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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.