DB

How to get YYYYMM format from date column in BigQuery

Share this for your friends.

How to get YYYYMM format from date column in BigQuery

How can I get YYYYMM format from date column in BigQuery ?

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

We can use SQL in BigQuery. So it is familiar to RDB user.


And BQ has date type.

So you may want to extract YYYYMM year-month data from date column.

Then how can we get YYYYMM format data ?

So today I introduce about "How to get YYYYMM format from date column in BigQuery".

Author


Mid-carieer engineer (AI, system). Good at Python and SQL.

Advantage to read

You can understand "How to get YYYYMM format from date column in BigQuery". Then you don't have to concern about converting date record.


How to get YYYYMM format from date column in BigQuery

How

In order to to get YYYYMM format from date column in BigQuery, there are some solution.

Methods to extract part of date

  • SUBSTR
  • EXTRACT
  • FORMAT_TIMESTAMP


I will introduce each of them.



SUBSTR

SUBSTR() if function that extracts part of text.

So we convert date data to text(string) data and use this function.

SQL

with t as(
    SELECT DATE("2021-12-08") as date_col
)
SELECT
CONCAT(SUBSTR(CAST(date_col as string),0,4), SUBSTR(CAST(date_col as string),6,2))
FROM t


Result

202112

In 2021-12-08, take first 4 characters and take 2 characters from 6th character. Then concatenate them to YYYYMM format.




EXTRACT

EXTRACT is function for extracting.

We use it like EXTRACT([part name] FROM [date column]).

SQL

with t as(
    SELECT DATE("2021-02-08") as date_col
)
SELECT
CONCAT(
    CAST(EXTRACT(YEAR FROM date_col) as string),
    LPAD(CAST(EXTRACT(MONTH FROM date_col) as string), 2, "0")
)
FROM t


Result

202102


Get year value by EXTRACT(YEAR FROM ...), and get month value by EXTRACT(MONTH FROM ...).

It returns int type. So in order to get 2 digits month value, we use LPAD.




FORMAT_TIMESTAMP

FORMAT_TIMESTAMP is easiest.

We can use it like FORMAT_TIMESTAMP([format string]], [date column])

SQL

with t as(
    SELECT DATE("2021-02-08") as date_col
)
SELECT
FORMAT_TIMESTAMP("%Y%m", date_col)
FROM t


Result

202102


"%Y%m" means YYYYMM format. So it convert date type data to specific format text.




Conclusion

Today I explained about "How to get YYYYMM format from date column in BigQuery".

In order to get YYYYMM format, we have these solutions below.

Methods to extract part of date

  • SUBSTR
  • EXTRACT
  • FORMAT_TIMESTAMP


I recommend FORMAT_TIMESTAMP.

Like FORMAT_TIMESTAMP("%Y%m", date_col), we can set format easily.


In order to get YYYYMM format, FORMAT_TIMESTAMP may be suitable.
To get MM value, EXTRACT(MONTH FROM ...) will be better.



There are some other articles about BigQuey.

If you interested in them, please read them.


Share this for your friends.

If you felt this article is useful, please share.

にほんブログ村 IT技術ブログへ

-DB
-

© 2023 ITips