In BigQuery, if you want to get the first day from a date (or a timestamp or datetime value), for example turning “2017-05-24” into “2017-05-01”, you can call the UTC_USEC_TO_MONTH function in Legacy SQL, or DATE_TRUNC() in Standard SQL. In fact, BigQuery has a whole series of functions that do these things, getting the first day, hour, week, and so on from a date.
Legacy SQL
First DayIf you want to get the first day from a 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 the UTC_USEC_TO_MONTH function, and it will output a Unix timestamp for the first day of the month.
Other functions like UTC_USEC_TO_MONTH:
- UTC_USEC_TO_WEEK
- UTC_USEC_TO_HOUR
- UTC_USEC_TO_DAY
- UTC_USEC_TO_YEAR
If you need the last day of the month, you can get the first day of the month and then go back 1 second. For example:
SELECT DATE_ADD( UTC_USEC_TO_MONTH( DATE_ADD( NOW(), 1, 'month' ) ), -1, 'second' )
Get the current timestamp -> add a month -> first day of next month -> subtract 1 second -> last day of the current month
Week, hour, day, or year? It works the same way!
Standard SQL
In Standard SQL, it's easier than in Legacy SQL. You can use TIMESTAMP_TRUNC for TIMESTAMP values, or DATETIME_TRUNC and DATE_TRUNC for the other types.First Day
SELECT TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH)
The second parameter controls what you get, whether it’s the first day of the month, the first day of the week, and so on. Pay attention: the second parameter is not a string!
You can use these for the second parameter:
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
Last Day
Just like Legacy SQL, you only need to change some of the functions to the Standard SQL versions. 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, you need to convert the TIMESTAMP to a DATETIME or DATE first.