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.