CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Ayush Sood
Ayush Sood

Reputation: 111

How to run a query to get results of last 12 months ( BigQuery)

I want to get records for last 12 months for a particular ID in Big Query table. I have extracted month and year as separate columns from the original timestamp column. I tried using date_add() and date_diff() but i am getting some errors. My Dataset is like:

Month Year MonthName    ID
8     2018   August      1
9     2018   September   2
9     2017   September   3

How can i achieve this?

Upvotes: 6

Views: 24455

Answers (4)

Andrés Cervantes
Andrés Cervantes

Reputation: 1

If you're looking for the last 12 full-months:

SELECT
  DATE_SUB((DATE_TRUNC(CURRENT_DATE(), month)), INTERVAL 1 month),
  DATE_SUB((DATE_TRUNC(CURRENT_DATE(), month)), INTERVAL 13 month)

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

I have extracted month and year as separate columns from the original timestamp column

I recommend doing 12 months filtering based on your original timestamp column.
Assuming it is named ts - below is for BigQuery Standard SQL 

WHERE DATE(ts) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND CURRENT_DATE()

for example 

#standardSQL
SELECT ts, id
FROM `project.dataset.table`
WHERE id = 1
AND DATE(ts) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND CURRENT_DATE()

Upvotes: 16

Royan Dawud Aldian
Royan Dawud Aldian

Reputation: 1

timestamp is assumed as standard timestamp naming for your BQ table as time identifier field. So you can filter like below to get last 12 month data only: 

WHERE DATE(TIMESTAMP_MILLIS(timestamp)) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) and CURRENT_DATE()

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

One method is to convert the year/month into months since some date. Then do the same with the current date:

where year * 12 + month >= extract(year from current_date) * 12 + extract(month from current_date) - 12

This assumes you have no future data. The same idea holds for future dates (using < rather than >=).

Upvotes: 0

Related Questions