diff_months() is off by 1

[hden]

Symptoms

1st of every month is offset by 1

Expected

Semantically it should be a whole month (i.e. from the first day to the last day) in the configured timezone, at least it is what BigQuery’s date_diff function do.

with data_srouce as (
  select id, timestamp_seconds(timestamp) as timestamp
  from `bigquery-public-data.samples.wikipedia`
  where date(timestamp_seconds(timestamp), 'Asia/Tokyo') between date(2009, 12, 1) and date(2010, 4, 2)
)
select date(timestamp, 'Asia/Tokyo') as date
     , date_diff(date(timestamp, 'Asia/Tokyo'), date(1970, 1, 1), month) as date_diff
     , count(*) as count
from data_srouce
group by 1, 2
order by 1, 2

Screenshot

Environment

• Hosted instance: https://toreta.jp.looker.com
• Database: BigQuery
• Timezone: Asia/Tokyo

Custom Dimension

diff_months(date(1970, 1, 1), ${calendar.timestamp_date})

LookML

view: calendar {
  derived_table: {
    sql:
      select id, timestamp
      from `bigquery-public-data.samples.wikipedia`
      where date(timestamp_seconds(timestamp), 'Asia/Tokyo') between date(2009, 12, 1) and date(2010, 4, 2)
    ;;
  }

  dimension: id {
    label: "id"
    type: number
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension_group: timestamp {
    label: "timestamp"
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: timestamp_seconds(${TABLE}.timestamp)  ;;
  }

  measure: measure_count {
    type: count
  }
}