How to filter by time not only date


#1

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


#2

As 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.


#3

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.