Datetime Functions

This page lists DATETIME functions that are available in the GSQL query language. Examples of DATETIME functions are converting to different locale formats, extracting days or months, calculating the difference between two values and returning the current time.

Every function on this page either takes a DATETIME object as its argument or returns a DATETIME object.

For type conversion functions, such as converting a DATETIME to a STRING, please see the Type Conversion Functions page.

datetime_add()

Syntax

datetime_add( date, INTERVAL int_value time_unit )

Description

Calculates a new DATETIME from a specified datepart multiplied by a specified amount, added to a specified DATETIME.

INTERVAL is a keyword that must be entered exactly as shown. time_unit is one of the keywords YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

Return type

DATETIME

Parameters

Parameter Description Data type

date

The DATETIME to add to.

DATETIME

int_value

An integer value

INT

Example

datetime_add(to_datetime("1970-01-01 00:00:00"), INTERVAL 1 MONTH)
    -> 1970-02-01 00:00:00

datetime_diff()

Syntax

datetime_diff( date1, date2 )

Description

Calculates the difference in seconds between two DATETIME values.

Return type

INT

Parameters

Parameter Description Data type

date1

A DATETIME value

DATETIME

date2

A DATETIME value

DATETIME

Example

datetime_diff(to_datetime("2020-01-01 00:00:00"), to_datetime("2020-02-03 04:13:12"))
    -> -2866392

datetime_format()

Syntax

datetime_format(date[, str])

Description

Prints a DATETIME value in a specific format indicated by a provided string.

Return type

STRING

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

str

A string pattern expressing the format to print date in. Use the following specifiers in your string to insert the corresponding value in the output.

  • %Y - year

  • %m - month

  • %d - day of month

  • %H - hour

  • %M - minute

  • %S - second

The default value for this parameter is "%Y-%m-%d %H:%M:%S"

DATETIME

Example

(datetime_format("2020-01-02 05:30:12"), "hi, it's %Y-%m-%d")
    -> "hi, it's 2020-01-02"

datetime_sub( )

Syntax

datetime_sub(date, INTERVAL int_value time_unit)

Description

Calculates a new DATETIME from a specified datepart multiplied by a specified amount, subtracted from a specified DATETIME.

INTERVAL is a keyword that must be entered exactly as shown. time_unit is one of the keywords YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

Return type

DATETIME

Parameters

Parameter Description Data type

date

The DATETIME to subtract from

DATETIME

int_value

An integer value

INT

Example

datetime_sub(to_datetime("1970-02-01 00:00:00"), INTERVAL 1 MONTH) -> 1970-01-01 00:00:00

datetime_to_epoch()

Syntax

datetime_to_epoch( date )

Description

Converts a DATETIME value to epoch time.

Return type

INT

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

datetime_to_epoch(to_datetime("1970-01-01 00:01:00")) -> 60

day()

Syntax

day( date )

Description

Returns the day of the month of a DATETIME value.

Return type

INT

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

day(to_datetime("1973-01-05 00:00:00")) -> 5

epoch_to_datetime()

Syntax

epoch_to_datetime(int_value)

Description

Converts an epoch time value to a DATETIME value.

Return type

DATETIME

Parameters

Parameter Description Data type

int_value

An epoch time value

INT

Example

epoch_to_datetime(1) -> 1970-01-01 00:00:01

hour()

Syntax

hour(date)

Description

Extracts the hour of the day from a DATETIME value.

Return type

INT

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

hour(to_datetime("1980-01-01 15:01:02")) -> 15

minute()

Syntax

minute(date)

Description

Extracts the minute of the hour from a DATETIME value.

Return type

INT

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

minute(to_datetime("1980-02-05 03:04:05")) -> 4

month()

Syntax

month(date)

Description

Extracts the month of the year from a DATETIME value.

Return type

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

month(to_datetime("1980-02-05 03:04:05")) -> 2

now()

Syntax

now()

Description

Returns the current UTC time in DATETIME.

Return type

DATETIME

Parameters

None.

Example

now() => "2021-11-13 00:32:16"

second()

Syntax

second(date)

Description

Extracts the seconds value from a DATETIME value.

Return type

INT

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

second(to_datetime("1980-02-05 03:04:05")) -> 5

year()

Syntax

year(date)

Description

Extracts the year from a DATETIME value.

Return type

INT

Parameters

Parameter Description Data type

date

A DATETIME value

DATETIME

Example

year(to_datetime("1980-02-05 03:04:05")) -> 1980