When working with data in BigQuery, you may sometimes need to convert the timestamp to date data types. This can be helpful when you want to perform certain operations on your data, such as filtering or aggregating by date.
Timestamps are a series of characters that indicate the time and day in a given data record. This data type is used to store date and time information. Meanwhile, the date data type is used to store information exclusively about dates.
These data types are part of Google BigQuery, which is a powerful tool for working with large data sets, particularly quantitative data. In this article, we’ll show you how to convert BigQuery timestamps to date data types.
Google BigQuery Date and Time Functions Overview
BigQuery provides a number of functions for working with timestamp and date data types. These functions can be used to perform operations such as calculating date and time ranges, comparing dates and times, and formatting dates and times.
Some instances where extracting and modifying information where time is a crucial factor
include monitoring daily or monthly transactions in a business, tracking work hours and clock in times for employees and determining peak sales hours.
The most commonly used BigQuery date and time functions are:
- DATE(timestamp): Returns the date portion of a timestamp as a Date data type.
- TIME(timestamp): Returns the time portion of a timestamp as a Time data type.
- DATETIME(timestamp): Returns a timestamp as a Datetime data type.
- FORMAT_DATETIME(format_string, timestamp): Formats a timestamp using the specified format string.
BigQuery also provides a number of other functions for working with timestamp and date data types that we’ll get into later.
Why Use BigQuery Timestamp and Date Functions?
BigQuery timestamp and date functions are useful for a number of reasons. Some common use cases include:
- Calculating dates and times: You can use BigQuery timestamp and date functions to calculate dates and times. For example, you can use the DATE() function to calculate the date portion of a timestamp, or the TIME() function to calculate the time portion of a timestamp.
It is an ideal alternative if you want to track hours spent on specific tasks. If you are trying to gauge the individual performance of your team, this function allows you to determine possible areas where your staff may be experiencing difficulties.
- Comparing dates and times: You can use BigQuery timestamp and date functions to compare dates and times. For example, you can use the DATETIME() function to compare two timestamps, or the FORMAT_DATETIME() function to format a timestamp for comparison.
Using the function to compare dates and times is typically used by marketing teams to compare how their campaigns fared during the first and second quarters of a given month or year.
- Formatting dates and times: You can use BigQuery timestamp and date functions to format dates and times. For example, you can use the FORMAT_DATETIME() function to format a timestamp for display or comparison. This function lets you present data in an organised manner.
BigQuery Timestamp vs Date Functions
BigQuery provides two types of functions for working with date and time data: timestamp functions and date functions. Timestamp functions are used to calculate dates and times, while date functions are used to compare dates and times.
In this manner, users will not have to deal with any issues when managing time-dependent information in their database. Say a company needs to create a year–end report of its expenditures. It will be tricky to determine which months generated the highest expenses and under what circumstances, with the dates and times all mixed up in the database.
Another benefit to the separate uses of timestamp and date functions is being able to isolate only the date or time from a timestamp. If the time and date in a work database are primarily used to monitor employees’ daily working hours, then implementing a function that compares their attendance on different dates may not be necessary.
Also, it is important to note that BigQuery timestamp functions return a timestamp data type, while BigQuery date functions return a date data type.
BigQuery Timestamp Functions
Here is a list of some of the most commonly used BigQuery timestamp functions:
CURRENT_TIMESTAMP()
As the name suggests, the function shows today’s date and the current time. It is important to add a specific range variable and enclose it in parenthesis to avoid yielding an ambiguous result.
Here’s a use case example:
WITH t AS (SELECT ‘column value’ AS `current_timestamp` )SELECT current_timestamp( ) AS now, t.current_timestamp FROM t; |
now | current_timestamp |
2020-06-02 23:57:12. 120174 UTC | column value |
In this example, adding “now” in the CURRENT_TIMESTAMP() function yields the exact date and time when the variable was added. It is then reflected in the column value.
When using the Current_Timestamp() function, note that it supports an optional timezone parameter.
EXTRACT()
EXTRACT(part FROM timestamp_expression [AT TIME ZONE time_zone]) |
This function returns a value from a timestamp_expression. All you have to do is specify which part of the supply timestamp_expression needs to be modified. Below is a list of part values accepted when you use the EXTRACT function:
- DATE
- DATETIME
- TIME
- DAY
- DAYOFWEEK
- DAYOFYEAR
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- MONTH
- QUARTER
- YEAR
- WEEK (<WEEKDAY>)
- ISOWEEK
- ISOYEAR
STRING()
STRING(timestamp_expression[, timezone]) |
This function is used to convert a timestamp_expression into a STRING data type. String functions are applicable to values that use the STRING and BYTES data types. The String function enables an optional parameter if you need more specific time zones.
Also, it is important to provide specific and correct UTF-8 timezones if you are going to use the STRING() function.
TIMESTAMP_ADD()
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part) |
Without taking time zones into consideration, this function implements int64_units of date_part to the timestamp.
For date_part, the following values are supported by the TIMESTAMP_ADD() function:
- DAY (24 hours)
- HOUR (60 minutes)
- MINUTE
- SECOND
- MICROSECOND
- MILLISECOND
TIMESTAMP_SUB()
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part) |
Similarly to the TIMESTAMP_ADD() function, TIMESTAMP_SUB() is also independent of any timezone. The purpose of this function is to subtract int64_expressions units from the timestamp’s date_part.
The function likewise supports the same date_part values as the TIMESTAMP_ADD() function.
BigQuery Date Functions
The following are the functions used in BigQuery to extract date and time information:
CURRENT_DATE()
CURRENT_DATE( [time_zone] ) |
The CURRENT_DATE() function returns the current date while following a particular timezone. In the event that no timezone is specified, the function automatically selects UTC by default. Timezone parameters that are determined to be NULL generate a NULL result.
EXTRACT()
EXTRACT(part FROM date_expression) |
The EXTRACT() function is a key function in obtaining specific information, such as a date, year, or month, from the Date Expression. To do this, you need to use the return type INT64.
For instance, you want to extract the exact day from a given date expression, which is 2022-11-15.
Use this syntax, following the example above:
SELECT EXTRACT(DAY FROM DATE ‘2022-11-15’ AS day. |
‘15’, which is the exact day, will appear under “day”’ when you use the Extract function.
DATE_ADD()
The DATE_ADD() function can be used to add a specific interval to a DATE.
The following date_part values are supported:
- YEAR
- QUARTER
- MONTH
- WEEK (Corresponds to 7 Days)
- DAY
Keep in mind the following in the event that you want to modify a QUARTER, MONTH, or YEAR: The resulting date for months that have fewer days than the original date will be the last day of that specific month.
DATE_SUB()
DATE_SUB(date_expression, INTERVAL int64_expression date_part ) |
The DATE_SUB() function is similar to DATE_ADD. It is also used to subtract a predetermined time period from a date that is supplied as an input or argument to the function.
It supports the following date_part values:
- YEAR
- QUARTER
- MONTH
- WEEK (Corresponds to 7 Days)
- DAY
DATE_DIFF()
DATE_DIFF(date_expression_a, date_expression_b, date_part |
The DATE_DIFF() function is used to restore the entire number of given or specified date_part intervals between two DATE objects. It yields a negative output if the first date precedes the second one.
BigQuery Timestamp to Date Functions
BigQuery provides four functions for converting timestamp to date data types:
DATE(), TIME(), DATETIME(), and FORMAT_DATETIME().
DATE(timestamp)
DATE(timestamp_expression[, time_zone] ) |
This function returns the date portion of a timestamp as a Date data type. For example, if the timestamp is “2018-12-01 12:00:00”, the DATE() function will return “2018-12-01”.
TIME(timestamp)
TIME(timestamp, [time_zone] ) |
This function returns the time portion of a timestamp as a Time data type. An example of this would be if the timestamp was “2018-12-01 12:00:00”, the TIME() function would return “12:00:00”.
DATETIME(timestamp)
DATE(datetime_expression) |
This function returns a timestamp as a Datetime data type. For example, if the timestamp is “2018-12-01 12:00:00”, the DATETIME() function will return “2018-12-01 12:00:00”.
FORMAT_DATETIME(format_string, timestamp)
FORMAT_DATETIME(format_string, datetime_expression) |
This function formats a timestamp using the specified format string. For example, if the format string is “yyyy-MM-dd HH:mm:ss” and the timestamp is “2018-12-01 12:25:00”, then the timestamp should appear like this: Sat Dec 01 12:25:00 2018
Unlock Your Business's Potential with Our Proven Solutions
Want to see what we can do for your business? Check out our data & reporting case studies below to see the success stories of our clients in different industries. Learn about how we’ve helped them achieve their goals and see if we can do the same for you.
How to Convert BigQuery Timestamp to Date Data Types?
Timestamp to Date using the CAST() Function
There are three different ways to convert timestamp to date data types using the CAST() function: CAST AS DATE, CAST AS DATETIME, and CAST AS TIMESTAMP.
The following data types are accepted by these functions:
- DATETIME
- TIME
- STRING
- TIMESTAMP
CAST AS DATE
CAST(expression AS DATE [format_clause] ) |
CAST AS DATETIME
CAST(expression AS DATETIME [format_caluse] ) |
Use a format clause if you need clearer instructions when casting from one expression type to another. For instance, if you are casting from a timestamp to a date, the timestamp is automatically abbreviated into the default time zone.
Here’s an example:
SELECT CAST(“2018-12-01 12:00:00” AS DATE); |
The result will be: 2018-12-01
CAST AS TIMESTAMP
CAST(expression AS TIMESTAMP [ format_clause [AT TIME ZONE timezone_expr ] ] ) |
In this function, there is an optional, additional clause. This clause, AT TIME ZONE timezone_expr, is useful if you want to specify a particular time zone as you convert the timestamp. Otherwise, the function will use your current time zone.
Timestamp to Date from STRING Using the PARSE_DATETIME() Function
PARSE_DATETIME(format_string, datetime_string) |
You can use the PARSE_DATETIME() function to convert a string to a timestamp or datetime object. The following example converts the string “2018-12-01 12:00:00” to a timestamp:
SELECT PARSE_DATETIME(“%Y-%m-%d %H:%M:%S”, ‘2018-12-01 12:00:00’ ) |
And it yields this result: 2018-12-01 12:00:00
Timestamp to Date Using the Extract() Function
The following example extracts the date from the timestamp “2018-12-01 12:00:00”:
SELECT EXTRACT(DATE FROM “2018-12-01 12:00:00”); |
The output is: 2018-12-01
Conclusion
You can use BigQuery timestamp to date functions to convert timestamps to dates. The CAST(), PARSE_DATETIME(), and EXTRACT() functions are the most commonly used functions for this purpose.
If you’re new to BigQuery and need help managing all your agency data inside of it, Acuto can help. We are a team of experts who specialise in developing custom data warehouses and automation scripts.
We can help you consolidate all your data from different sources into one BigQuery data warehouse. This will allow you to query billions of rows of data and unlock new insights you couldn’t before.
Contact us today to learn more about our services.