Working With Dates and Times in BigQuery

Welcome to the world of dates and times. In this tutorial, we’ll explore the critical elements of event_date and event_timestamp, two fundamental components of every event. As you traverse the GA4 BigQuery data, you’ll frequently encounter these fields. However, understanding their nuances is essential. Continue reading to delve deeper into the intricacies of working with date and time data.

This is part 8 of 10 in our series, “Get Started with the GA4 BigQuery Export”. Check out the course overview for a full list of all the topics covered.

Introduction

Every event within the GA4 BigQuery export is output with an event_date and event_timestamp – two fields that hold immense importance as you work with the data. These timestamps serve as the pillars of time-based insights, but they come with distinct characteristics that demand attention.

While event_date synchronizes with the timezone configured for your GA4 property, event_timestamp adheres to UTC. This distinction shapes how you interact with these fields, and we’re here to guide you through the intricacies.

For instance, picture your GA4 property set to the New York timezone. Without converting your timestamps to the same timezone, they’ll be out of sync with your users by 4 or 5 hours, failing to accurately capture when your users engaged with your website or app.

While your users could be in various timezones, if your GA4 property adopts New York time, it’s reasonable to assume that your primary audience shares or overlaps this timezone. Conversely, if your GA4 property aligns with UK time (essentially UTC, barring daylight savings), the need for timestamp conversion becomes less critical.

In this tutorial, we’ll explore the fascinating interplay of timezones and timestamps, ensuring you can harness the full potential of date and time data within the GA4 BigQuery export.

Working with event_date

The event_date field is stored as a STRING in the format “YYYYMMDD”, which offers flexibility in how you choose to work with it. You can leave the value as it is or opt to parse it into a DATE format, converting it into the more standard “YYYY-MM-DD” format for ease of analysis

See below example showing the use of the PARSE_DATE function:

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS event_date
FROM `<project>.<dataset>.<table>`

Once event_date is parsed into a DATE format, you’ll also then have the ability to modify and extract additional information from it. This includes dissecting it into its constituent parts, such as the year, month, week, day of the week, and more.

Extract year from event_date

The following query extracts the year from the parsed event_date field:

SELECT
  FORMAT_DATE('%Y', PARSE_DATE('%Y%m%d',event_date)) AS year
FROM `<project>.<dataset>.<table>`

Extract month from event_date

The following query extracts the month from the parsed event_date field:

SELECT
  FORMAT_DATE('%m', PARSE_DATE('%Y%m%d',event_date)) AS month
FROM `<project>.<dataset>.<table>`

Extract day of week from event_date

The following query extracts the day from the parsed event_date field:

SELECT
  FORMAT_DATE('%A', PARSE_DATE('%Y%m%d',event_date)) AS day
FROM `<project>.<dataset>.<table>`

Using the FORMAT_DATE function, you have the flexibility to format your parsed event_date into various date representations. Some additional inputs for the FORMAT_DATE function include:

• %D: the date in the format %m/%d/%y (e.g. 07/28/23)
• %d: the day of the month as a decimal number (01 to 31)
• %U: the week number (01 to 52)
• %B: the full month name

Many more options are available, allowing you to tailor the date format to your specific analysis needs. Remember that these format codes are case-sensitive, so accuracy in their usage is key. You can explore the full list of format options here.

Working with event_timestamp

The event_timestamp is stored as a value in microseconds, strictly adhering to UTC. This format, while precise, can appear quite cryptic and lacks human readability. To make sense of these values, your first step is converting it into a more comprehensible format.

The simplest approach is to convert it into a TIMESTAMP, as the below example shows:

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_time
FROM `<project>.<dataset>.<table>`

Extract time from event_timestamp

But converting to a TIMESTAMP alone may not provide the ideal human-readable format, as you’ll still encounter a value like this: 2023-07-28 09:50:49.785434 UTC.

To achieve a more refined representation, you can utilize the following query. It efficiently extracts the hour, minutes, and seconds and assembles them into the user-friendly format “HH:MM:SS”‘:

SELECT
  CONCAT(
    FORMAT('%02d',EXTRACT(hour FROM TIMESTAMP_MICROS(event_timestamp))), ':',
    FORMAT('%02d',EXTRACT(minute FROM TIMESTAMP_MICROS(event_timestamp))), ':',
    FORMAT('%02d',EXTRACT(second FROM TIMESTAMP_MICROS(event_timestamp)))
  ) AS event_time
FROM `<project>.<dataset>.<table>`

Convert timezones

The resulting value from the query above will still be in UTC. If you need to convert it to another timezone, you can further enhance your query by incorporating the TIME function:

SELECT
  CONCAT(
    FORMAT('%02d',EXTRACT(hour FROM TIME(TIMESTAMP_MICROS(event_timestamp), 'Europe/London'))), ':',
    FORMAT('%02d',EXTRACT(minute FROM TIME(TIMESTAMP_MICROS(event_timestamp), 'Europe/London'))), ':',
    FORMAT('%02d',EXTRACT(second FROM TIME(TIMESTAMP_MICROS(event_timestamp), 'Europe/London')))
  ) AS event_time
FROM `<project>.<dataset>.<table>`

In the example provided above, we demonstrated the conversion to the “Europe/London” timezone. However, you have the flexibility to convert to whichever timezone aligns to your specific needs. Some alternative timezone values include:

• America/New_York
• America/Los_Angeles
• Asia/Tokyo
• Australia/Sydney

Click here for the full list of timezone names. Another advantage of converting timezone in this manner is that it automatically accounts for daylight saving time adjustments, saving you the trouble of manual calculations.

Wrapping up

Congratulations on mastering the intricacies of working with dates and times in BigQuery. You’ve learned how to parse event_date, format event_timestamp, and even convert timestamps to different timezones. These skills are invaluable for conducting time-based analyses or creating any kind of time-series report.

Now, it’s time to take your knowledge to the next level. In the next tutorial, we’ll explore how to transform your data into actionable metrics and build insightful reports that can drive data-driven decision-making.

Next >> 9. Creating GA4 metrics and reports in BigQuery