Data Types and Functions

Complete reference for data types, identifiers, and built-in functions.

Identifiers

An identifier is an ID to name your database objects, such as index names, field names, aliases, and so on. Energy Logserver supports two types of identifiers: regular identifiers and delimited identifiers.

Regular identifiers

A regular identifier is a string of characters that starts with an ASCII letter (lower or upper case). The next character can either be a letter, digit, or underscore (_). It can’t be a reserved keyword. Whitespace and other special characters are also not allowed.

Energy Logserver supports the following regular identifiers:

1. Identifiers prefixed by a dot . sign. Use to hide an index. For example .sample-dashboards.

2. Identifiers prefixed by an @ sign. Use for meta fields generated by Network Probe ingestion.

3. Identifiers with hyphen - in the middle. Use for index names with date information.

4. Identifiers with star * present. Use for wildcard match of index patterns.

For regular identifiers, you can use the name without any back tick or escape characters. In this example, source, fields, account_number, firstname, and lastname are all identifiers. Out of these, the source field is a reserved identifier.

SELECT account_number, firstname, lastname FROM accounts;

account_number	firstname	lastname
1	Amber	Duke
6	Hattie	Bond
13	Nanette	Bates
18	Dale	Adams

Delimited identifiers

A delimited identifier can contain special characters not allowed by a regular identifier. You must enclose delimited identifiers with back ticks (``). Back ticks differentiate the identifier from special characters.

If the index name includes a dot (.), for example, log-2021.01.11, use delimited identifiers with back ticks to escape it `log-2021.01.11`.

Typical examples of using delimited identifiers:

1. Identifiers with reserved keywords.

2. Identifiers with a . present. Similarly, - to include date information.

3. Identifiers with other special characters. For example, Unicode characters.

To quote an index name with back ticks:

source=`accounts` | fields `account_number`;

account_number
1
6
13
18

Case sensitivity

Identifiers are case sensitive. They must be exactly the same as what’s stored in Energy Logserver.

For example, if you run source=Accounts, you’ll get an index not found exception because the actual index name is in lower case.

Data types

The following table shows the data types supported by the SQL plugin and how each one maps to SQL and Energy Logserver data types:

Energy Logserver SQL Type	Energy Logserver Type	SQL Type
boolean	boolean	BOOLEAN
byte	byte	TINYINT
short	byte	SMALLINT
integer	integer	INTEGER
long	long	BIGINT
float	float	REAL
half_float	float	FLOAT
scaled_float	float	DOUBLE
double	double	DOUBLE
keyword	string	VARCHAR
text	text	VARCHAR
date	timestamp	TIMESTAMP
date_nanos	timestamp	TIMESTAMP
ip	ip	VARCHAR
date	timestamp	TIMESTAMP
binary	binary	VARBINARY
object	struct	STRUCT
nested	array	STRUCT

In addition to this list, the SQL plugin also supports the datetime type, though it doesn’t have a corresponding mapping with Energy Logserver or SQL. To use a function without a corresponding mapping, you must explicitly convert the data type to one that does.

Date and time types

The date and time types represent a time period: DATE, TIME, DATETIME, TIMESTAMP, and INTERVAL. By default, the Energy Logserver DSL uses the date type as the only date-time related type that contains all information of an absolute time point.

To integrate with SQL, each type other than the timestamp type holds part of the time period information. Some functions might have restrictions for the input argument type.

Date

The date type represents the calendar date regardless of the time zone. A given date value is a 24-hour period, but this period varies in different timezones and might have flexible hours during daylight saving programs. The date type doesn’t contain time information and it only supports a range of 1000-01-01 to 9999-12-31.

Type	Syntax	Range
date	yyyy-MM-dd	0001-01-01 to 9999-12-31

Time

The time type represents the time of a clock regardless of its timezone. The time type doesn’t contain date information.

Type	Syntax	Range
time	hh:mm:ss[.fraction]	00:00:00.0000000000 to 23:59:59.9999999999

Datetime

The datetime type is a combination of date and time. It doesn’t contain timezone information. For an absolute time point that contains date, time, and timezone information, see Timestamp.

Type	Syntax	Range
datetime	yyyy-MM-dd hh:mm:ss[.fraction]	0001-01-01 00:00:00.0000000000 to 9999-12-31 23:59:59.9999999999

Timestamp

The timestamp type is an absolute instance independent of timezone or convention. For example, for a given point of time, if you change the timestamp to a different timezone, its value changes accordingly.

The timestamp type is stored differently from the other types. It’s converted from its current timezone to UTC for storage and converted back to its set timezone from UTC when it’s retrieved.

Type	Syntax	Range
timestamp	yyyy-MM-dd hh:mm:ss[.fraction]	0001-01-01 00:00:01.9999999999 UTC to 9999-12-31 23:59:59.9999999999

Interval

The interval type represents a temporal duration or a period.

Type	Syntax
interval	INTERVAL expr unit

The expr unit is any expression that eventually iterates to a quantity value. It represents a unit for interpreting the quantity, including MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. The INTERVAL keyword and the unit specifier are not case sensitive.

The interval type has two classes of intervals: year-week intervals and day-time intervals.

• Year-week intervals store years, quarters, months, and weeks.

• Day-time intervals store days, hours, minutes, seconds, and microseconds.

Convert between date and time types

Apart from the interval type, all date and time types can be converted to each other. The conversion might alter the value or cause some information loss. For example, when extracting the time value from a datetime value, or converting a date value to a datetime value, and so on.

The SQL plugin supports the following conversion rules for each of the types:

Convert from date

• Because the date value doesn’t have any time information, conversion to the time type isn’t useful and always returns a zero time value of 00:00:00.

• Converting from date to datetime has a data fill-up due to the lack of time information. It attaches the time 00:00:00 to the original date by default and forms a datetime instance. For example, conversion of 2020-08-17 to a datetime type is 2020-08-17 00:00:00.

• Converting to timestamp type alternates both the time value and the timezone information. It attaches the zero time value 00:00:00 and the session timezone (UTC by default) to the date. For example, conversion of 2020-08-17 to a datetime type with a session timezone UTC is 2020-08-17 00:00:00 UTC.

Convert from time

• You cannot convert the time type to any other date and time types because it doesn’t contain any date information.

Convert from datetime

• Converting datetime to date extracts the date value from the datetime value. For example, conversion of 2020-08-17 14:09:00 to a date type is 2020-08-08.

• Converting datetime to time extracts the time value from the datetime value. For example, conversion of 2020-08-17 14:09:00 to a time type is 14:09:00.

• Because the datetime type doesn’t contain timezone information, converting to timestamp type fills up the timezone value with the session timezone. For example, conversion of 2020-08-17 14:09:00 (UTC) to a timestamp type is 2020-08-17 14:09:00 UTC.

Convert from timestamp

• Converting from a timestamp type to a date type extracts the date value and converting to a time type extracts the time value. Converting from a timestamp type to datetime type extracts only the datetime value and leaves out the timezone value. For example, conversion of 2020-08-17 14:09:00 UTC to a date type is 2020-08-17, to a time type is 14:09:00, and to a datetime type is 2020-08-17 14:09:00.

Functions

You must enable fielddata in the document mapping for most string functions to work properly.

The specification shows the return type of the function with a generic type T as the argument. For example, abs(number T) -> T means that the function abs accepts a numerical argument of type T, which could be any subtype of the number type, and it returns the actual type of T as the return type.

The SQL plugin supports the following common functions shared across the SQL and PPL languages.

Mathematical

Function	Specification	Example
abs	abs(number T) -> T	SELECT abs(0.5)
add	add(number T, number T) -> T	SELECT add(1, 5)
cbrt	cbrt(number T) -> double	SELECT cbrt(8)
ceil	ceil(number T) -> T	SELECT ceil(0.5)
conv	conv(string T, integer, integer) -> string	SELECT conv(‘2C’, 16, 10), conv(1111, 2, 10)
crc32	crc32(string) -> string	SELECT crc32(‘MySQL’)
divide	divide(number T, number T) -> T	SELECT divide(1, 0.5)
e	e() -> double	SELECT e()
exp	exp(number T) -> double	SELECT exp(0.5)
expm1	expm1(number T) -> double	SELECT expm1(0.5)
floor	floor(number T) -> long	SELECT floor(0.5)
ln	ln(number T) -> double	SELECT ln(10)
log	log(number T) -> double OR log(number T, number T) -> double	SELECT log(10), SELECT log(2, 16)
log2	log2(number T) -> double	SELECT log2(10)
log10	log10(number T) -> double	SELECT log10(10)
mod	mod(number T, number T) -> T	SELECT mod(2, 3)
modulus	modulus(number T, number T) -> T	SELECT modulus(2, 3)
multiply	multiply(number T, number T) -> T	SELECT multiply(2, 3)
pi	pi() -> double	SELECT pi()
pow	pow(number T, number T) -> double	SELECT pow(2, 3)
power	power(number T, number T) -> double	SELECT power(2, 3)
rand	rand() -> float OR rand(number T) -> float	SELECT rand(), SELECT rand(0.5)
rint	rint(number T) -> double	SELECT rint(1.5)
round	round(number T) -> T OR round(number T, integer) -> T	SELECT round(1.5), SELECT round(1.175, 2)
sign	sign(number T) -> integer	SELECT sign(1.5)
signum	signum(number T) -> integer	SELECT signum(0.5)
sqrt	sqrt(number T) -> double	SELECT sqrt(0.5)
strcmp	strcmp(string T, string T) -> integer	SELECT strcmp(‘hello’, ‘hello world’)
subtract	subtract(number T, number T) -> T	SELECT subtract(3, 2)
truncate	truncate(number T, number T) -> T	SELECT truncate(56.78, 1)
+	number T + number T -> T	SELECT 1 + 5
-	number T - number T -> T	SELECT 3 - 2
*	number T * number T -> T	SELECT 2 * 3
/	number T / number T -> T	SELECT 1 / 0.5
%	number T % number T -> T	SELECT 2 % 3

Trigonometric

Function	Specification	Example
acos	acos(number T) -> double	SELECT acos(0.5)
asin	asin(number T) -> double	SELECT asin(0.5)
atan	atan(number T) -> double	SELECT atan(0.5)
atan2	atan2(number T, number T) -> double	SELECT atan2(1, 0.5)
cos	cos(number T) -> double	SELECT cos(0.5)
cosh	cosh(number T) -> double	SELECT cosh(0.5)
cot	cot(number T) -> double	SELECT cot(0.5)
degrees	degrees(number T) -> double	SELECT degrees(0.5)
radians	radians(number T) -> double	SELECT radians(0.5)
sin	sin(number T) -> double	SELECT sin(0.5)
sinh	sinh(number T) -> double	SELECT sinh(0.5)
tan	tan(number T) -> double	SELECT tan(0.5)

Date and time

Functions marked with * are only available in SQL.

Function	Specification	Example
adddate	adddate(date, INTERVAL expr unit) -> date	SELECT adddate(date(‘2020-08-26’), INTERVAL 1 hour)
addtime	addtime(date, date) -> date	SELECT addtime(date(‘2008-12-12’), date(‘2008-12-12’))
convert_tz	convert_tz(date, string, string) -> date	SELECT convert_tz(‘2008-12-25 05:30:00’, ‘+00:00’, ‘America/Los_Angeles’)
curtime	curtime() -> time	SELECT curtime()
curdate	curdate() -> date	SELECT curdate()
current_date	current_date() -> date	SELECT current_date()
current_time	current_time() -> time	SELECT current_time()
current_timestamp	current_timestamp() -> date	SELECT current_timestamp()
date	date(date) -> date	SELECT date(‘2000-01-02’)
datediff	datediff(date, date) -> integer	SELECT datediff(date(‘2000-01-02’), date(‘2000-01-01’))
datetime	datetime(string) -> datetime	SELECT datetime(‘2008-12-25 00:00:00’)
date_add	date_add(date, INTERVAL integer UNIT)	SELECT date_add(‘2020-08-26’, INTERVAL 1 HOUR)
date_format	date_format(date, string) -> string OR date_format(date, string, string) -> string	SELECT date_format(date(‘2020-08-26’), ‘Y’)
date_sub	date_sub(date, INTERVAL expr unit) -> date	SELECT date_sub(date(‘2008-01-02’), INTERVAL 31 day)
dayofmonth	dayofmonth(date) -> integer	SELECT dayofmonth(date(‘2001-05-07’))
day	day(date) -> integer	SELECT day(date(‘2020-08-25’))
dayname	dayname(date) -> string	SELECT dayname(date(‘2020-08-26’))
dayofmonth	dayofmonth(date) -> integer	SELECT dayofmonth(date(‘2020-08-26’))
dayofweek	dayofweek(date) -> integer	SELECT dayofweek(date(‘2020-08-26’))
dayofyear	dayofyear(date) -> integer	SELECT dayofyear(date(‘2020-08-26’))
dayofweek	dayofweek(date) -> integer	SELECT dayofweek(date(‘2020-08-26’))
day_of_month*	day_of_month(date) -> integer	SELECT day_of_month(date(‘2020-08-26’))
day_of_week*	day_of_week(date) -> integer	SELECT day_of_week(date(‘2020-08-26’))
day_of_year*	day_of_year(date) -> integer	SELECT day_of_year(date(‘2020-08-26’))
extract*	extract(part FROM date) -> integer	SELECT extract(MONTH FROM datetime(‘2020-08-26 10:11:12’))
from_days	from_days(N) -> integer	SELECT from_days(733687)
from_unixtime	from_unixtime(N) -> date	SELECT from_unixtime(1220249547)
get_format	get_format(PART, string) -> string	SELECT get_format(DATE, ‘USA’)
hour	hour(time) -> integer	SELECT hour(time ‘01:02:03’)
hour_of_day*	hour_of_day(time) -> integer	SELECT hour_of_day(time ‘01:02:03’)
last_day*	last_day(date) -> integer	SELECT last_day(date(‘2020-08-26’))
localtime	localtime() -> date	SELECT localtime()
localtimestamp	localtimestamp() -> date	SELECT localtimestamp()
makedate	makedate(double, double) -> date	SELECT makedate(1945, 5.9)
maketime	maketime(integer, integer, integer) -> date	SELECT maketime(1, 2, 3)
microsecond	microsecond(expr) -> integer	SELECT microsecond(time ‘01:02:03.123456’)
minute	minute(expr) -> integer	SELECT minute(time ‘01:02:03’)
minute_of_day*	minute_of_day(expr) -> integer	SELECT minute_of_day(time ‘01:02:03’)
minute_of_hour*	minute_of_hour(expr) -> integer	SELECT minute_of_hour(time ‘01:02:03’)
month	month(date) -> integer	SELECT month(date(‘2020-08-26’))
month_of_year*	month_of_year(date) -> integer	SELECT month_of_year(date(‘2020-08-26’))
monthname	monthname(date) -> string	SELECT monthname(date(‘2020-08-26’))
now	now() -> date	SELECT now()
period_add	period_add(integer, integer)	SELECT period_add(200801, 2)
period_diff	period_diff(integer, integer)	SELECT period_diff(200802, 200703)
quarter	quarter(date) -> integer	SELECT quarter(date(‘2020-08-26’))
second	second(time) -> integer	SELECT second(time ‘01:02:03’)
second_of_minute*	second_of_minute(time) -> integer	SELECT second_of_minute(time ‘01:02:03’)
sec_to_time*	sec_to_time(integer) -> date	SELECT sec_to_time(10000)
subdate	subdate(date, INTERVAL expr unit) -> date, datetime	SELECT subdate(date(‘2008-01-02’), INTERVAL 31 day)
subtime	subtime(date, date) -> date	SELECT subtime(date(‘2008-12-12’), date(‘2008-11-15’))
str_to_date*	str_to_date(string, format) -> date	SELECT str_to_date(“01,5,2013”, “%d,%m,%Y”)
time	time(expr) -> time	SELECT time(‘13:49:00’)
timediff	timediff(time, time) -> time	SELECT timediff(time(‘23:59:59’), time(‘13:00:00’))
timestamp	timestamp(date) -> date	SELECT timestamp(‘2001-05-07 00:00:00’)
timestampadd	timestampadd(interval, integer, (date)) -> date	SELECT timestampadd(DAY, 17, datetime(‘2000-01-01 00:00:00’))
timestampdiff	timestampdiff(interval, date, date) -> integer	SELECT timestampdiff(YEAR, ‘1997-01-01 00:00:00’, ‘2001-03-06 00:00:00’)
time_format	time_format(date, string) -> string	SELECT time_format(‘1998-01-31 13:14:15.012345’, ‘%f %H %h %I %i %p %r %S %s %T’)
time_to_sec	time_to_sec(time) -> long	SELECT time_to_sec(time ‘22:23:00’)
to_days	to_days(date) -> long	SELECT to_days(date ‘2008-10-07’)
to_seconds	to_seconds(date) -> integer	SELECT to_seconds(date(‘2008-10-07’))
unix_timestamp	unix_timestamp(date) -> double	SELECT unix_timestamp(timestamp(‘1996-11-15 17:05:42’))
utc_date	utc_date() -> date	SELECT utc_date()
utc_time	utc_time() -> date	SELECT utc_time()
utc_timestamp	utc_timestamp() -> date	SELECT utc_timestamp()
week	week(date[mode]) -> integer	SELECT week(date(‘2008-02-20’))
weekofyear	weekofyear(date[mode]) -> integer	SELECT weekofyear(date(‘2008-02-20’))
week_of_year*	week_of_year(date[mode]) -> integer	SELECT week_of_year(date(‘2008-02-20’))
year	year(date) -> integer	SELECT year(date(‘2001-07-05’))
yearweek*	yearweek(date[mode]) -> integer	SELECT yearweek(date(‘2008-02-20’))

String

Function	Specification	Example
ascii	ascii(string) -> integer	SELECT ascii(‘h’)
concat	concat(string, string) -> string	SELECT concat(‘hello’, ‘world’)
concat_ws	concat_ws(separator, string, string…) -> string	SELECT concat_ws(” “, “Hello”, “World!”)
left	left(string, integer) -> string	SELECT left(‘hello’, 2)
length	length(string) -> integer	SELECT length(‘hello’)
locate	locate(string, string, integer) -> integer OR locate(string, string) -> integer	SELECT locate(‘o’, ‘hello’), locate(‘l’, ‘hello world’, 5)
replace	replace(string, string, string) -> string	SELECT replace(‘hello’, ‘l’, ‘x’)
right	right(string, integer) -> string	SELECT right(‘hello’, 2)
rtrim	rtrim(string) -> string	SELECT rtrim(‘hello’)
substring	substring(string, integer, integer) -> string	SELECT substring(‘hello’, 2, 4)
trim	trim(string) -> string	SELECT trim(‘hello’)
upper	upper(string) -> string	SELECT upper(‘hello world’)

Aggregate

Function	Specification	Example
avg	avg(number T) -> T	SELECT avg(column) FROM my-index
count	count(number T) -> T	SELECT count(date) FROM my-index
min	min(number T) -> T	SELECT min(column) FROM my-index
show	show(string) -> string	SHOW TABLES LIKE my-index

Advanced

Function	Specification	Example
if	if(boolean, os_type, os_type) -> os_type	SELECT if(false, 0, 1),if(true, 0, 1)
ifnull	ifnull(os_type, os_type) -> os_type	SELECT ifnull(0, 1), ifnull(null, 1)
isnull	isnull(os_type) -> integer	SELECT isnull(null), isnull(1)

Relevance-based search (full-text search)

These functions are only available in the WHERE clause. For their descriptions and usage examples in SQL and PPL, see Full-text search.