Datetime Functions

Datetime Value Functions

A datetime value function returns a temporal instant value.

date()

Returns a value of type DATE.

Syntax	`date([<param>])`
Arguments	Name	Type	Description
Arguments	`<param>`	`STRING` or `MAP`	Either a date string (format) or a map with the fields `year`, `month`, and `day`
Return 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.

UQL
return date(), current_date

Result:

date()	current_date
2025-08-21	2025-08-21

The parameter should match one of the supported formats:

UQL
uncollect [
  date("1993-05-09"),
  date("19930509"),
  date({year: 1993, month: 5, day: 9}),
  date({year: 1993, month: 5}),
  date({year: 1993})
] as value
return value

Result:

value
1993-05-09
1993-05-09
1993-05-09
1993-05-01
1993-01-01

local_datetime()

Returns a value of type LOCAL DATETIME.

Syntax	`local_datetime([<param>])`
Arguments	Name	Type	Description
Arguments	`<param>`	`STRING` or `MAP`	Either a datetime string (format) or a map with the fields `year`, `month`, `day`, `hour`, `minute`, `second`, and one of `millisecond` (3 digits), `microsecond` (6 digits), or `nanosecond` (9 digits)
Return 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.

UQL
return local_datetime(), local_timestamp

Result:

local_datetime()	local_timestamp
2025-08-21 15:20:30.625790824	2025-08-21 15:20:30.625790824

The parameter should match one of the supported formats:

UQL
uncollect [
  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})
] as value
return value

Result:

value
1993-05-09 03:02:11.7
1993-05-09 03:02:11.7
1993-05-09 03:02:11
1993-05-09 03:02:11
1993-05-09 03:02:11.07
1993-05-09 03:02:11.00007
1993-05-09 03:02:11.00000007
1993-05-09 03:02:11
1993-05-09 03:02:00
1993-05-09 03:00:00

local_time()

Returns a value of type LOCAL TIME.

Syntax	`local_time([<param>])`
Arguments	Name	Type	Description
Arguments	`<param>`	`STRING` or `MAP`	Either a time string (format) or a map with the fields `hour`, `minute`, `second`, and one of `millisecond` (3 digits), `microsecond` (6 digits), or `nanosecond` (9 digits)
Return 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).

UQL
return local_time()

Result:

local_time()
15:20:30.625790824

The parameter should match one of the supported formats:

UQL
uncollect [
  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})
] as value
return value

Result:

value
03:02:11.7
03:02:11.7
03:02:11
03:02:11
03:02:11.07
03:02:11.00007
03:02:11.00000007
03:02:11
03:02:00
03:00:00

now()

Returns the current datetime in Coordinated Universal Time (UTC).

Syntax	`now()`
Return Type	`DATETIME`

UQL
return now()

Result:

now()
2025-08-21 09:20:30.625790824

zoned_datetime()

Returns a value of type ZONED DATETIME.

Syntax	`zoned_datetime([<param>])`
Arguments	Name	Type	Description
Arguments	`<param>`	`STRING` or `MAP`	Either a datetime string (format) or a map with the fields `year`, `month`, `day`, `hour`, `minute`, `second`, one of `millisecond` (3 digits), `microsecond` (6 digits), or `nanosecond` (9 digits), as well as `timezone`
Return 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.

UQL
return zoned_datetime(), current_timestamp

Result:

zoned_datetime()	current_timestamp
2025-08-21 15:20:30.625790824-0600	2025-08-21 15:20:30.625790824-0600

The parameter should match one of the supported formats:

UQL
uncollect [
  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})
] as value
return value

Result:

value
1993-05-09 03:02:11.7-0600
1993-05-09 03:02:11.7-0600
1993-05-09 03:02:11-0600
1993-05-09 03:02:11-0600
1993-05-09 03:02:11.07-0600
1993-05-09 03:02:11.00007-0600
1993-05-09 03:02:11.00000007-0600
1993-05-09 03:02:11-0600
1993-05-09 03:02:00-0600
1993-05-09 03:00:00-0600

zoned_time()

Returns a value of type ZONED TIME.

Syntax	`zoned_time([<param>])`
Arguments	Name	Type	Description
Arguments	`<param>`	`STRING` or `MAP`	Either a time string (format) or a map with the fields `hour`, `minute`, `second`, one of `millisecond` (3 digits), `microsecond` (6 digits), or `nanosecond` (9 digits), as well as `timezone`
Return 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.

UQL
return zoned_time(), current_time

Result:

zoned_time()	current_time
15:20:30.625790824-0600	15:20:30.625790824-0600

The parameter should match one of the supported formats:

UQL
uncollect [
  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"})
] as value
return value

Result:

value
03:02:11.7-0600
03:02:11.7-0600
03:02:11-0600
03:02:11-0600
03:02:11.07-0600
03:02:11.00007-0600
03:02:11.00000007-0600
03:02:11-0600
03:02:00-0600
03:00:00-0600

Other Temporal Functions

dateAdd()

Adds a specified time interval to a given date.

Syntax	`dateAdd(<time>, <interval>, <unit>)`
Arguments	Name	Type	Description
	`<time>`	Temporal	The initial time
	`<interval>`	`INT`	The number of units to add (positive value to add, negative to subtract)
	`<unit>`	`STRING`	The unit of time to add, which can be `year`, `month`, `day`, `hour`, `minute`, or `second`
Return Type	`DATETIME`

UQL
return dateAdd("1970-1-1", -1, "hour") as newTime

Result:

newTime
1969-12-31 23:00:00

UQL
return dateAdd("1970-1-1", 10, "year")

Result:

newTime
1980-01-01 00:00:00

dateDiff()

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

Syntax	`dateAdd(<time1>, <time2>, <unit>)`
Arguments	Name	Type	Description
	`<endTime>`	Temporal	The first time
	`<time2>`	Temporal	The second time
	`<unit>`	`STRING`	The unit of difference, which can be `day`, `hour`, `minute`, or `second`
Return Type	`DATETIME`

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

Result:

diff
-120

dateFormat()

Prints a given date in the specific format.

Syntax	`dateFormat(<time>, <formatCode>)`
Arguments	Name	Type	Description
	`<time>`	Temporal	The input time
	`<formatCode>`	`STRING`	The format code
Return Type	`STRING`

Format codes:

Code	Description	Examples / Range
`%a`	Abbreviated weekday name in the system language	(en_US) Sun, Mon
`%A`	Full weekday name in the system language	(en_US) Sunday, Monday
`%b`	Abbreviated month name in the system language	(en_US) Jan, Feb
`%B`	Full month name in the system language	(en_US) January, February
`%c`	Default date and time format in the system settings	Wed Jan 11 10:59:28 2023
`%C`	Century number (year/100) in 2 digits	00, 01, ..., 99
`%d`	Day of the month (zero-padded)	01, 02, ..., 31
`%D`	Equivalent to `%m/%d/%y`	01/11/23
`%e`	Day of the month	1, 2, ..., 31
`%Ez`	Time zone	+08:00
`%g`	Year without the century	00, 01, ..., 99
`%G`	Year in 4 digits	0000, 0001, ..., 9999
`%h`	Equivalent to `%b`	See `%b`
`%H`	Hour using a 24-hour clock (zero-padded)	00, 01, ..., 23
`%I`	Hour using a 12-hour clock (zero-padded)	01, 02, ..., 12
`%j`	Day of the year (zero-padded)	001, 002, ..., 366
`%m`	Month of the year (zero-padded)	01, 02, ..., 12
`%M`	Minute (zero-padded)	00, 01, ..., 59
`%n`	Line break
`%p`	Either 'AM' or 'PM' according to the given time value	(en_US) AM, PM
`%P`	Either 'am' or 'pm' according to the given time value	(en_US) am, pm
`%r`	Equivalent to `%I/%M/%S %p`	01:49:23 AM
`%R`	Equivalent to `%H:%M`	13:49
`%S`	Second (zero-padded)	00, 01, ..., 59
`%t`	Tab
`%T`	Equivalent to `%H:%M:%S`	23:02:05
`%u`	Day number of the week, Monday being 1 (Sunday being 1 in a Sun Solaris system)	1, 2, ..., 7
`%U`	Week number of the year (zero-padded), starting with the first Sunday as the first day of week 01	00, 01, ..., 53
`%V`	Week number of year (zero-padded), with Monday as the first day of the week, week 01 is the first week that has at least 4 days in the current year	01, 02, ..., 53
`%W`	Week number of the year (zero-padded), starting with the first Monday as the first day of week 01	00, 01, ..., 53
`%w`	Day number of the week, Sunday being 0	0, 1, ..., 6
`%x`	Default date format in the system settings	01/11/23
`%X`	Default time format in the system settings	06:38:45
`%y`	Equivalent to `%g`	See `%g`
`%Y`	Equivalent to `%G`	See `%G`
`%z`	Offset from UTC in the format of `±HHMM[SS]`	+0000, -0400, +1030, ...
`%Z`	Name of the time zone	GMT, UTC, IST, CST, ...
`%%`	Character %	%

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

Result:

newFormat
Saturday 25 September, 2010

day()

Extracts the day component from a given date.

Syntax	`day(<time>)`
Arguments	Name	Type	Description
Arguments	`<time>`	Temporal	The input time
Return Type	`UINT`

UQL
return day("2022-10-5")

Result:

day("2022-10-5")
5

dayOfWeek()

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

Syntax	`dayOfWeek(<time>)`
Arguments	Name	Type	Description
Arguments	`<time>`	Temporal	The input time
Return Type	`UINT`

UQL
return dayOfWeek("2024-12-5")

Result:

dayOfWeek("2024-12-5")
5

month()

Extracts the month component from a given date.

Syntax	`month(<time>)`
Arguments	Name	Type	Description
Arguments	`<time>`	Temporal	The input time
Return Type	`UINT`

UQL
return month("2022-10-5")

Result:

month("2022-10-5")
10

year()

Extracts the year component from a given date.

Syntax	`year(<time>)`
Arguments	Name	Type	Description
Arguments	`<time>`	Temporal	The input time
Return Type	`UINT`

UQL
return year("2022-10-5")

Result:

year("2022-10-5")
2022

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