You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Date and time: dimensions & metrics

Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in
💡
This article is about GA3 - Universal Analytics

This example query contains all following Google Analytics date and time dimensions and metrics. If you only need one dimension or metric, look at the -- comments in the example query and copy the part you need from the select clause. Make sure that you also add any additional conditions (in the from, where, group by and order by) that are necessary to calculate the results correctly.

Time dimensions

  • date
  • year
  • iso year
  • month of year
  • month of the year
  • week of year
  • week of the year
  • iso week of the year
  • iso week of iso year
  • day of the month
  • day of week
  • day of week name
  • hour
  • minute
  • hour of day
  • date hour and minute

Time metrics

-

Example query

select
  -- date (dimension)
  date as date,
  -- year (dimension)
  format_date('%Y', parse_date("%Y%m%d",date)) as year,
  -- iso year (dimension)
  format_date('%G', parse_date("%Y%m%d",date)) as iso_year,
  -- month of year (dimension)
  format_date('%Y%m', parse_date("%Y%m%d",date)) as month_of_year,
  -- month of the year (dimension)
  format_date('%m', parse_date("%Y%m%d",date)) as month_of_the_year,
  -- week of year (dimension)
  format_date('%Y%U', parse_date("%Y%m%d",date)) as week_of_year,
  -- week of the year (dimension)
  format_date('%U', parse_date("%Y%m%d",date)) as week_of_the_year,
  -- iso week of the year (dimension)
  format_date('%W', parse_date("%Y%m%d",date)) as iso_week_of_the_year,
  -- iso week of iso year (dimension)
  format_date('%G%W', parse_date("%Y%m%d",date)) as iso_week_of_iso_year,
  -- day of the month (dimension)
  format_date('%d', parse_date("%Y%m%d",date)) as day_of_the_month,
  -- day of week (dimension)
  format_date('%w', parse_date("%Y%m%d",date)) as day_of_week,
  -- day of week name (dimension)
  format_date('%A', parse_date("%Y%m%d",date)) as day_of_week_name,
  -- hour of day(dimension)
  concat(date,cast(extract (hour from timestamp_seconds(visitstarttime)) as string)) as hour_of_day,
  -- hour (dimension)
  format("%02d",extract (hour from timestamp_seconds(visitstarttime))) as hour,
  -- minute (dimension)
  format("%02d",extract (minute from timestamp_seconds(visitstarttime))) as minute,
  -- date hour and minute (dimension)
  concat(concat(date,format("%02d",extract (hour from timestamp_seconds(visitstarttime))),format("%02d",extract(minute from timestamp_seconds(visitstarttime))))) as date_hour_and_minute
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
where
  totals.visits = 1
order by
  date_hour_and_minute desc

Remark #1: Timezone formatting

BigQuery displays data usually in UTC. That leads to problems when using date formatting functions because dates and times can be off. Converting the datetimes prior formatting into the correct timezone solves those issues. (source & how to)