Extract Month and Year from timestamp in Bigquery

bigquery last day of month
bigquery current date - 1
format_datetime bigquery
bigquery string to date
insert timestamp bigquery
substring bigquery
bigquery get day of-week
bigquery get todays date

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;

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

Date Functions in Standard SQL | BigQuery, 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  MONTH; QUARTER; YEAR; Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the result day is the last day of the new month. Return Data Type. DATE. Example


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

Timestamp Functions in Standard SQL | BigQuery, EXTRACT Day / Month / Year from Date or Timestamp Column in Google Bigquery. In the following example, EXTRACT returns a value  MONTH; QUARTER; YEAR; ISOYEAR: Truncates timestamp_expression to the preceding ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year. TIMESTAMP_TRUNC function supports an optional time_zone parameter.


You can try:

SELECT CONCAT(CAST(EXTRACT(YEAR FROM DATE (OrderDate)) AS STRING),"-",CAST(EXTRACT(MONTH FROM DATE (OrderDate)) AS STRING)) AS yyyy_mm

Extract Month and Year from timestamp in Bigquery, This meant a relatively simple SQL query in Google BigQuery. Extract a date part DATETIME(year, month, day, hour, minute, second) This meant a relatively simple SQL query in Google BigQuery. Once again I found myself googling how to extract the year from a timestamp, and scrolling through the documentation to find the correct function and realised that I needed to write this down somewhere.


I don't understand your comment "In bigquery, we use oracle like SQL", so I'm just going to assume you're using normal Standard SQL in BigQuery.

You're SQL is a little weird, and doesn't make a lot of sense, but here's a working example on a public dataset. You'll need to change the timestamp column to match yours, and I've no idea what total is, so I've just made it 1:

SELECT
  SUM(CASE
      WHEN EXTRACT(month  FROM  community_owned_date ) = 1 AND EXTRACT(year  FROM  community_owned_date ) = 2017 THEN 1
      ELSE 0
    END ) AS January,
  SUM(CASE
      WHEN EXTRACT(month  FROM  community_owned_date ) = 2 AND EXTRACT(year  FROM  community_owned_date ) = 2017 THEN 1
      ELSE 0
    END ) AS February
FROM
  `bigquery-public-data.stackoverflow.posts_answers`

EXTRACT Day / Month / Year from Date or Timestamp Column in , Get the date and/or time right now: select current_timestamp; -- date and time Find rows between two absolute timestamps: select * from table  BigQuery provides multiple functions to convert timestamps / dates / datetimes to a different timezone: DATE(timestamp_expression, timezone) TIME(timestamp, timezone) DATETIME(timestamp_expression, timezone) According to the docu the timezone can be provided as UTC-offset (e.g. +02:00) or timezone name (e.g. Europe/Berlin).


I guess you're looking for this

SELECT
  FORMAT_DATETIME('%B', DATETIME '2008-12-25 15:30:00')
  AS formatted;

So DATETIME to cast your string to a datetime format and format_datetime() to get the name of the month

Google BigQuery SQL Dates and Times Cheat Sheet, Using SQL date functions like EXRACT() and FORMAT_DATE() to slice your data by day of the Duration: 9:15 Posted: May 21, 2019 BigQuery supports the following DATETIME functions. Returns the current time as a DATETIME object. This function supports an optional timezone parameter. See Timezone definitions for information on how to specify a time zone. DATETIME (year, month, day, hour, minute, second) 2. DATETIME (date_expression, time_expression) 3.


How to Query Date and Time in BigQuery, It turns out migrating from Redshift to Bigquery was not “as easy as 123” and A TIMESTAMP is just a DATE with an additional two levels of precision: grabs the month and year from the date so you can get a monthly view. Casting between date and timestamp types. BigQuery supports casting between date and timestamp types as follows: CAST(date_expression AS TIMESTAMP) CAST(timestamp_expression AS DATE) Casting from a date to a timestamp interprets date_expression as of midnight (start of the day) in the default time zone, UTC. Casting from a timestamp to date effectively truncates the timestamp as of the default time zone.


Aggregate by day, week and month in BigQuery, By default, queries in Google BigQuery run on Legacy SQL. format, select the necessary part (day, month, or year), or shift the date by a certain interval. YEAR(timestamp), EXTRACT(YEAR FROM timestamp), Returns the  Extract Month and Year from date in Bigquery. You can just use the FORMAT_DATETIME function: SELECT FORMAT_DATETIME("%B, %Y", DATETIME "2018-05-09 15:30:00") FROM yourTable; This should print May, 2018.


Formatting and dealing with dates in different SQL dialects, Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument. For example, if unix_timestamp occurs in 2010, the function returns 1274259481071200 , the microsecond representation of 2010-01-01 00:00 .