Periscope Data Documentation

Connecting to Periscope

First Time Connecting

Connection Definitions

Common Connection Errors

Private Network SSH Tunnels

Integrations

Overview

Getting Started

Keyboard Shortcuts

Charts & Dashboards

Chart Overview

Chart Options

Dashboard Overview

Refresh Mechanics

Organizing Charts

Organizing Dashboards

Table-like Charts

Table Overview

Cohort Grids

Pivot Tables

Graph-like Charts

Overview & General Settings

Chart Types and Settings

Mixing Chart Types

Map Charts

Map Chart Overview

Heatmaps

Clusters and Bubbles

Satellite Maps

GeoJSON

Other Charts & Customizations

Pie Charts

Image Charts

Chart Annotations

Site Administration

Adding Users

Removing Users

User Types

User Groups

Two-Factor Authentication

Managing User Permissions

Dashboard Permissions

Site Preferences

Query Management

Color Themes

Dashboard Refresh Management

Usage Data

Periscope Cache

Caching Strategies

Querying the Periscope Cache

Cross-Database Joins

CSV Upload

Materializing Views

SQL Formatters

Using SQL Formatters

Composing SQL Formatters

Dollars and Percentages

Time Zone Formatters

Date Aggregation

Date Parts

Basic Filters

Custom Filters

Date Range Filters

Aggregation Filter

Adding, Moving, and Hiding Filters

Advanced Filters

Default Filters

Drilldowns

Parent-Child Filters

Matching Filter Values

Time Zone Conversions

Direct Replacement

User Friendly Names

Views

SQL Views

Joining to Views

More SQL Tools

SQL Snippets

Parameterized SQL Snippets

SQL Alerts

Automatic Joins

Sharing and Embedding

Sharing Dashboards Externally

Email Reports

Easy Embedding

Embed API

Embed API Options

Public CSV URL

Slack API

Slack Linking Reports

Data Management

Adding and Deleting Datasource

Spaces: Data-Level Permissions

Improving Query Throughput

Revoking Table Permissions

Salesforce Datasource

Schema Browser

SSO Integrations

Overview

Okta

OneLogin

Google Apps

Time Zone Conversions

When using the Periscope Data 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 Periscope 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(*)
FROM users
WHERE [created_at=daterange]
GROUP BY 1

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: