Custom Timezone Filter

[intapiuser]

I’ve seen this quite a few times so I thought I’d write a little community post about it since our native way in specifying timezones doesn’t work with filters! 

Normally, this would be the way to convert a datetime column to a specific timezone. See this doc for more details. Using no filter, this is the general syntax to make conversions:

select [datetime_column:timezone] from table

When trying to use a direct replacement filter with the native Sisense timezone change syntax, you will receive an error:

select [datetime_column:[timezone_filter]] from table

How do we get around this? 

With a custom timezone filter! 

Here’s what we need to do:

1. Create and name the filter
2. Define filter values and names
3. Set the filter to radio buttons
4. Implement the filter in the SQL
5. Use the filter in your charts!

1. CREATE AND NAME THE FILTER

This should be familiar to you if you've made a filter before! If not, check out these instructions!

2. DEFINE FILTER VALUES AND NAMES

For this filter, we'll need to manually input the timezones and its corresponding values. Here’s how you would set up the direct replacement filter putting in whichever timezone you desire:

3. SET THE FILTER TO RADIO BUTTONS

To ensure only one filter value is selected at a time, we should enable the 'radio button' setting:

4. IMPLEMENT THE FILTER IN THE SQL

You know how before we weren't able to do this with the Sisense conversion syntax? Well, to get around this, instead we would have to use the appropriate timezone conversion function along with the proper pipe notation! 

• Redshift, Snowflake, Postgresql:

select convert_timezone('[timezone_filter|default_timezone]', datetime_column) from table

• MySql

select convert_tz(datetime_column, ‘current_timezone’, '[timezone_filter|default_timezone]') from table

• BigQuery (make sure the timezone is capitalized)

#standardSQL

select datetime(datetime_column, '[timezone_filter|default_timezone]') from table

5. USE THE FILTER IN YOUR CHARTS!

BONUS:

• You can also put the filter in your chart title so it displays which timezone is selected! 

Chart Title [timezone_filter]

Chart Title [timezone_filter]

Happy Sisense-ing!