Extract Month and Year from timestamp in Bigquery

Question

I would like to extract month and year from Timestamp column(YYYYMMDD HHMMSS) and if month is 1,it should create a column called 'January' and month is 2,it should create column called 'February'.

Here is the query i have tried but no luck.

SELECT
  SUM(case when extract(month() from timestamp) = 1 and extract(year() from timestamp) = '2018' then total else 0 end ) as January ,
  SUM(case when extract(month() from timestamp) = 2 and extract(year() from timestamp) = '2018' then total else 0 end ) as February, and so on until December..

FROM x;

Answer 1

Answering the title of the question as probably others will end up here like me looking for a way to create a YYYYMM year-month from a BigQuery timestamp.

This is the code I ended up with in standard SQL:

CONCAT(CAST(EXTRACT(YEAR from timestamp) as string), LPAD(CAST(EXTRACT(MONTH from timestamp) as string),2,'0') ) as yearmonth

Answer 2

Below is for BigQuery StandardSQL

#standardsQL
CREATE TEMP FUNCTION monthValue(ts TIMESTAMP, m INT64, y INT64, value INT64) AS (
  IF(EXTRACT(MONTH FROM ts) = m AND EXTRACT(YEAR FROM ts) = y, value, 0) 
);
SELECT
  SUM(monthValue(timestamp, 1, 2018, total)) AS January,
  SUM(monthValue(timestamp, 2, 2018, total)) AS February,
  SUM(monthValue(timestamp, 3, 2018, total)) AS March,
  SUM(monthValue(timestamp, 4, 2018, total)) AS April,
  SUM(monthValue(timestamp, 5, 2018, total)) AS May,
  SUM(monthValue(timestamp, 6, 2018, total)) AS June,
  SUM(monthValue(timestamp, 7, 2018, total)) AS July,
  SUM(monthValue(timestamp, 8, 2018, total)) AS August,
  SUM(monthValue(timestamp, 9, 2018, total)) AS September,
  SUM(monthValue(timestamp, 10, 2018, total)) AS October,
  SUM(monthValue(timestamp, 11, 2018, total)) AS November,
  SUM(monthValue(timestamp, 12, 2018, total)) AS December
FROM
  `yourproject.yourdataset.yourtable`

Can we do this in Legacy SQL??

for Legacy SQL see below

#legacySQL
SELECT
  SUM(IF(MONTH(timestamp) = 1 AND YEAR(timestamp) = 2018, total, 0)) AS January,
  SUM(IF(MONTH(timestamp) = 2 AND YEAR(timestamp) = 2018, total, 0)) AS February,
  SUM(IF(MONTH(timestamp) = 3 AND YEAR(timestamp) = 2018, total, 0)) AS March,
  SUM(IF(MONTH(timestamp) = 4 AND YEAR(timestamp) = 2018, total, 0)) AS April,
  SUM(IF(MONTH(timestamp) = 5 AND YEAR(timestamp) = 2018, total, 0)) AS May,
  SUM(IF(MONTH(timestamp) = 6 AND YEAR(timestamp) = 2018, total, 0)) AS June,
  SUM(IF(MONTH(timestamp) = 7 AND YEAR(timestamp) = 2018, total, 0)) AS July,
  SUM(IF(MONTH(timestamp) = 8 AND YEAR(timestamp) = 2018, total, 0)) AS August,
  SUM(IF(MONTH(timestamp) = 9 AND YEAR(timestamp) = 2018, total, 0)) AS September,
  SUM(IF(MONTH(timestamp) = 10 AND YEAR(timestamp) = 2018, total, 0)) AS October,
  SUM(IF(MONTH(timestamp) = 11 AND YEAR(timestamp) = 2018, total, 0)) AS November,
  SUM(IF(MONTH(timestamp) = 12 AND YEAR(timestamp) = 2018, total, 0)) AS December
FROM [yourproject:yourdataset.yourtable]  

Note: it is quite recommended by BigQuery Team to migrate to Standard SQL