Utility Functions

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

Constants

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.

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))

named timeformat

1
2
3

FAKE( linspace(0, 1, 1))
MAPVALUE(0, strTime(time("now"), "RFC822", tz("UTC")), "time")
MARKDOWN(rownum(true))

parseFloat()

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

• str string

Parsing str into float number.

1
2
3

FAKE( csv(`world,3.141792`) )
MAPVALUE(1, parseFloat(value(1)))
JSON()

{
    "data": {
        "columns": [ "column0", "column1" ],
        "types": [ "string", "double" ],
        "rows": [ [ "world", 3.141792 ] ]
    },
    "success": true,
    "reason": "success",
    "elapse": "140.125µs"
}

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.

1
2
3

FAKE( csv(`world,True`) )
MAPVALUE(1, parseBool(value(1)))
JSON()

{
    "data": {
        "columns": [ "column0", "column1" ],
        "types": [ "string", "bool" ],
        "rows": [ [ "world", true ] ]
    },
    "success": true,
    "reason": "success",
    "elapse": "122.667µs"
}

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

1
2

SQL(`select to_char(time), value from example where time < ?`, time('now'))
CSV()

SQL(`select to_char(time), value from example where time = ?`, time(1628737200123456789))
CSV()

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).

1
2

SQL(`select to_char(time), value from example where time < ?`, timeAdd('now', '-10s'))
CSV()

SQL(`select to_char(time), value from example where time = ?`, timeAdd(1628737200123456789, '-5s'))
CSV()

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 omitted 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

sqlTimeformat()

Syntax: sqlTimeformat( format )

• format string

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

Math

Mathematical functions. Since v8.0.6

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

An example usage of math functions with MAPVALUE.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

FAKE(meshgrid(linspace(-4,4,100), linspace(-4,4, 100))) MAPVALUE(2, sin(pow(value(0), 2) + pow(value(1), 2)) / (pow(value(0), 2) + pow(value(1), 2)) ) MAPVALUE(0, list(value(0), value(1), value(2))) POPVALUE(1, 2) CHART( plugins("gl"), size("600px", "600px"), chartOption({ grid3D:{}, xAxis3D:{}, yAxis3D:{}, zAxis3D:{}, series:[ {type: "line3D", data: column(0)}, ] }) )

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

FAKE( meshgrid( linspace(0, 10, 100), linspace(0, 10, 100) ) ) MAPVALUE(2, abs( simplex(123, value(0), value(1)) ) * 10) MAPVALUE(0, list(value(0), value(1), value(2))) CHART( plugins("gl"), size("600px", "600px"), chartOption({ visualMap: { max: 8, inRange:{ color:[ "#313695", "#74add1", "#e0f3f8", "#fee090", "#f46d43", "#a50026"]} }, grid3D:{ boxWidth:100, boxDepth:100, boxHeight:20}, xAxis3D:{}, yAxis3D:{}, zAxis3D:{}, series:[ {type: "bar3D", data: column(0), itemStyle:{opacity:1.0}}, ] }) )

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.