CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sql
kshoe94
kshoe94

Reputation: 429

date comparison in BigQuery

I am trying to filter out certain dates in my query. The line of code I am using in my query is as follows:

(date(date_add(date_time,-1,'year')) < date(date_add(current_date(),-1,'year')) ) OR year(date_time) = 2017)

The idea is to get all data YTD in 2017, as well as the data from 2016 that corresponds to those same days. So if I were to run a proper version of what I want, it would return all data YTD, as well as all 2016 data up until Nov. 16, 2016.

However, whenever I run the query with this in the WHERE clause, my query still returns dates that do not fit within my described parameters. I may be missing something completely obvious but this has been driving me nuts. Is there a particular way to filter dates that I'm missing?

Edit: Here is some of my data 

[date]       [today]    [uniques]    
2016-01-01  2017-11-16  363878   
2016-01-02  2017-11-16  383813   
2016-01-03  2017-11-16  392579   
2016-01-04  2017-11-16  709367   
..
..   
2016-11-15  2017-11-16  841850   
2016-11-16  2017-11-16  847831   
2016-11-17  2017-11-16  797610   
2016-11-18  2017-11-16  187158   
2016-11-19  2017-11-16  521100   
..   
2017-11-12  2017-11-16  297604   
2017-11-13  2017-11-16  527858   
2017-11-14  2017-11-16  474051   
2017-11-15  2017-11-16  569686

I added the today row for myself to make sure there wasn't a difference in date formatting. With this data, the bottom 3 rows in the middle section should not be returned, but they are. Everything else should fit the parameters I've set and get returned.

Appreciate any and all help. Thanks!

Upvotes: 2

Views: 3314

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL: 

#standardSQL
SELECT date_time, uniques 
FROM `project.dataset.yourTable`
WHERE (PARSE_DATE('%Y-%m-%d', date_time) < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) 
OR EXTRACT(YEAR FROM PARSE_DATE('%Y-%m-%d', date_time)) = 2017

You can test / play with it using dummy data from your question as it is below 

#standardSQL
WITH `project.dataset.yourTable` AS (
  SELECT '2016-01-01' date_time, 363878 uniques UNION ALL
  SELECT '2016-01-02', 383813 UNION ALL
  SELECT '2016-01-03', 392579 UNION ALL
  SELECT '2016-01-04', 709367 UNION ALL

  SELECT '2016-11-15', 841850 UNION ALL
  SELECT '2016-11-16', 847831 UNION ALL
  SELECT '2016-11-17', 797610 UNION ALL
  SELECT '2016-11-18', 187158 UNION ALL
  SELECT '2016-11-19', 521100 UNION ALL

  SELECT '2017-11-12', 297604 UNION ALL
  SELECT '2017-11-13', 527858 UNION ALL
  SELECT '2017-11-14', 474051 UNION ALL
  SELECT '2017-11-15', 569686 
)
SELECT date_time, uniques 
FROM `project.dataset.yourTable`
WHERE (PARSE_DATE('%Y-%m-%d', date_time) < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) 
OR EXTRACT(YEAR FROM PARSE_DATE('%Y-%m-%d', date_time)) = 2017

It is highly recommended to use BigQuery Standard SQL - so if you are still using Legacy SQL - consider Migrating to Standard SQL

Meantime if you have your code in legacy sql (which is look like to me) - you can use below 

#legacySQL
SELECT date_time, uniques 
FROM [project:dataset.yourTable]
WHERE (DATE(date_time) < DATE(DATE_ADD(CURRENT_DATE(), -1, 'year'))) 
OR YEAR(date_time) = 2017

You can test / play with dummy data as below 

#legacySQL
SELECT date_time, uniques 
FROM --[project:dataset.yourTable]
  (SELECT '2016-01-01' date_time, 363878 uniques),
  (SELECT '2016-01-02' date_time, 383813 uniques),
  (SELECT '2016-01-03' date_time, 392579 uniques),
  (SELECT '2016-01-04' date_time, 709367 uniques),

  (SELECT '2016-11-15' date_time, 841850 uniques),
  (SELECT '2016-11-16' date_time, 847831 uniques),
  (SELECT '2016-11-17' date_time, 797610 uniques),
  (SELECT '2016-11-18' date_time, 187158 uniques),
  (SELECT '2016-11-19' date_time, 521100 uniques),

  (SELECT '2017-11-12' date_time, 297604 uniques),
  (SELECT '2017-11-13' date_time, 527858 uniques),
  (SELECT '2017-11-14' date_time, 474051 uniques),
  (SELECT '2017-11-15' date_time, 569686 uniques)
WHERE (DATE(date_time) < DATE(DATE_ADD(CURRENT_DATE(), -1, 'year'))) 
OR YEAR(date_time) = 2017

Upvotes: 2

Related Questions