+10Hi,
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
+12As you noticed, our built-in filter options for date fields currently don’t support time of day. There are a few different workarounds, all of which involve formulas.
My recommended workaround is to create a formula that compares the date field to a constant date-time. For example:
DATETIME_FORMAT(SET_TIMEZONE({Datetime field}, 'America/Los_Angeles'), 'YYYY-MM-DD HH:mm') >= '2017-05-17 15:00'
This will show 1 for times at or after May 17, 3:00pm, 0 otherwise. Replace {Datetime field} with your field name and 'America/Los_Angeles' with your time zone. (The string comparison works because the date is formatted in a way that puts larger units to the left, smaller units to the right.)
Now you can filter on this formula result. One drawback with this approach is that, in order to change the comparison value, you have to edit this formula or create a new formula. Some alternatives that allow you to change the comparison value through a filter:
Create a formula that formats your date field as a Unix timestamp as a number: VALUE(DATETIME_FORMAT({Datetime field}, 'X')) . Now you can use a numeric filter on the formula. The drawback of this approach is that unix timestamps aren’t very readable.
Create a formula that extracts the time of day of the date field as a number. For example, to get the hour of the day from the date field: VALUE(DATETIME_FORMAT(SET_TIMEZONE({Datetime field}, 'America/Los_Angeles'), 'HH')). Now you can combine 2 filters: one for the date field, one for the time of day. You could also take a similar approach to get the minute of the day represented as a number.
+16As you noticed, our built-in filter options for date fields currently don’t support time of day. There are a few different workarounds, all of which involve formulas.
My recommended workaround is to create a formula that compares the date field to a constant date-time. For example:
DATETIME_FORMAT(SET_TIMEZONE({Datetime field}, 'America/Los_Angeles'), 'YYYY-MM-DD HH:mm') >= '2017-05-17 15:00'
This will show 1 for times at or after May 17, 3:00pm, 0 otherwise. Replace {Datetime field} with your field name and 'America/Los_Angeles' with your time zone. (The string comparison works because the date is formatted in a way that puts larger units to the left, smaller units to the right.)
Now you can filter on this formula result. One drawback with this approach is that, in order to change the comparison value, you have to edit this formula or create a new formula. Some alternatives that allow you to change the comparison value through a filter:
Create a formula that formats your date field as a Unix timestamp as a number: VALUE(DATETIME_FORMAT({Datetime field}, 'X')) . Now you can use a numeric filter on the formula. The drawback of this approach is that unix timestamps aren’t very readable.
Create a formula that extracts the time of day of the date field as a number. For example, to get the hour of the day from the date field: VALUE(DATETIME_FORMAT(SET_TIMEZONE({Datetime field}, 'America/Los_Angeles'), 'HH')). Now you can combine 2 filters: one for the date field, one for the time of day. You could also take a similar approach to get the minute of the day represented as a number.
Hi Matt,
I have a date string in Unix format that I want to convert to ‘Australia/Melbourne’. I’ve tried various DATETIME format permutations but I’m stuck.
My Unix timestamp is 1495170240 in a column called {B Due}.
What combination of formulas to use?
Thanks in advance.
+16As you noticed, our built-in filter options for date fields currently don’t support time of day. There are a few different workarounds, all of which involve formulas.
My recommended workaround is to create a formula that compares the date field to a constant date-time. For example:
DATETIME_FORMAT(SET_TIMEZONE({Datetime field}, 'America/Los_Angeles'), 'YYYY-MM-DD HH:mm') >= '2017-05-17 15:00'
This will show 1 for times at or after May 17, 3:00pm, 0 otherwise. Replace {Datetime field} with your field name and 'America/Los_Angeles' with your time zone. (The string comparison works because the date is formatted in a way that puts larger units to the left, smaller units to the right.)
Now you can filter on this formula result. One drawback with this approach is that, in order to change the comparison value, you have to edit this formula or create a new formula. Some alternatives that allow you to change the comparison value through a filter:
Create a formula that formats your date field as a Unix timestamp as a number: VALUE(DATETIME_FORMAT({Datetime field}, 'X')) . Now you can use a numeric filter on the formula. The drawback of this approach is that unix timestamps aren’t very readable.
Create a formula that extracts the time of day of the date field as a number. For example, to get the hour of the day from the date field: VALUE(DATETIME_FORMAT(SET_TIMEZONE({Datetime field}, 'America/Los_Angeles'), 'HH')). Now you can combine 2 filters: one for the date field, one for the time of day. You could also take a similar approach to get the minute of the day represented as a number.
Hi Matt, any update on when filtering by time might be a feature that is introduced? Happy to help beta test if that’s useful…
+12Having the ability to filter by time and not only date would be very helpful. It’s a little odd that this feature should be limited like it is, since presumably the dates are all stored as UNIX timestamps!
Hi,
Based on documentation formula IS_AFTER compares the only date but it works well with time also.
Example:
IS_AFTER({Modified}, ‘2021-01-07T14:05:09’)
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
