Time Zone Formatters

👤 This documentation is intended for SQL Users.

Converting Time Zones

Convert a datetime to a given timezone by putting the time zone name in brackets, e.g.

select [created_at:pst] from users -- Convert created_at to Pacific Time
select [created_at:est] from users -- Convert created_at to Eastern Time

To verify Periscope's timezone math, click on the "Query" tab to display the expanded SQL.

Supported Time Zones

The following timezones are currently supported:

USA:

:est

:cst

:mst

:pst

:hawaii

South America:

:pet

:brt

:cot

Europe:

:gmt

:wet

:cet

:eet

Africa

:eat

Israel:

:israel

India:

:ist

Australia:

:awst

:acst

:acdr

:aest

:aedt

New Zealand:

:nzst

South Korea:

:kst

Southeast Asia:

:wib

:myt

:sgt

UTC:

:utc

US Daylight Savings Time

American time zones use the current UTC offset to convert all dates. To illustrate, during daylight savings time, the :pst operator applies a -7 hours difference onto UTC for all dates in the dataset (regardless if the exact timestamp being converted was during daylight savings time). When daylight savings time ends, the :pst converter will switch to applying a -8 hours difference onto the UTC time.

To perform the (more expensive) historically accurate conversion, prepend an "h" for "historical", e.g.:

select [created_at:hpst] from users
select [created_at:hmst] from users
select [created_at:hcst] from users
select [created_at:hest] from users

The historical timezone operators are accurate going back to 2009.

Our support team is ready to help