Help

Re: Filter by full datetime

Solved
Jump to Solution
1176 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Mullin
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions
Rupert_Hoffsch1
10 - Mercury
10 - Mercury

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!

See Solution in Thread

4 Replies 4
Rupert_Hoffsch1
10 - Mercury
10 - Mercury

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
6 - Interface Innovator
6 - Interface Innovator

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.

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

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.