Converting Date and Time Formats in Google BigQuery

Introduction

Google BigQuery provides a comprehensive set of functions for handling date and time data, including converting between different formats. This article will explore the CONVERT_TIME_STAMP and CONVERT_TIME_ZONE functions, which allow you to convert between timestamps and dates/times in different time zones.

Converting Timestamps to Dates and Times

The CONVERT_TIME_STAMP function can be used to convert a timestamp value into a date and time value in a specific time zone. The syntax of the function is as follows: CONVERT_TIME_STAMP(time_stamp, tz) -> DATE, TIME * time_stamp: The timestamp value to convert. * tz: The time zone to convert the timestamp to. For example, the following query converts the timestamp value 2010-01-11 133000 to the date and time value 2010-01-11 13:30:00 in the America/Los_Angeles time zone: SELECT CONVERT_TIME_STAMP('2010-01-11 133000', 'America/Los_Angeles') AS converted_datetime;

Converting Dates and Times to Timestamps

The CONVERT_TIME_ZONE function can be used to convert a date and time value in a specific time zone to a timestamp value. The syntax of the function is as follows: CONVERT_TIME_ZONE(dat, tim, tz) -> TIMESTAMP * dat: The date value to convert. * tim: The time value to convert. * tz: The time zone to convert the date and time to. For example, the following query converts the date and time value 2010-01-11 13:30:00 in the America/Los_Angeles time zone to the timestamp value 2010-01-11 133000: SELECT CONVERT_TIME_ZONE('2010-01-11', '13:30:00', 'America/Los_Angeles') AS converted_timestamp;