Time Zone Conversions

When using the Sisense for Cloud Data Teams built-in daterange filter, there are two time zones considered: the databases's time zone and the site display time zone. The database time zone is usually in UTC as that is the default timezone for databases. The site display time zone is configured per Sisense site upon first connecting, and can be changed through the Preferences Page.

The date range filter converts the inputted date range from the site's display timezone to UTC (the database timezone) prior to comparing it against the timestamp column.

To ensure consistency in the selected range and the data being displayed, the timezone in the SELECT clause can be converted to the timezone of the site. For a site in the PST timezone, the timestamp in the select clause would need to be converted to PST:

SELECT
 
 [created_at:pst:date]
   
 , count(*)

 [created_at=daterange]

Skipping Time Zones

If the date has been converted to a timezone already, or if the data is stored as a date without timestamp precision, the automatic conversion can be skipped by specifying no time zone:

[created_at=daterange_no_tz]

For a site with a display time zone of PST, if "[created_at=daterange]" is used and 1 day is selected in the daterange filter, the results also include the following day:

If we run this chart on February 6th, at a time when it is still the 5th in PST, the results shouldn't be inclusive of the 5th. The results are also excluding some data from the day. This is due to the underlying conversion:

The automatic conversion of the daterange selection from PST to UTC is causing the discrepancy. To avoid this, specify no time zone in the query:

Without the conversion the result includes the entire count of logs for the 6th. The underlying SQL is interpreted as: