Datetime Functions

Datetime Value Functions

A datetime value function yields or extracts a temporal instant value.

date()

Returns a value of type DATE.

When called without a parameter, date() returns the current session date (or the server date if no session timezone is set). It is equivalent to CURRENT_DATE.

RETURN date(), CURRENT_DATE

Result:

The parameter should match one of the supported formats:

FOR value IN [
  date("1993-05-09"),
  date("19930509"),
  date({year: 1993, month: 5, day: 9}),
  date({year: 1993, month: 5}),
  date({year: 1993})
]
RETURN value  

Result:

local_datetime()

Returns a value of type LOCAL DATETIME.

When called without a parameter, local_datetime() returns the current session datetime (or the server datetime if no session timezone is set). It is equivalent to LOCAL_TIMESTAMP.

RETURN local_datetime(), LOCAL_TIMESTAMP

Result:

The parameter should match one of the supported formats:

FOR value IN [
  local_datetime("1993-05-09T03:02:11.70"),
  local_datetime("1993-05-09 03:02:11.70"),
  local_datetime("19930509T030211"),
  local_datetime("19930509 030211"),
  local_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, millisecond: 70}),
  local_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, microsecond: 70}),
  local_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, nanosecond: 70}),
  local_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11}),
  local_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2}),
  local_datetime({year: 1993, month: 5, day: 9, hour: 3})
]
RETURN value  

Result:

local_time()

Returns a value of type LOCAL TIME.

When called without a parameter, local_time() returns the current session time (or the server time if no session timezone is set).

RETURN local_time()

Result:

The parameter should match one of the supported formats:

FOR value IN [
  local_time("03:02:11.70"),
  local_time("030211.70"),
  local_time("03:02:11"),
  local_time("030211"),
  local_time({hour: 3, minute: 2, second: 11, millisecond: 70}),
  local_time({hour: 3, minute: 2, second: 11, microsecond: 70}),
  local_time({hour: 3, minute: 2, second: 11, nanosecond: 70}),
  local_time({hour: 3, minute: 2, second: 11}),
  local_time({hour: 3, minute: 2}),
  local_time({hour: 3})
]
RETURN value  

Result:

now()

Returns the current session datetime (or the server datetime if no session timezone is set). It is equivalent to local_datetime() and LOCAL_TIMESTAMP.

RETURN now()

Result:

zoned_datetime()

Returns a value of type ZONED DATETIME.

When called without a parameter, zoned_datetime() returns the current session datetime (or the server datetime if no session timezone is set). It is equivalent to CURRENT_TIMESTAMP.

RETURN zoned_datetime(), CURRENT_TIMESTAMP

Result:

The parameter should match one of the supported formats:

FOR value IN [
  zoned_datetime("1993-05-09T03:02:11.70-0600"),
  zoned_datetime("1993-05-09 03:02:11.70-06:00"),
  zoned_datetime("19930509T030211-06:00"),
  zoned_datetime("19930509 030211-0600"),
  zoned_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, millisecond: 70, timezone: -0600}),
  zoned_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, microsecond: 70, timezone: -0600}),
  zoned_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, nanosecond: 70, timezone: -0600}),
  zoned_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, second: 11, timezone: -0600}),
  zoned_datetime({year: 1993, month: 5, day: 9, hour: 3, minute: 2, timezone: -0600}),
  zoned_datetime({year: 1993, month: 5, day: 9, hour: 3, timezone: -0600})
]
RETURN value  

Result:

zoned_time()

Returns a value of type ZONED TIME.

When called without a parameter, zoned_time() returns the current session time (or the server time if no session timezone is set). It is equivalent to CURRENT_TIME.

RETURN zoned_time(), CURRENT_TIME

Result:

The parameter should match one of the supported formats:

FOR value IN [
  zoned_time("03:02:11.70-06:00"),
  zoned_time("030211.70-0600"),
  zoned_time("03:02:11-06:00"),
  zoned_time("030211-0600"),
  zoned_time({hour: 3, minute: 2, second: 11, millisecond: 70, timezone: "-0600"}),
  zoned_time({hour: 3, minute: 2, second: 11, microsecond: 70, timezone: "-0600"}),
  zoned_time({hour: 3, minute: 2, second: 11, nanosecond: 70, timezone: "-0600"}),
  zoned_time({hour: 3, minute: 2, second: 11, timezone: "-0600"}),
  zoned_time({hour: 3, minute: 2, timezone: "-0600"}),
  zoned_time({hour: 3, timezone: "-0600"})
]
RETURN value  

Result:

Other Temporal Functions

dateAdd()

Adds a specified time interval to a given date.

RETURN dateAdd("1970-1-1", -1, "hour") as newTime

Result:

RETURN dateAdd("1970-1-1", 10, "year")

Result:

dateDiff()

Computes the difference between two dates (time1 - time2) and returns the result as a specified unit of time.

RETURN dateDiff("1970-01-01 10:00:00", "1970-01-01 12:00:20", "minute") as diff

Result:

dateFormat()

Prints a given date in the specific format.

Format codes:

RETURN dateFormat("2010/9/25 6:12:30","%A %e %B, %G") as newFormat

Result:

dayOfWeek()

Returns a number (from 1 to 7, where 1 = Sunday and 7 = Saturaday) representing the day of the week for a given date.

RETURN dayOfWeek("2024-12-5")

Result:

Datetime String Format

Date string

• Format: yyyy-mm-dd or yyyymmdd
• Range: -9999-12-31 to 9999-12-31

Time string

• Format: hh:mm:ss[.fraction] or hhmmss[.fraction]
• Range: 00:00:00.000000000 to 23:59:59.999999999

Datetime string

• Format: The date and time strings are joined by either a space or the letter T.
• Range: -9999-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999

Timezone string

• Format: Represented as a UTC offset in the form of ±hh:mm or ±hhmm, appended directly to the time value.
• Range: UTC-15:00 to UTC+15:00