Utility Functions

Utility Functions

Utility functions can be commonly used as parameters of any functions.

Constants

constantsdescription
NULLnull value
PI3.141592…. https://oeis.org/A000796

Context

key()

Syntax: key()

Returns the key of the current record.

value()

Syntax: value( [index] )

  • index integer optional index of the value array

Returns the whole value of the current records in array. If the index is given, it returns the element of the values.

For example, If the current value is [0, true, "hello", "world"]

  • value() returns the whole value array [0, true, "hello", "world"]
  • value(0) returns the first element of the value 0
  • value(3) returns the last element of the value "world"

payload()

Syntax: payload()

Returns the current input stream that sent from caller of the tql script. If the tql script is called via HTTP, the result of payload() is the stream of the body content of POST request. If the tql script is called via MQTT, the payload() returns the payload of the PUBLISH message.

param()

Syntax: param( name )

  • name string name of the query parameter

When the tql script is called via HTTP, the requested query parameters can be accessed by param() function.

context()

Syntax: context()

Returns context object of the script runtime.

String

escapeParam()

Syntax: escapeParam( str ) : string Since v8.0.7

escapeParam() escapes the string so it can be safely placed inside a URL query.

1
2
3
4
5
6
CSV(
    file(`http://127.0.0.1:5654/db/query?format=csv&q=`+
        escapeParam(`select count(*) from example`)
    )
)
CSV()

strTrimSpace()

Syntax: strTrimSpace(str) : string Since v8.0.7

strTrimSpace returns a slice of the string str, with all leading and trailing white space removed.

strTrimPrefix()

Syntax: strTrimPrefix(str, prefix) : string Since v8.0.7

strTrimPrefix returns str without the provided leading prefix string. If str doesn’t start with prefix, str is returned unchanged.

strTrimSuffix()

Syntax: strTrimSuffix(str, suffix) : string Since v8.0.7

strTrimSuffix returns str without the provided trailing suffix string. If str doesn’t end with suffix, str is returned unchanged.

strHasPrefix()

Syntax: strHasPrefix(str, prefix) : boolean Since v8.0.7

strHasPrefix tests whether the string str begins with prefix.

strHasSuffix()

Syntax: strHasSuffix(str, suffix) : boolean Since v8.0.7

strHasSuffix tests whether the string s ends with suffix.

strReplaceAll()

Syntax: strReplaceAll(str, old, new) : string Since v8.0.7

strReplaceAll returns a copy of the string s with all non-overlapping instances of old replaced by new. If old is empty, it matches at the beginning of the string and after each UTF-8 sequence, yielding up to k+1 replacements for a k-rune string.

strReplace()

Syntax: strReplace(str, old, new, n) : string Since v8.0.7

  • str string
  • old string
  • new string
  • n integer

strReplacereturns a copy of the string s with the first n non-overlapping instances of old replaced by new. If old is empty, it matches at the beginning of the string and after each UTF-8 sequence, yielding up to k+1 replacements for a k-rune string. If n < 0, there is no limit on the number of replacements.

strSub()

Syntax: strSub(str, offset [, count]) : string Since v8.0.7

strSub returns substring of str.

strIndex()

Syntax: strIndex(str, substr) : number Since v8.0.15

Returns the index of the first instance of substr in str, or -1 if substr is not present in str.

strLastIndex()

Syntax: strLastIndex(str, substr) : number Since v8.0.15

Returns the index of the last instance of substr in str, or -1 if substr is not present in str.

strToUpper()

Syntax: strToUpper(str) : string Since v8.0.7

strToUpper returns str with all Unicode letters mapped to their upper case.

strToLower()

Syntax: strToLower(str, suffix) : string Since v8.0.7

strToLower returns str with all Unicode letters mapped to their lower case.

strSprintf()

Syntax: strSprintf(fmt, args...) : string Since v8.0.7

strSprintf() formats according to a format specifier and returns the resulting string.

The syntax of fmt format string is %[flags][width][.precision]verb.

The verb at the end defines the type and the interpretation of its corresponding argument.

verbdescription
fdecimal floating point, lowercase
Fdecimal floating point, uppercase
escientific notation (mantissa/exponent), lowercase
Escientific notation (mantissa/exponent), uppercase
gthe shortest representation of %e or %f
Gthe shortest representation of %E or %F
qa quoted string
tthe word true or false
sa string
vdefault format
%%a single %
1
2
3
4
FAKE( csv(`world,3.141792`) )
MAPVALUE(1, parseFloat(value(1)))
MAPVALUE(2, strSprintf(`hello %s? %1.2f`, value(0), value(1)))
CSV()
world,3.141792,hello world? 3.14

strTime()

Syntax: strTime(time, format [, tz]) : string Since v8.0.7

  • time time
  • format string|sqlTimeformat()
  • tz tz() optional, time zone if omitted default is tz('UTC').

strTime() formats time value to string according to the given format and time zone.

numeric timeformat

1
2
3
FAKE( linspace(0, 1, 1))
MAPVALUE(0, strTime(time("now"), "2006/01/02 15:04:05.999", tz("UTC")), "result")
MARKDOWN(rownum(true))

sqlTimeformat()

1
2
3
FAKE( linspace(0, 1, 1))
MAPVALUE(0, strTime(time("now"), sqlTimeformat("YYYY/MM/DD HH24:MI:SS.nnn"), tz("UTC")), "result")
MARKDOWN(rownum(true))
ROWNUMresult
12024/01/10 07:27:29.667

named timeformat

Since v8.0.12
1
2
3
FAKE( linspace(0, 1, 1))
MAPVALUE(0, strTime(time("now"), "RFC822", tz("UTC")), "time")
MARKDOWN(rownum(true))
ROWNUMtime
110 Jan 24 07:23 UTC

parseFloat()

Syntax: parseFloat( str ) : number Since v8.0.7

  • str string

Parsing str into float number.

parseBool()

Syntax: parseBool( str ) : boolean Since v8.0.7

  • str string

It takes one of the accepted string values: “1”, “t”, “T”, “TRUE”, “true”, “True”, “0”, “f”, “F”, “FALSE”, “false”, “False” and converts it to the equivalent boolean value: true or false. For any other string, the function returns an error.

String Match

glob()

Syntax: glob(pattern, text) : boolean Since v8.0.7

glob returns true if the text does match with pattern.

1
2
3
4
FAKE( linspace(1, 4, 4))
PUSHVALUE(0, "map."+value(0))
WHEN( glob("*.3", value(0)), doLog("found", value(1)))
CSV()

regexp()

Syntax: regexp(expression, text) : boolean Since v8.0.7

regexp returns true if the text does match with expression.

1
2
3
4
FAKE( linspace(1, 4, 4))
PUSHVALUE(0, "map."+value(0))
WHEN( regexp(`^map\.[2,3]$`, value(0)), doLog("found", value(1)))
CSV()

Time

time()

Syntax: time( number|string ) : time

  • time('now') returns current time.
  • time('now -10s50ms') returns the time 10.05 seconds before from now.
  • time(1672531200*1000000000) returns the time of Jan-1-2023 AM 12:00:00

timeYear()

Syntax: timeYear( time [, timezone] ) : number Since v8.0.15

timeYear() returns the year in which time occurs.

timeMonth()

Syntax: timeMonth( time [, timezone] ) : number Since v8.0.15

timeMonth() returns the month of the year specified by time.

timeDay()

Syntax: timeMonth( time [, timezone] ) : number Since v8.0.15

timeDay() returns the day of the month specified by time.

timeHour()

Syntax: timeHour( time [, timezone] ) : number Since v8.0.15

timeHour() returns the hour within the day specified by time, in the range [0, 23]..

timeMinute()

Syntax: timeMinute( time [, timezone] ) : number Since v8.0.15

timeMinute() returns the minute offset within the hour specified by time, in the range [0, 59].

timeSecond()

Syntax: timeSecond( time ) : number Since v8.0.15

timeSecond() returns the second offset within the minute specified by time, in the range [0, 59].

timeNanosecond()

Syntax: timeNanosecond( time ) : number Since v8.0.15

timeNanosecond() returns the nanosecond offset within the second specified by time, in the range [0, 999999999].

timeISOYear()

Syntax: timeISOYear( time [, timezone] ) : number Since v8.0.15

timeISOYear() returns the ISO 8601 year number in which ts occurs.

timeISOWeek()

Syntax: timeISOWeek( time [, timezone] ) : number Since v8.0.15

timeISOWeek() returns the ISO 8601 week number in which time occurs. Week ranges from 1 to 53. Jan 01 to Jan 03 of year n might belong to week 52 or 53 of year n-1, and Dec 29 to Dec 31 might belong to week 1 of year n+1.

According to the rule that the first calendar week of a calendar year is the week including the first Thursday of that year, and that the last one is the week immediately preceding the first calendar week of the next calendar year. See https://www.iso.org/obp/ui#iso:std:iso:8601:-1:ed-1:v1:en:term:3.1.1.23 for details.

timeYearDay()

Syntax: timeYearDay( time [, timezone] ) : number Since v8.0.15

timeYearDay() returns the day of the year specified by time, in the range [1,365] for non-leap years, and [1,366] in leap years.

timeWeekDay()

Syntax: timeWeekDay( time [, timezone] ) : number Since v8.0.15

timeWeekDay() returns the day of the week specified by time. (Sunday = 0, …).

1
2
3
4
FAKE(arrange(1, 7, 1))
MAPVALUE(0, time(strSprintf("now - %.fd",value(0))))
GROUP( lazy(true), by(timeWeekDay(value(0))), count(value(0)) )
CSV()

timeUnix()

Syntax: timeUnix( time ) : number Since v8.0.13

timeUnix returns time as a Unix time, the number of seconds elapsed since January 1, 1970 UTC. The result does not depend on the location associated with time.

timeUnixMilli()

Syntax: timeUnixMilli( time ) : number Since v8.0.13

timeUnixMilli returns time as a Unix time, the number of milliseconds elapsed since January 1, 1970 UTC. The result does not depend on the location associated with time.

timeUnixMicro()

Syntax: timeUnixMicro( time ) : number Since v8.0.13

timeUnixMicro returns time as a Unix time, the number of microseconds elapsed since January 1, 1970 UTC. The result does not depend on the location associated with time.

timeUnixNano()

Syntax: timeUnixNano( time ) : number Since v8.0.13

timeUnixNano returns time as a Unix time, the number of nanoseconds elapsed since January 1, 1970 UTC. The result does not depend on the location associated with time.

timeAdd()

Syntax: timeAdd( number|string|time [, timeExpression] ) : time

Example)

  • timeAdd('now', 0) returns current time.
  • timeAdd('now', '-10s50ms') returns the time 10.05 seconds before from now.
  • timeAdd(value(0), '1m') if value(0) is time, it returns the time of 1 minute later from value(0).

roundTime()

Syntax: roundTime( time, duration ) : time

Returns rounded time.

Example)

  • roundTime(time('now'), '1h')
  • roundTime(value(0), '1s')

parseTime()

Syntax: parseTime( time, format [, timezone] ) : time

  • time string time expression
  • format string time format expression
  • timezone tz timezone, use tz() to get the demand location, if ommited default is tz("UTC").

Example)

  • parseTime("2023-03-01 14:01:02", "DEFAULT", tz("Asia/Tokyo"))
  • parseTime("2023-03-01 14:01:02", "DEFAULT", tz("local"))

tz()

Syntax: tz( name ) : timeZone

Returns time zone that matched with the given name

Example)

  • tz('local')
  • tz('UTC')
  • tz('EST')
  • tz("Europe/Paris")

timeformat()

Syntax: timeformat( format )

  • format string
1
2
3
4
5
6
FAKE( json({
    [ 1701345032123456789, 10],
    [ 1701345043219876543, 11]
}))
MAPVALUE(0, time(value(0)) )
CSV(timeformat("DEFAULT"), tz("Asia/Seoul"))
2023-11-30 20:50:32.123,10
2023-11-30 20:50:43.219,11
formatresult of timeformatting
DEFAULT2006-01-02 15:04:05.999
NUMERIC01/02 03:04:05PM ‘06 -0700
ANSICMon Jan _2 15:04:05 2006
UNIXMon Jan _2 15:04:05 MST 2006
RUBYMon Jan 02 15:04:05 -0700 2006
RFC82202 Jan 06 15:04 MST
RFC822Z02 Jan 06 15:04 -0700
RFC850Monday, 02-Jan-06 15:04:05 MST
RFC1123Mon, 02 Jan 2006 15:04:05 MST
RFC1123ZMon, 02 Jan 2006 15:04:05 -0700
RFC33392006-01-02T15:04:05Z07:00
RFC3339NANO2006-01-02T15:04:05.999999999Z07:00
KITCHEN3:04:05PM
STAMPJan _2 15:04:05
STAMPMILLIJan _2 15:04:05.000
STAMPMICROJan _2 15:04:05.000000
STAMPNANOJan _2 15:04:05.000000000
sunix epoch time in seconds
msunix epoch time in milliseconds
usunix epoch time in microseconds
nsunix epoch time in nanoseconds
s_msseconds and millisec (05.999)
s_usseconds and microsec (05.999999)
s_nsseconds and nanosec (05.999999999)
s.msseconds and millisec, zero padding (05.000)
s.usseconds and microsec, zero padding (05.000000)
s.nsseconds and nanosec, zero padding (05.000000000)

sqlTimeformat()

Syntax: sqlTimeformat( format )

  • format string
formatresult of timeformatting
YYYYfour-digit year value
YYtwo-digit year value
MMtwo-digit month value between 01 to 12
MMMday of week
DDtwo-digit day of month between 01 to 31
HH24two-digit hour value between 00 to 23
HH12two-digit hour value between 0 to 12
HHtwo-digit hour value between 0 to 12
MItwo-digit minute value between 00 to 59
SStwo-digit seconds value between 0 and 59
AMAM/PM
nnn…1 to 9 digits fractions of a second
1
2
3
4
5
6
FAKE( json({
    [ 1701345032123456789, 10],
    [ 1701345043219876543, 11]
}))
MAPVALUE(0, time(value(0)) )
CSV( sqlTimeformat("YYYY-MM-DD HH24:MI:SS.nnnnnn"), tz("Asia/Seoul") )
2023-11-30 20:50:32.123456,10
2023-11-30 20:50:43.219876,11

ansiTimeformat()

Syntax: ansiTimeformat( format )

  • format string
1
2
3
4
5
6
FAKE( json({
    [ 1701345032123456789, 10],
    [ 1701345043219876543, 11]
}))
MAPVALUE(0, time(value(0)) )
CSV( ansiTimeformat("yyyy-mm-dd hh:nn:ss.ffffff"), tz("UTC"))
2023-11-30 11:50:32.123456,10
2023-11-30 11:50:43.219876,11
formatresult of timeformatting
yyyyfour-digit year value
mmtwo-digit month value between 01 to 12
ddtwo-digit day value between 01 to 31
hhtwo-digit hour value between 00 to 23
nntwo-digit minute value between 00 to 59
sstwo-digit seconds value between 0 and 59
fff…1 to 9 digits fractions of a second

Math

Mathematical functions. Since v8.0.6

This functions does not guarantee bit-identical results across system architectures.

functiondescription
abs(x)the absolute value of x.
acos(x)the arccosine, in radians, of x.
acosh(x)the inverse hyperbolic cosine of x.
asin(x)the arcsine, in radians, of x.
asinh(x)the inverse hyperbolic sine of x.
atan(x)the arctangent, in radians, of x.
atanh(x)the inverse hyperbolic tangent of x.
ceil(x)the least integer value greater than or equal to x.
cos(x)the cosine of the radian argument x.
cosh(x)the hyperbolic cosine of x.
exp(x)e**x, the base-e exponential of x.
exp2(x)2**x, the base-2 exponential of x.
floor(x)the greatest integer value less than or equal to x.
log(x)the natural logarithm of x.
log2(x)the binary logarithm of x. The special cases are the same as for log.
log10(x)the decimal logarithm of x. The special cases are the same as for log.
max(x,y)the larger of x or y.
min(x,y)the smaller of x or y.
mod(x,y)the floating-point remainder of x/y.
The magnitude of the result is less than y and its sign agrees with that of x.
pow(x, y)x**y, the base-x exponential of y.
pow10(x)10**x, the base-10 exponential of n.
remainder(x,y)the IEEE 754 floating-point remainder of x/y.
round(x)the nearest integer, rounding half away from zero.
sin(x)the sine of the radian argument x.
sinh(x)the hyperbolic sine of x.
sqrt(x)the square root of x.
tan(x)the tangent of the radian argument x.
tanh(x)the hyperbolic tangent of x.
trunc(x)the integer value of x.

An example usage of math functions with MAPVALUE.

random()

Syntax: random() : number Since v8.0.7

random() returns a float, a pseudo-random number in the half-open interval [0.0,1.0).

simplex()

Syntax: simplex(seed, dim1 [, dim2 [, dim3 [, dim4]]]) : number Since v8.0.7

  • seed int seed number
  • dim1 ~ dim4 float number

simplex() returns SimpleX noise(wikipedia) by given seed and dimension values.

List

count()

Syntax: count( array|tuple ) : number

Returns the number of the elements.

list()

Syntax: list(args...) : list Since v8.0.7

list() returns a new tuple that contains the args as its elements.

dict()

Syntax: dict( name1, value1 [, name2, value2 ...]) : dictionary Since v8.0.8

dict() returns a new dictionary that contains pairs of namen:valuen.

Last updated on