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

CURRENT_DATETIME()

Result: 2021-04-26T18:24:38.702355

CURRENT_DATETIME("America/New_York")

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

DATETIME("2020-12-01 14:20:32")

Result: 2020-12-01T14:20:32

DATETIME(TIMESTAMP("2020-12-01 14:20:32+07"), "America/Los_Angeles")

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: a DATETIME object or column

Return Data Type

INTEGER, except in the following cases:

• If date_part is DATE, returns a DATE object.

• If date_part is TIME, returns a TIME object.

Example

EXTRACT(DAY FROM DATETIME("2020-12-01 14:20:32"))

Result: 1

EXTRACT(DAYOFWEEK FROM DATETIME("2020-12-01 14:20:32"))

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 of date_part

• datetime: a DATETIME object or column

Return Data Type

DATETIME

Example

DATETIME_ADD(DATETIME("2020-12-25 15:30:00"), INTERVAL 10 MINUTE)

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 of date_part

• datetime: a DATETIME object or column

Return Data Type

DATETIME

Example

DATETIME_SUB(DATETIME("2020-12-25 15:30:00"), INTERVAL 10 MINUTE)

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: a DATETIME object or column

• datetime2: a DATETIME object or column

• date_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

DATETIME_DIFF(DATETIME("2020-12-25 10:20:00"), DATETIME("2020-12-20 15:30:00"), DAY)

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: a DATETIME object or column

• date_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

DATETIME_TRUNC(DATETIME("2021-04-10 15:30:00"), HOUR)

Result: 2021-04-10T15:00:00

DATETIME_TRUNC(DATETIME("2021-04-10 15:30:00"), MONTH)

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: a STRING expression. To see supported format elements, visit here.

• datetime: a DATETIME object or column.

Return Data Type

STRING

Example

FORMAT_DATETIME("%b %Y", DATETIME("2020-12-25 15:30:00"))

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: a STRING expression. To see supported format elements, visit here.

• datetime_expression: a STRING object or column.

Return Data Type

DATETIME

Example

PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2020 2:23:38 PM')

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: a DATETIME object or column

• date_part: can be one of the following: YEAR, QUARTER, MONTH, WEEK, WEEK(), ISOWEEK, or ISOYEAR (See more details)

Return Data Type

DATETIME

Example

LAST_DAY(DATE('2021-03-25'), MONTH)

Result: 2021-03-31

LAST_DAY(DATE('2021-03-25'), WEEK)

Result: 2021-03-27

Details about date_part

Supported Format Elements