Why are my timestamps being converted twice? / Multiple timezone conversion

[sam8]

Knowledge Drop

Last tested; May 31, 2018
 

You're most likely seeing this because we're referencing a date/time field inside another date/time field. For example, say we have the following:

dimension_group: created {

type: time

timeframes:[date, year, month, quarter, raw]

sql: ${TABLE}.created_at ;;

}
 

dimension: bad_date {

type: date_time

sql: date_add(${created_date},1) ;;

}

If we select the bad_date dimension, we will see multiple timezone conversions applied in the SQL.

Instead, we should use the _raw timeframe like so:
 

dimension: good_date {

type: date_time

sql: date_add(${created_raw},1) ;;

}

Which correctly generates only one conversion:

This content is subject to limited support.