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.
CUSTOMER NEWS - Our November 24 Release Is Now Available - Download It Now!