Date Functions
1. DATE_ADD
DATE_ADD(date, INTERVAL value unit): Adds a specified interval to a date.
INTERVAL:YEAR | MONTH | DAY
2. DATE_SUB
DATE_SUB(date, INTERVAL value unit): Subtracts a specified interval from a date.
INTERVAL:YEAR | MONTH | DAY
3. DATE_DIFF
DATE_DIFF(date1, date2, INTERVAL ): Calculates the difference between two dates.
INTERVAL:YEAR | MONTH | DAY
4. DATE_TRUNC
DATE_TRUNC(date, INTERVAL ): Truncates a date to a specified level.
INTERVAL:YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
5. EXTRACT
EXTRACT(PART FROM date): Retrieve specific parts from a date or timestamp
PART:YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
6. LAST_DAY
LAST_DAY(date): Returns the last day of the month for a given date.
7. DAYOFWEEK
DAYOFWEEK(date): Returns the day of the week (1-7, where 1 is Sunday).
8. DAYOFMONTH
DAYOFMONTH(date): Returns the day of the month (1-31)
9. DAYOFYEAR
DAYOFYEAR(date): Returns the day of the year (1-365).
10. MONTH, YEAR
MONTH(date): Returns the month of the year (1-12).YEAR(date): Returns the year.
11. CURRENT_DATE
CURRENT_DATE([time_zone]): Returns the current date
12. How to get the day of the month in BigQuery?
EXTRACT(day FROM date)
13. Explain the difference between DATE_TRUNC and EXTRACT in what they return and the functionality.
- Return type:
EXTRACTreturns a numeric value, whileDATE_TRUNCreturns a date or timestamp. - Functionality:
EXTRACTextracts a specific part of a date or timestamp, whileDATE_TRUNCtruncates the value to a specified precision.
14. When to use DATE_TRUNC or EXTRACT?
EXTRACTis used to extract specific parts of a date or timestamp.DATE_TRUNCis used to truncate a date or timestamp to a specified precision.
15. TIME_ADD
TIME_ADD(time, INTERVAL value unit): Adds a specified interval to a time.
INTERVAL:HOUR | MINUTE | SECOND
16. TIME_SUB
TIME_SUB(time, INTERVAL value unit): Subtracts a specified interval from a time.
INTERVAL:HOUR | MINUTE | SECOND
17. TIME_DIFF
TIME_DIFF(time1, time2, INTERVAL): Calculates the difference between two times.
HOUR | MINUTE | SECOND
18. HOUR, MINUTE, SECOND
HOUR(time): Returns the hour of the day (0-23).MINUTE(time): Returns the minute of the hour (0-59).SECOND(time): Returns the second of the minute (0-59).