Datetime Functions
CURRENT_DATETIME
CURRENT_DATETIME([time_zone])
Description
Return the current date and time.
Input
timezone
(optional,STRING
): The default timezone is UTC. You can optionally specify a time zone.
Return Data Type
DATETIME
Example
Result: 2021-04-26T18:24:38.702355
Result: 2021-04-26T14:24:38.702355
DATETIME
DATETIME(datetime_expression)
Description
Convert a string, timestamp, or date expression to a datetime variable in a "yyyy-mm-dd hh-mm-ss" format. For the timestamp inputs, you can optionally specify a time zone.
Input
a STRING
, TIMESTAMP
object or column
Return Data Type
DATETIME
Example
Result: 2020-12-01T14:20:32
Result: 2020-11-30T23:20:32
EXTRACT
EXTRACT(date_part FROM datetime_expression)
Description
Return a part of the datetime object.
Input
date_part
: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, ISOYEAR, DATE, or TIME (See more details)datetime
: aDATETIME
object or column
Return Data Type
INTEGER
, except in the following cases:
If
date_part
isDATE
, returns aDATE
object.If
date_part
isTIME
, returns aTIME
object.
Example
Result: 1
Result: 3 (1=Sunday, 2=Monday, 3=Tuesday....)
DATETIME_ADD
DATETIME_ADD(datetime, INTERVAL integer datetime_part)
Description
Add a specified time interval to the given datetime.
Input
date_part
: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, ISOYEAR, DATE, or TIME (See more details)integer
: the number ofdate_part
datetime
: aDATETIME
object or column
Return Data Type
DATETIME
Example
Result: 2020-12-25T15:40:00
DATETIME_SUB
DATETIME_SUB(datetime, INTERVAL integer datetime_part)
Description
Subtract a specified time interval to a given datetime.
Input
date_part
: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR (See more details)integer
: the number ofdate_part
datetime
: aDATETIME
object or column
Return Data Type
DATETIME
Example
Result: 2020-12-25T15:20:00
DATETIME_DIFF
DATETIME_DIFF(datetime1, datetime2, date_part)
Description
Return the difference between two datetimes (dateime1-datetime2). The difference is expressed in units of date_part
.
Input
datetime1
: aDATETIME
object or columndatetime2
: aDATETIME
object or columndate_part
: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK(begins on Sunday), ISOWEEK(begins on Monday), MONTH, QUARTER, YEAR, or ISOYEAR (See more details)
Return Data Type
INTEGER
Example
Result: 5
DATETIME_TRUNC
DATETIME_TRUNC(datetime, date_part)
Description
Truncate a specific datetime to the start of a given unit of duration and returns a new datetime. The unit of duration is defined by date_part
.
Input
datetime
: aDATETIME
object or columndate_part
: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, WEEK(), ISOWEEK, MONTH, QUARTER, YEAR, or ISOYEAR (See more details)
Return Data Type
DATETIME
Example
Result: 2021-04-10T15:00:00
Result: 2021-04-01T00:00:00
FORMAT_DATETIME
FORMAT_DATETIME(format, datetime)
Description
Transform a datetime variable into a string variable in a specific format.
Input
format
: aSTRING
expression. To see supported format elements, visit here.datetime
: aDATETIME
object or column.
Return Data Type
STRING
Example
Result: Dec 2020
PARSE_DATETIME
PARSE_DATETIME(format, datetime_expression)
Description
Convert a string to a datetime variable according to the specified format.
Input
format
: aSTRING
expression. To see supported format elements, visit here.datetime_expression
: aSTRING
object or column.
Return Data Type
DATETIME
Example
Result: 2020-08-30T14:23:38
LAST_DAY
LAST_DAY(datetime, [date_part])
Description
Extract the last day of a given unit of duration for a given date. The unit of duration is defined by date_part. This is commonly used to return the last day of the month.
datetime
: aDATETIME
object or columndate_part
: can be one of the following: YEAR, QUARTER, MONTH, WEEK, WEEK(), ISOWEEK, or ISOYEAR (See more details)
Return Data Type
DATETIME
Example
Result: 2021-03-31
Result: 2021-03-27
Details about date_part
Supported Format Elements
Last updated