Skip to main content

Time Functions

The time function package contains functions to convert, aggregate, and manipulate timestamps and DateTime scalars. The time function package is part of the standard SQRL function library.

IMPORT time.*; -- imports all time functions
IMPORT time.dayOfWeek; -- imports single time function

Reference​

The following table lists all the functions in the time package with a short description. The table also specifies whether a function is a time-window function.

Function NameDescriptionTime Window?
atZoneReturns the timestamp at the given timezone.no
dayOfMonthThis SQL function returns the day of the month for a given date. For example, dayOfMonth('2020-07-15') would return 15.no
dayOfWeekThis SQL function returns an integer representing the day of the week for a given date. For example, dayOfWeek('2020-07-01') returns 3, indicating that July 1, 2020 is a Wednesday.no
dayOfYearThis SQL function returns the day of the year for a given date. For example, dayOfYear('2020-02-14') returns 45.no
endOfDayTime window function that returns the end of day for the timestamp argument.
E.g. endOfDay(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-03-12T23:59:59.999999999Z		yes
endOfHourTime window function that returns the end of hour for the timestamp argument.
E.g. endOfHour(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-03-12T18:59:59.999999999Z		yes
endOfMinuteTime window function that returns the end of minute for the timestamp argument.
E.g. endOfMinute(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-03-12T18:23:59.999999999Z		yes
endOfMonthTime window function that returns the end of month for the timestamp argument.
E.g. endOfMonth(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-03-31T23:59:59.999999999Z		yes
endOfSecondTime window function that returns the end of second for the timestamp argument.
E.g. endOfSecond(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-03-12T18:23:34.999999999Z		yes
endOfWeekTime window function that returns the end of week for the timestamp argument.
E.g. endOfWeek(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-03-12T23:59:59.999999999Z		yes
endOfYearTime window function that returns the end of year for the timestamp argument.
E.g. endOfYear(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the timestamp 2023-12-31T23:59:59.999999999Z		yes
epochMilliToTimestampConverts the epoch timestamp in milliseconds to the corresponding timestamp.
E.g. epochMilliToTimestamp(1678645414000) returns the timestamp 2023-03-12T18:23:34Z		no
epochToTimestampConverts the epoch timestamp in seconds to the corresponding timestamp.
E.g. epochToTimestamp(1678645414) returns the timestamp 2023-03-12T18:23:34Z		no
hourThis SQL function returns the hour of a given time value. For example, hour('12:30:15') returns 12.no
minuteThis SQL function returns the minute of a given time value. For example, minute('12:45:30') returns 45.no
monthThis SQL function returns the month of a given date. For example, month('2020-07-01') returns 7.no
parseTimestampParses a timestamp from an ISO timestamp string.no
quarterThis SQL function returns an integer value representing the quarter of the year for a given date. For example, quarter('2020-07-15') returns 3, representing the third quarter of the year.no
secondThis SQL function returns the second item in a list of items. For example, second('apple', 'banana', 'cherry') would return 'banana'.no
timestampToEpochReturns the seconds since epoch for the given timestamp.
E.g. timestampToEpoch(parseTimestamp(2023-03-12T18:23:34.083Z)) returns the number 1678645414		no
timestampToStringConverts the timestamp to an ISO timestamp stringno
weekThis SQL function returns the week number of the year for a given date. For example, week('2020-02-14') returns 7, as February 14th is the 7th week of the year 2020.no
yearThis SQL function returns the year from a given date. For example, year('2020-01-01') would return 2020.no