BigQuery SQL Get First or Last Day of Month From Specify Date

In BigQuery, If you want get first day from a Date (or timestamp, datetime type) , for example, type “2017-05-24” and get “2017-05-01”, you can call UTC_USEC_TO_MONTH function in Legacy SQL, or DATE_TRUNC() in Standard SQL, In fact, BigQuery have a series function do these thing, Get First day, hour, week…..etc from a Date.

Legacy SQL

First Day

If you want get first day from timestamp:

SELECT USEC_TO_TIMESTAMP(UTC_USEC_TO_MONTH(NOW()))

1501322861000000 is 2017-07-29 10:07:41 UTC, and this query will output: 2017-07-01 00:00:00 UTC

You can input a Unix Timestamp (usec, int) into UTC_USEC_TO_MONTH function, and It can output a Unix Timestamp that is first day of month for you.

Some function like UTC_USEC_TO_MONTH:

  • UTC_USEC_TO_WEEK
  • UTC_USEC_TO_HOUR
  • UTC_USEC_TO_DAY
  • UTC_USEC_TO_YEAR

Last Day

If you need last day of month, you can get first day of month, and back 1 second, then you can get last day of month, for example:

SELECT DATE_ADD( UTC_USEC_TO_MONTH( DATE_ADD( NOW(), 1, 'month' ) ), -1, 'second' )

Get Current Timestamp -> Add a Month -> Next Month First Day -> Decrease 1 Second -> Get Current Month Last Day

Week, Hour, Day or Year? Similarly!

Standard SQL

In Standard SQL, It’s easy than Legacy SQL, you can use TIMESTAMP_TRUNC do same for TIMESTAMP type, or DATETIME_TRUNC, DATE_TRUNC function.

First Day

SELECT TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH)

Second param control what you need, is first day of month or first day of week…etc, and pay attention, the second param is not String!

You can use these at second param:

  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

If you want input a DATE type, you need use DATE_TRUNC, DATETIME type either.

Last Day

Like Legacy SQL, just change some Function for Standard SQL use, for example:

SELECT DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)

Because TIMESTAMP_ADD doesn’t support MONTH, so you need convert TIMESTAMP to DATETIME or DATE.

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *