Time Zone Conversions for SQL Server Using User Attribute

Purpose: This article will show you how to convert datetimes in Microsoft SQL Server (MS SQL) using User Attributes and Liquid Syntax

Issue: Looker has various Time Zone Settings for the database connection, Looker instance, User time zones (if enabled), and LookML convert_tz. However, Microsoft SQL Server (MS SQL) is NOT a supported database dialect for time conversions.

Conversion Functions:
Datetime and Strings
In MS SQL, there are a few handy conversion functions. This is not an all-inclusive list.

Convert datetime to string uses a numerical code for each string-datetime format. These numerical codes are not intuitive and difficult to remember.

datetime_string = CONVERT(CHAR(23), my_datetime, 121) 

The DATEPART() function provides access to each date-time component as building blocks for creating date-strings.

And as of SQL Server 2012, FORMAT() and PARSE() functions are available for converting datetimes to strings and strings to datetimes, respectively.

datetime_string = FORMAT(my_datetime, 'yyyy-MM-dd HH-mm:ss.ffff')

Datetime Data Types
When creating your database tables, one must decide the appropriate data types to use.
Traditionally, DBA’s would use either DATETIME or SMALLDATETIME data types for datetime fields. With these fields, the time zone was not intuitive or explicit. Is the data stored in UTC time or in the time zone of the server?

Most modern database servers store datetimes in the UTC time zone. UTC has an advantage in that it is constant, based on GMT, and does not change for daylight savings. Many Unix/Linux servers store datetimes as Epoch time, the integer number of seconds (or milliseconds or microseconds) since January 1, 1970. As such, these datetimes are stored as integer or bigint data types. Also, integers are generally better/faster fields for storage and indexing, because they use less space.

epoch_int = 1520665200
utc_datetime = DATEADD(s, epoch_int, '19700101')

As of SQL Server 2008, the DATETIMESOFFSET data type is available. This data type explicitly captures the time zone and with this data type, there are several useful time zone conversion functions.

Datetime and Time Zones
SWITCHOFFSET() allows you to convert datetimes based on a time zone offeset string, -HH:MM.

pdt_time = SWITCHOFFSET(my_utc_time, '-07:00')

Since Unix Epoch Times are integers, you could convert times using a tz_offset and the number of seconds/hour (3600).

epoch_int = 1520665200
tz_offset = -7
local_pdt_int = epoch_int + (3600 * tz_offset)

However, these time zone offset options do not “elegantly” account for daylight savings time. The AT TIME ZONE function provides a better time zone conversion option to account for daylight savings time.

# MS SQL query for timezone_name
select * from sys.time_zone_info;
timezone_name = 'Eastern Standard Time'
# Time conversion UTC time to EST time
est_time = utc_time AT TIME ZONE 'UTC' AT TIME ZONE timezone_name

# Time conversion UTC Epoch integer to EST time
epoch_int = 1520665200
est_time = DATEADD(s, epoch_int, '19700101') AT TIME ZONE 'UTC' AT TIME ZONE timezone_name

LookML Example
This example assumes you have a looker_scratch database schema and a MS SQL connection in Looker.

In SQL Runner, create the following table with a bigint (unix epoch integer time) and datetimeoffset fields.

CREATE TABLE looker_scratch.epoch_time (
epoch_int BIGINT,
epoch_time DATETIMEOFFSET
);

Insert the following records. The last few records will help demonstrate datetime conversions for daylight savings time.

INSERT INTO looker_scratch.epoch_time (epoch_int, epoch_time) VALUES
(1564600381, DATEADD(s, 1564600381, '19700101')),
(1528504815, DATEADD(s, 1528504815, '19700101')),
(1528504814, DATEADD(s, 1528504814, '19700101')),
(1520710252, DATEADD(s, 1528504815, '19700101')),
(1520793052, DATEADD(s, 1520793052, '19700101')),
(1520665200, DATEADD(s, 1520665200, '19700101')),
(1520751600, DATEADD(s, 1520751600, '19700101'));

Query the table to view the data:

select * from looker_scratch.epoch_time;

Unfortunately, SQL Runner does not show the epoch_time (DATETIMEOFFSET data type) correctly. The data is there, but is shows as [object Object]. Fortunately, LookML is able to handle this data type.

Create User Attributes: In the Looker Admin Console, create the following User Attributes.

Name: timezone_offset
Data Type: Number Filter (advanced)
Default Value: -7
User Access: View
Hide Values: No
Name: timezone_name
Data Type: String Filter (advanced)
Default Value: Pacific Standard Time
User Access: View
Hide Values: No

Update User Settings: In the Admin Console, update your User settings to provide values for these User Attributes.
timezone_offset = -7
timezone_name = Pacific Standard Time

View LookML: Add the following Looker View called epoch_time. In this View, I show examples of using epoch_int time, local_int time, time conversions from epoch integers to date-time dimension groups, and time zone conversions using a User Attributes. My preference is the use the AT TIME ZONE function with a User Attribute timezone_name, as demonstrated in the pst_time dimension.

view: epoch_time {
  sql_table_name: looker_scratch.epoch_time ;;
  
  dimension: pst_time {
    label: "PST Time"
    type: date_time
    sql: DATEADD(s, ${epoch_int}, '19700101') AT TIME ZONE 'UTC' AT TIME ZONE '{{ _user_attributes['timezone_name'] }}' ;;
  }
  
  
  dimension: date_string_epoch_time {
    type: string
    sql: CONVERT(CHAR(23), ${TABLE}.epoch_time, 121) ;;
  }
  
  dimension: epoch_int {
    type: number
    value_format_name: id
    sql: ${TABLE}.epoch_int ;;
  }

  dimension_group: epoch {
    type: time
    datatype: epoch
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${epoch_int} ;;
  }
  
  dimension: local_int {
    type: number
    value_format_name: id
    sql: ${epoch_int} + (3600 * ({{ _user_attributes['timezone_offset'] }})) ;;
  }
  
  dimension_group: local {
    type: time
    datatype: epoch
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${local_int} ;;
  }
  
  dimension_group: epoch_dttm {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.epoch_time ;;
  }

  measure: count {
    type: count
    drill_fields: []
  }
}

Model LookML: In your Model, create an Explore to expose this View.

explore: epoch_time {}

Explore Epoch Time: Now you are able to Explore and view these time conversions. Create an Explore like the following. Rows 5 & 7 show the daylight savings time conversion for March 10 and 11, 2018.

Summary: I hope this approach provides some insight and examples for you to follow when doing time conversions for Microsoft SQL Server databases using Looker. Please let me know if you use this approach and any recommendations or changes you suggest.

2 Likes

Hey Jeff! Funny timing here—we’ve added support for timezone conversions to MS SQL Server 2016+ and will be releasing it in Looker v6.22. Hopefully that simplifies things for you.

Best,
Kevin Marr, Product Manager @ Looker

2 Likes

That’s great news! Thanks for the update.
Hopefully they perform reasonably well for grouping 1M+ record tables.
That’s been another related challenge… the order of operations for converting and grouping dates to strings seems to slow things down.
Reference: Date handling from LookML to SQL Server, suggestions