Filtering dates for 5, 10 minute intervals

Q&A
, , ,
1

I have a datraset taht has a timestamp column with minute by minute data.
Is there an option to filter the data for the following intervals:
1 minute, 5 minutes, 10 minutes?

Current data:
Jan 1, 2024 00:01
Jan 1, 2024 00:02
Jan 1, 2024 00:03
Jan 1, 2024 00:04
Jan 1, 2024 00:05 …

For example, if I choose the “5 minute” option, I want the data for these timestamps:
Output:
Jan 1, 2024 00:00
Jan 1, 2024 00:05
Jan 1, 2024 00:10
Jan 1, 2024 00:15
Jan 1, 2024 00:20

Can this be accomplished in Quicksight?

2

On a high level, it seems possible by creating a combination of -

  1. parameter/control/filter (say ‘DateTime Interval’) to accept the 1/5/10 minute interval in a filter
  2. pass this into a calculated field where you are using this in one of the Date functions for filtering and
  3. apply the filter to one or more visuals in your analyses.

However, this can get complicated depending on few other details.
Questions -

  1. What is the data source - Excel, RDS db or any custom source?
  2. Have you already imported the data and created a dataset in Quicksight from this data source? There can be a datatype mismatch issue during the import depending on the data source and the datatype of source/target.
  3. Are there more columns in this dataset other than the datetime column? This can impact your grouping for other columns depending on the interval filter.
    Thanks!
3

Hi @uc06

You can extract the minute portion of the date
extract( “MI”, {Updated Time})

Then you can create a calculated field say ModTest to test with the mod function
ifelse(mod(MinutePortion, 5) = 0, 1, 0)

and in the visual apply a filter equal to 1 for the ModTest field.

The 5 I have used for the mod can be your parameter value (1, 5, 10 etc). Please make sure you supply a default value for the parameter as then only it will be available for use in the calculated field definition dialogue.

4

Hi @uc06

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with closing this topic.

5

Hi @uc06,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!