Dates and Times

Introduction

BigQuery offers no shortage of functionality to help you get the most out of date and time data, but it can be hard to know what to use when. This notebook will review and compare BigQuery's date and time functionality.

The Basics

Data types

In BigQuery, dates and times can be one of the following datatypes:

DATE

: calendar date (e.g. 2020-01-01)

DATETIME

: calendar date and time (e.g. 2020-01-01 13:04:11)

TIMEZONE

: a particular moment in time, can include timezone but defaults to UTC (e.g. 2020-01-01 13:04:11-5:00)

TIME

: a time as seen on a watch (e.g. 13:04:11)

In general, if you want to work with time zones, you'll need to stick with TIMESTAMPs. Otherwise DATETIME is the most flexible since you can take advantage of date and time functionality.

Converting to date/time data types

If your data isn't in one of these data types, you can convert them by using CAST , one of the PARSE functions, or unix time conversion functions.

Converting using CAST

To convert your STRING to one of the Date data types, your STRING must be in the following formats:

DATE

:

YYYY-MM-DD

DATETIME

:

YYYY-MM-DD HH:MM:SS

TIMESTAMP

:

YYYY-MM-DD HH:MM:SS [timezone]

TIME

:

HH:MM:SS

Converting from STRING using PARSE

To use one of the PARSE functions, your STRING can be formatted any way you like, you'll just tell the function how it should read it. There is a PARSE function for each Date/Time Data type:

DATE

:

PARSE_DATE(format_string, date_string)

DATETIME

:

PARSE_DATETIME(format_string, datetime_string)

TIMESTAMP

:

PARSE_TIMESTAMP(format_string, timestamp_string[, timezone])

TIME

:

PARSE_TIME(format_string, time_string)

.You can see the full list of format stringshere

For example, if my date was in this format: Thursday, January 7, 2021 12:44:02, I could use the following:

Converting from Unix time

BigQuery offers several helper functions to get dates represented as numbers converted to a Date/Time date type.

These include:

DATE

:

DATE_FROM_UNIX_DATE(days since 1970-01-01 00:00:00 UTC)

TIMESTAMP

:

TIMESTAMP_SECONDS(seconds since 1970-01-01 00:00:00 UTC)

TIMESTAMP_MILLIS(milliseconds since 1970-01-01 00:00:00 UTC)

TIMESTAMP_MICROS(microseconds since 1970-01-01 00:00:00 UTC)

SELECT
  TIMESTAMP_SECONDS(1577836800)

If you need to BigQuery also offers a series of functions to convert your Date/Time data types into Unix time.

Formatting your date/times

When working with dates and times, it's often handy (and helpful for anyone else looking at the data) so see the data in a more approachable format. To do that we can make use of the FORMAT functions in BigQuery.

There is a FORMAT function for each Date/Time Datatype:

DATE

:

FORMAT_DATE(format_string, date)

DATETIME

:

FORMAT_DATETIME(format_string, datetime)

TIMESTAMP

:

FORMAT_TIMESTAMP(format_string, timestamp[, timezone])

TIME

:

FORMAT_TIME(format_string, time)

The format strings here are the same as for the PARSE function here.

So if I want to show my dates in a more common format of: YYYY/DD/MM then I could do FORMAT_DATETIME('%Y/%d/%m', datetime_column

⚠️ It's worth noting that FORMAT functions return STRINGs so if you wanted to use the result of FORMAT as a DATE, it won't work.

For example, this query:

will return:

Comparing Dates

One of the most common things we use dates for is to compare them. We'll do this to filter for the last week of data, or only for data that was within certain dates.

Comparing with operators

The easiest way to compare dates is to use a comparison operator:

<

,

<=

,

>

,

>=

,

=

,

!=

or

<>

,

[NOT] BETWEEN

,

[NOT] LIKE

,

[NOT] IN

Alternatively, you can use >=,< to achieve the same result:

Dynamic comparisons

Sometimes we want our query to always pull the last n days so comparing to a single date means we'll have to keep updating our query. For that we can use dynamic comparisons instead.

The most common functions we'll use for dynamic comparisons are CURRENT_[date_part] and [date_part]_DIFF

To return the current date, datetime, time, or timestamp, you can use the CURRENT_[date part] function in BigQuery. They will return the type you've specified, so you can use it to compare against other dates.

CURRENT_DATE([time_zone])

CURRENT_DATETIME([time_zone])

CURRENT_TIMESTAMP()

CURRENT_TIME([time_zone])

To find the difference in two dates, use [date_part]_DIFF:

DATE_DIFF(date_expression_a, date_expression_b, part)

(

parts available

)

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

(

parts available

)

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, part)

(

parts available

)

TIME_DIFF(time_expression_a, time_expression_b, part)

(

parts available

)

What if we wanted to filter our data for the last 12 months? To do that, we'll compare our date column to the CURRENT_DATE and make sure the difference is 12 or fewer months.

WHERE date_diff(current_date(),date,DAY) <= X

What if we wanted to find the difference between 2 dates- the expected arrival date, and the actual arrival date, but we wanted to exclude Sundays when no deliveries take place?

To do this we can take the total days (3) and subtract the weeks difference (1) which will include Sundays.

Adding/Subtracting Dates

To perform any kind of data transformations like adding a year to a date, or subtracting 2 weeks, then we can use the [date_part]_ADD and [date_part]_SUB functions.

To add an interval to a date/time in BigQuery we can use any of:

DATE_ADD(date_expression, INTERVAL int64_expression part)

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression part)

TIME_ADD(time_expression, INTERVAL int64_expression part)

What if we had contract start dates, contract durations, and we wanted to calculate the contract end date?

To subtract an interval from a date/time in BigQuery, we can use any of:

DATE_SUB(date_expression, INTERVAL int64_expression part)

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression part)

TIME_SUB(time_expression, INTERVAL int64_expression part)

How to subtract 3 business days from a date?

For this we'll use different DATE_SUB intervals depending on what day of the week it is.

Grouping Dates

When we're analysing date/time data we want to group our data by a different date part (e.g. Yearly, Quarterly, etc.). There are a few ways to group our Date/Times BigQuery.

Truncating dates and times

Truncating a date / time means you group the date by a specific date part. For example truncating Tuesday 15 December 2020 to the WEEK would return the first day of the week: Sunday 13 December 2020, to the YEAR would return Wednesday 1 Jan 2020, etc.

DATE_TRUNC(date_expression, part)

(

parts available

)

DATETIME_TRUNC(datetime_expression, part)

(

parts available

)

TIMESTAMP_TRUNC(timestamp_expression, part)

(

parts available

)

TIME_TRUNC(time_expression, part)

(

parts available

)

⚠️ It's important to note that TRUNC functions return another Date / Time object. So you can use the results to compare to other date / times.

To get the LAST day of each date_part you can use the LAST_DAY function instead of the TRUNC functions above.

Extracting date and time parts

Alternatively, you can EXTRACT a date_part from a Date / Time. This is helpful if you want to do some arithmetic with your date_part, like comparing the number of visitors on your website regardless of the date.

EXTRACT(part FROM date_expression)

EXTRACT(part FROM datetime_expression)

EXTRACT(part FROM timestamp_expression)

EXTRACT(part FROM time_expression)

The difference between EXTRACT and TRUNC

The key difference is the data types returned. TRUNC will return a DATE, DATETIME, TIMESTAMP, TIME object, and in most cases EXTRACT returns an INT64.

Related Pages

Data Types

DATE_DIFF

DATETIME_DIFF

TIMESTAMP_DIFF

TIME_DIFF

DATE_TRUNC

DATETIME_TRUNC

TIMESTAMP_TRUNC

TIME_TRUNC

Subscribe to newsletter

Subscribe to receive the latest blog posts to your inbox every week.

I’m happy to receive Count’s newsletter and other marketing. I can unsubscribe at any time.

By subscribing you agree to our

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Start solving your organization's biggest problems with Count today.

Stay up to date with all things count, data and problem solving.

Get started

Book a demo

Use Cases

Metric trees

Reports

Data modelling

Data exploration

Explore

Gallery

Pricing

Community

Customer success stories

Integrations

Learn

Blog

Webinars

Podcast

Videos

SQL tutorials

Company

About

Careers

Security

Keep up to date with all things data

Stay up to date with all things count, data and problem solving.

Subscribe to newsletter

© 2025 Count Technologies Ltd. All rights reserved.

Privacy Policy

Terms of Use

Cookies Policy

Consent preferences

/*window.intercomSettings = {
api_base: "https://api-iam.intercom.io",
app_id: "m36om8pq",
background_color: '#2d2af9'
};*/

document.addEventListener('DOMContentLoaded', function() {
var intercomLaunchers = document.querySelectorAll('[data-intercom-target="true"]');
intercomLaunchers.forEach(function(launcher) {
launcher.addEventListener('click', function() {
Intercom('show');
});
});
});

document.addEventListener("DOMContentLoaded", function() {
var currentPageUrl = window.location.href; // Capture the current page URL
var hiddenField = document.querySelector('input[name="sourcePage"]'); // Adjust if you named your hidden field differently

if(hiddenField) {
hiddenField.value = currentPageUrl; // Set the hidden field value to the current page URL
}
});

document.addEventListener('DOMContentLoaded', function() {
const mobileMenuToggle = document.querySelector('.mobile-menu-toggle');

mobileMenuToggle.addEventListener('click', function() {
document.body.classList.toggle('no-scroll');
});
});

document.querySelectorAll('.dropdown-link').forEach(function(trigger) {
trigger.addEventListener('click', function() {
// Get the target dropdown ID from data-target
var targetDropdownId = this.getAttribute('data-target');
var targetDropdown = document.getElementById(targetDropdownId);
var pageOverlay = document.querySelector('.page-overlay');

// If the clicked dropdown is already open, close it and hide the overlay
if (targetDropdown.style.display === 'block') {
targetDropdown.style.display = 'none';
pageOverlay.style.display = 'none'; // Hide overlay
} else {
// Otherwise, hide all dropdowns first and show the overlay
document.querySelectorAll('.dropdown-content').forEach(function(dropdown) {
dropdown.style.display = 'none';
});

// Show the clicked dropdown
if (targetDropdown) {
targetDropdown.style.display = 'block';
pageOverlay.style.display = 'block'; // Show overlay
}
}
});
});

document.addEventListener('DOMContentLoaded', function () {
// Get the YouTube iframe
const videoPlayer = document.getElementById('video-player');
if (!videoPlayer) {
//console.error('YouTube iframe with ID "video-player" not found!');
return;
}

// Store the original iframe src
const originalSrc = videoPlayer.src;

// Add event listeners to all buttons with data-timestamp
const buttons = document.querySelectorAll('[data-timestamp]');
buttons.forEach(button => {
button.addEventListener('click', function () {
// Get the timestamp from the button's data attribute
const timestamp = this.getAttribute('data-timestamp');
if (!timestamp) {
console.error('Button does not have a valid "data-timestamp" value.');
return;
}

// Update the iframe src to include the timestamp and autoplay
const baseUrl = "https://www.youtube.com/embed/MZyeKlw76L4";
videoPlayer.src = `${baseUrl}?start=${timestamp}&autoplay=1`;

console.log(`Video updated to start at ${timestamp} seconds.`);
});
});

// Add event listener for modal close button
const closeButton = document.querySelector('[data-close]');
if (!closeButton) {
console.error('Close button with data-close attribute not found!');
return;
}

closeButton.addEventListener('click', function () {
// Reset the iframe src to stop the video
videoPlayer.src = '';
console.log('Video stopped because modal was closed.');

// Restore the original src after a short delay
setTimeout(() => {
videoPlayer.src = originalSrc;
console.log('Video iframe src restored.');
}, 100); // Slight delay to avoid immediate reload
});
});

document.addEventListener('DOMContentLoaded', function () {
const searchParams = new URLSearchParams(window.location.search);
const nav = searchParams.get('nav');
if (nav == 'new') {
document.querySelector('a[data-target="product-dropdown"]').setAttribute('data-target', 'product-dropdown-new');
}
})

document.addEventListener('DOMContentLoaded', function () {
let faqArray = [];
let questionElements = document.querySelectorAll('[faq-question]');
let answerElements = document.querySelectorAll('[faq-answer]');

for (let i = 0; i < questionElements.length; i++) {
let question = questionElements[i].innerText;
let answer = '';

for (let j = 0; j < answerElements.length; j++) {
if (questionElements[i].getAttribute('faq-question') === answerElements[j].getAttribute('faq-answer')) {
answer = answerElements[j].innerText;
break;
}
}

faqArray.push({
"@type": "Question",
"name": question,
"acceptedAnswer": {
"@type": "Answer",
"text": answer
}
});
}
if (faqArray.length == 0) { return; }
let faqSchema = {
"@context": "https://schema.org",
"@type": "FAQPage",
"mainEntity": faqArray
}

let script = document.createElement('script');
script.type = "application/ld+json";
script.id = 'faq-metadata'
script.innerHTML = JSON.stringify(faqSchema);

if (document.querySelector('#faq-metadata') == null) {
document.getElementsByTagName('head')[0].appendChild(script);
}
})

.prose-rich-text .w-embed {
background-color: #f8f8f8;
padding: 12px;
}

.prose-rich-text th, td {
min-width: 80px;
text-align: left;
padding-right: 40px;
}

.sidenav-sub-item {
cursor: pointer;
padding: 8px;
transition: background 0.2s ease;
}
.sidenav-sub-item:hover {
background-color: #efefef;
}

document.addEventListener("DOMContentLoaded", function () {
// Step 1: Assign IDs to h2s (for scrolling)
const headings = document.querySelectorAll('.prose-rich-text h2');
headings.forEach(h2 => {
const text = h2.textContent.trim();
const slug = text.toLowerCase().replace(/\s+/g, '-').replace(/[^\w\-]+/g, '');
h2.id = slug;
});

// Step 2: Scroll when clicking links
document.querySelectorAll('.sidenav-sub-item[data-target]').forEach(link => {
link.addEventListener("click", function (e) {
e.preventDefault();
const targetId = this.getAttribute("data-target");
const targetElement = document.getElementById(targetId);
if (targetElement) {
const yOffset = -80;
const y = targetElement.getBoundingClientRect().top + window.pageYOffset + yOffset;
window.scrollTo({ top: y, behavior: 'smooth' });
}
});
});

// Step 3: Show correct sidebar section based on current URL and highlight the sidenav item
const currentPath = window.location.pathname

// Hide all sidebar-item-parent elements initially
document.querySelectorAll('.sidebar-item-parent').forEach(parent => {
parent.style.display = 'none';
});

const matchingParent = document.querySelector(`.sidebar-item-parent[data-target="${currentPath}"]`);
if (matchingParent) {
// Show the matching sidebar-item-parent
matchingParent.style.display = 'block';

// DTM - make all the siblings visible too
[...matchingParent.parentElement.children].forEach(parent => {
parent.style.display = 'block';
});

const subItemsWrapper = matchingParent.querySelector('.sidenav-sub-items-parent');
if (subItemsWrapper) {
subItemsWrapper.style.display = 'block';

// ✅ Step 4: Programmatically create sidenav-sub-items based on h2s
headings.forEach(h2 => {
const slug = h2.id;
const text = h2.textContent.trim();
const link = document.createElement('div');
link.className = 'sidenav-item sidenav-sub-item';
link.setAttribute('data-target', slug);
link.textContent = text;

link.addEventListener("click", function (e) {
e.preventDefault();
const targetElement = document.getElementById(slug);
if (targetElement) {
const yOffset = -80;
const y = targetElement.getBoundingClientRect().top + window.pageYOffset + yOffset;
window.scrollTo({ top: y, behavior: 'smooth' });
}
});

subItemsWrapper.appendChild(link);
});
} else {
console.warn('⚠️ Found matching parent but no .sidenav-sub-items-parent inside.');
}

// Highlight the sidenav item inside the matching parent
const sidenavItem = matchingParent.querySelector('.sidenav-item');
if (sidenavItem) {
sidenavItem.style.color = '#2d2af9';
sidenavItem.classList.add('active-sidenav-item');
} else {
console.warn(`⚠️ No .sidenav-item found inside matching parent.`);
}
} else {
console.warn(`🚫 No sidebar-item-parent found for: ${currentPath}`);
}
});