The following function returns current date based on the UTC timezone not local timezone. The date is returned in YYYY-MM-DD. This function is supported in both legacy SQL and standard SQL.
SELECT CURRENT_DATE()
The following function returns current time based on UTC timezone not local timezone. The time is returned in HH:MM:SS format when the query is run in legacy SQL and it is returned with milliseconds when run in standard SQL.
SELECT CURRENT_TIME()
The following function returns the current date time at seconds level in UTC timezone.
SELECT CURRENT_TIMESTAMP()
The DATE function extracts the date from given TIMESTAMP value. The following query returns the current date based on UTC timezone.
SELECT DATE(CURRENT_TIMESTAMP())
The DATE_ADD function in legacy SQL will add specified time interval to the TIMESTAMP passed to it. The following query returns next day's date based on UTC timezone. The interval can be a positive or negative value. The following are the values that can be passed for the interval unit.
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
- SECOND
#legacySQL
SELECT DATE_ADD(CURRENT_TIMESTAMP(),1,"DAY")
The following query is the standard SQL equivalent for the legacy SQL query given above but the standard SQL returns the value as next day's date time at millisecond level. The interval value can be positive or negative.
#standardSQL
SELECT DATETIME_ADD(CURRENT_DATETIME(),INTERVAL 1 DAY)
The following are the list of values that can passed as interval unit.
- YEAR
- QUARTER
- MONTH
- WEEK
- DAY
- HOUR
- MINUTE
- SECOND
- MILLISECOND
- MICROSECOND
The DATEDIFF legacy SQL function returns the number of days difference between two dates. The following query returns 5 as output.
#legacySQL
SELECT DATEDIFF('2017-12-01','2017-11-26')
The following is the standard SQL equivalent for the above query. The interval unit can be any of the values that was specified in DATEIME_ADD function specified earlier.
#standardSQL
SELECT DATE_DIFF('2017-12-01','2017-11-26', DAY)