Using AS AT TIME ZONE on MS SQL Server

This allows you to report or filter any datetime object adjusted for it’s own timezone.

Example: You hold all dates within you DB as UTC

You can get a list of installed timezones and current offsets from UTC by quering

SELECT * FROM sys.time_zone_info;

You can convert this into useful objects in a Pi Data connection (you’d want to create a specific one named something like Time zones)

Objects like those below are useful

I’ve added this as a category object

Then in any date object I can add the adjustment. This is taking the input from a user selection {{ }}

But could easily be a [[variable]] or a [DBColumn] or even an #~object~# calculation

This can then be referenced as you would any other object.

It takes into account things like daylight savings.

NOTE:

This does potentially have a performance impact on MS SQL.

We have solved for this is pi by creating a measure object that we can add to any chart or table that looks like this

The actual SQL looks like

[Tickets].[ticket_logged_date] BETWEEN CAST({{Date[0]}' AS DATETIME) AT TIME ZONE '{{Time zone}}' AND CAST('{{Date[1]}}' AS DATETIME) AT TIME ZONE '{{Time zone}}'

This can then be added to anything as

This forces the time zone adjust to the input dates and preserves the use of the index on the date field.