', default: (\)
```
When exporting the data in CSV format, this field specifies the character that should be treated as an escape character in the data file written to S3 bucket. Escaping happens in the following scenarios:
1. If the value itself contains the quote character (") then it will be escaped using an escape character. For example, if the value is `Time"stream`, where (\$1) is the configured escape character, then it will be escaped as `Time\"stream`.
1. If the value contains the configured escape character, it will be escaped. For example, if the value is `Time\stream`, then it will be escaped as `Time\\stream`.
If the exported output contains complex data type in the like Arrays, Rows or Timeseries, it will be serialized as a JSON string. Following is an example.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/timestream/latest/developerguide/supported-sql-constructs.UNLOAD.html)
INCLUDE\$1HEADER
```
include_header = 'true' , default: 'false'
```
When exporting the data in CSV format, this field lets you include column names as the first row of the exported CSV data files.
The accepted values are 'true' and 'false' and the default value is 'false'. Text transformation options such as `escaped_by` and `field_delimiter` apply to headers as well.
When including headers, it is important that you not select a carriage return character (ASCII 13, hex 0D, text '\$1r') or a line break character (ASCII 10, hex 0A, text '\$1n') as the `FIELD_DELIMITER`, since that will prevent many parsers from being able to parse the headers correctly in the resulting CSV output.
MAX\$1FILE\$1SIZE
```
max_file_size = 'X[MB|GB]' , default: '78GB'
```
This field specifies the maximum size of the files that the `UNLOAD` statement creates in Amazon S3. The `UNLOAD` statement can create multiple files but the maximum size of each file written to Amazon S3 will be approximately what is specified in this field.
The value of the field must be between 16 MB and 78 GB, inclusive. You can specify it in integer such as `12GB`, or in decimals such as `0.5GB` or `24.7MB`. The default value is 78 GB.
The actual file size is approximated when the file is being written, so the actual maximum size may not be exactly equal to the number you specify.
# Logical operators
Timestream for LiveAnalytics supports the following logical operators.
| Operator | Description | Example |
| --- | --- | --- |
| AND | True if both values are true | a AND b |
| OR | True if either value is true | a OR b |
| NOT | True if the value is false | NOT a |
+ The result of an `AND` comparison may be `NULL` if one or both sides of the expression are `NULL`.
+ If at least one side of an `AND` operator is `FALSE` the expression evaluates to `FALSE`.
+ The result of an `OR` comparison may be `NULL` if one or both sides of the expression are `NULL`.
+ If at least one side of an `OR` operator is `TRUE` the expression evaluates to `TRUE`.
+ The logical complement of `NULL` is `NULL`.
The following truth table demonstrates the handling of `NULL` in `AND` and `OR`:
| A | B | A and b | A or b |
| --- | --- | --- | --- |
| null | null | null | null |
| false | null | false | null |
| null | false | false | null |
| true | null | null | true |
| null | true | null | true |
| false | false | false | false |
| true | false | false | true |
| false | true | false | true |
| true | true | true | true |
The following truth table demonstrates the handling of NULL in NOT:
| A | Not a |
| --- | --- |
| null | null |
| true | false |
| false | true |
# Comparison operators
Timestream for LiveAnalytics supports the following comparison operators.
| Operator | Description |
| --- | --- |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
| = | Equal |
| <> | Not equal |
| \$1= | Not equal |
**Note**
The `BETWEEN` operator tests if a value is within a specified range. The syntax is as follows:
```
BETWEEN min AND max
```
The presence of `NULL` in a `BETWEEN` or `NOT BETWEEN` statement will result in the statement evaluating to `NULL`.
`IS NULL `and `IS NOT NULL` operators test whether a value is null (undefined). Using `NULL` with `IS NULL` evaluates to true.
In SQL, a `NULL` value signifies an unknown value.
# Comparison functions
Timestream for LiveAnalytics supports the following comparison functions.
**Topics**
+ [greatest()](comparison-functions.greatest.md)
+ [least()](comparison-functions.least.md)
+ [ALL(), ANY() and SOME()](comparison-functions.all-any-some.md)
# greatest()
The **greatest()** function returns the largest of the provided values. It returns `NULL` if any of the provided values are `NULL`. The syntax is as follows.
```
greatest(value1, value2, ..., valueN)
```
# least()
The **least()** function returns the smallest of the provided values. It returns `NULL` if any of the provided values are `NULL`. The syntax is as follows.
```
least(value1, value2, ..., valueN)
```
# ALL(), ANY() and SOME()
The `ALL`, `ANY` and `SOME` quantifiers can be used together with comparison operators in the following way.
| Expression | Meaning |
| --- | --- |
| A = ALL(...) | Evaluates to true when A is equal to all values. |
| A <> ALL(...) | Evaluates to true when A does not match any value. |
| A < ALL(...) | Evaluates to true when A is smaller than the smallest value. |
| A = ANY(...) | Evaluates to true when A is equal to any of the values. |
| A <> ANY(...) | Evaluates to true when A does not match one or more values. |
| A < ANY(...) | Evaluates to true when A is smaller than the biggest value. |
## Examples and usage notes
**Note**
When using `ALL`, `ANY` or `SOME`, the keyword `VALUES` should be used if the comparison values are a list of literals.
## Example: `ANY()`
An example of `ANY()` in a query statement as follows.
```
SELECT 11.7 = ANY (VALUES 12.0, 13.5, 11.7)
```
An alternative syntax for the same operation is as follows.
```
SELECT 11.7 = ANY (SELECT 12.0 UNION ALL SELECT 13.5 UNION ALL SELECT 11.7)
```
In this case, `ANY()` evaluates to `True`.
## Example: `ALL()`
An example of `ALL()` in a query statement as follows.
```
SELECT 17 < ALL (VALUES 19, 20, 15);
```
An alternative syntax for the same operation is as follows.
```
SELECT 17 < ALL (SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 15);
```
In this case, `ALL()` evaluates to `False`.
## Example: `SOME()`
An example of `SOME()` in a query statement as follows.
```
SELECT 50 >= SOME (VALUES 53, 77, 27);
```
An alternative syntax for the same operation is as follows.
```
SELECT 50 >= SOME (SELECT 53 UNION ALL SELECT 77 UNION ALL SELECT 27);
```
In this case, `SOME()` evaluates to `True`.
# Conditional expressions
Timestream for LiveAnalytics supports the following conditional expressions.
**Topics**
+ [The CASE statement](conditional-expressions.CASE.md)
+ [The IF statement](conditional-expressions.IF.md)
+ [The COALESCE statement](conditional-expressions.COALESCE.md)
+ [The NULLIF statement](conditional-expressions.NULLIF.md)
+ [The TRY statement](conditional-expressions.TRY.md)
# The CASE statement
The **CASE** statement searches each value expression from left to right until it finds one that equals `expression`. If it finds a match, the result for the matching value is returned. If no match is found, the result from the `ELSE` clause is returned if it exists; otherwise `null` is returned. The syntax is as follows:
```
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
```
Timestream also supports the following syntax for **CASE** statements. In this syntax, the "searched" form evaluates each boolean condition from left to right until one is `true` and returns the matching result. If no conditions are `true`, the result from the `ELSE` clause is returned if it exists; otherwise `null` is returned. See below for the alternate syntax:
```
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
```
# The IF statement
The **IF** statement evaluates a condition to be true or false and returns the appropriate value. Timestream supports the following two syntax representations for **IF**:
```
if(condition, true_value)
```
This syntax evaluates and returns `true_value` if condition is `true`; otherwise `null` is returned and `true_value` is not evaluated.
```
if(condition, true_value, false_value)
```
This syntax evaluates and returns `true_value` if condition is `true`, otherwise evaluates and returns `false_value`.
## Examples
```
SELECT
if(true, 'example 1'),
if(false, 'example 2'),
if(true, 'example 3 true', 'example 3 false'),
if(false, 'example 4 true', 'example 4 false')
```
| \$1col0 | \$1col1 | \$1col2 | \$1col3 |
| --- | --- | --- | --- |
| `example 1` | `-` `null` | `example 3 true` | `example 4 false` |
# The COALESCE statement
**COALESCE** returns the first non-null value in an argument list. The syntax is as follows:
```
coalesce(value1, value2[,...])
```
# The NULLIF statement
The **IF** statement evaluates a condition to be true or false and returns the appropriate value. Timestream supports the following two syntax representations for **IF**:
**NULLIF** returns null if `value1` equals `value2`; otherwise it returns `value1`. The syntax is as follows:
```
nullif(value1, value2)
```
# The TRY statement
The **TRY** function evaluates an expression and handles certain types of errors by returning `null`. The syntax is as follows:
```
try(expression)
```
# Conversion functions
Timestream for LiveAnalytics supports the following conversion functions.
**Topics**
+ [cast()](#conversion-functions.cast)
+ [try\$1cast()](#conversion-functions.try-cast)
## cast()
The syntax of the cast function to explicitly cast a value as a type is as follows.
```
cast(value AS type)
```
## try\$1cast()
Timestream for LiveAnalytics also supports the try\$1cast function that is similar to cast but returns null if cast fails. The syntax is as follows.
```
try_cast(value AS type)
```
# Mathematical operators
Timestream for LiveAnalytics supports the following mathematical operators.
| Operator | Description |
| --- | --- |
| \$1 | Addition |
| - | Subtraction |
| \$1 | Multiplication |
| / | Division (integer division performs truncation) |
| % | Modulus (remainder) |
# Mathematical functions
Timestream for LiveAnalytics supports the following mathematical functions.
| Function | Output data type | Description |
| --- | --- | --- |
| abs(x) | [same as input] | Returns the absolute value of x. |
| cbrt(x) | double | Returns the cube root of x. |
| ceiling(x) or ceil(x) | [same as input] | Returns x rounded up to the nearest integer. |
| degrees(x) | double | Converts angle x in radians to degrees. |
| e() | double | Returns the constant Euler's number. |
| exp(x) | double | Returns Euler's number raised to the power of x. |
| floor(x) | [same as input] | Returns x rounded down to the nearest integer. |
| from\$1base(string,radix) | bigint | Returns the value of string interpreted as a base-radix number. |
| ln(x) | double | Returns the natural logarithm of x. |
| log2(x) | double | Returns the base 2 logarithm of x. |
| log10(x) | double | Returns the base 10 logarithm of x. |
| mod(n,m) | [same as input] | Returns the modulus (remainder) of n divided by m. |
| pi() | double | Returns the constant Pi. |
| pow(x, p) or power(x, p) | double | Returns x raised to the power of p. |
| radians(x) | double | Converts angle x in degrees to radians. |
| rand() or random() | double | Returns a pseudo-random value in the range 0.0 1.0. |
| random(n) | [same as input] | Returns a pseudo-random number between 0 and n (exclusive). |
| round(x) | [same as input] | Returns x rounded to the nearest integer. |
| round(x,d) | [same as input] | Returns x rounded to d decimal places. |
| sign(x) | [same as input] | Returns the signum function of x, that is: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/timestream/latest/developerguide/mathematical-functions.html) For double arguments, the function additionally returns: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/timestream/latest/developerguide/mathematical-functions.html) |
| sqrt(x) | double | Returns the square root of x. |
| to\$1base(x, radix) | varchar | Returns the base-radix representation of x. |
| truncate(x) | double | Returns x rounded to integer by dropping digits after decimal point. |
| acos(x) | double | Returns the arc cosine of x. |
| asin(x) | double | Returns the arc sine of x. |
| atan(x) | double | Returns the arc tangent of x. |
| atan2(y, x) | double | Returns the arc tangent of y / x. |
| cos(x) | double | Returns the cosine of x. |
| cosh(x) | double | Returns the hyperbolic cosine of x. |
| sin(x) | double | Returns the sine of x. |
| tan(x) | double | Returns the tangent of x. |
| tanh(x) | double | Returns the hyperbolic tangent of x. |
| infinity() | double | Returns the constant representing positive infinity. |
| is\$1finite(x) | boolean | Determine if x is finite. |
| is\$1infinite(x) | boolean | Determine if x is infinite. |
| is\$1nan(x) | boolean | Determine if x is not-a-number. |
| nan() | double | Returns the constant representing not-a-number. |
# String operators
Timestream for LiveAnalytics supports the `||` operator for concatenating one or more strings.
# String functions
**Note**
The input data type of these functions is assumed to be varchar unless otherwise specified.
| Function | Output data type | Description |
| --- | --- | --- |
| chr(n) | varchar | Returns the Unicode code point n as a varchar. |
| codepoint(x) | integer | Returns the Unicode code point of the only character of str. |
| concat(x1, ..., xN) | varchar | Returns the concatenation of x1, x2, ..., xN. |
| hamming\$1distance(x1,x2) | bigint | Returns the Hamming distance of x1 and x2, i.e. the number of positions at which the corresponding characters are different. Note that the two varchar inputs must have the same length. |
| length(x) | bigint | Returns the length of x in characters. |
| levenshtein\$1distance(x1, x2) | bigint | Returns the Levenshtein edit distance of x1 and x2, i.e. the minimum number of single-character edits (insertions, deletions or substitutions) needed to change x1 into x2. |
| lower(x) | varchar | Converts x to lowercase. |
| lpad(x1, bigint size, x2) | varchar | Left pads x1 to size characters with x2. If size is less than the length of x1, the result is truncated to size characters. size must not be negative and x2 must be non-empty. |
| ltrim(x) | varchar | Removes leading whitespace from x. |
| replace(x1, x2) | varchar | Removes all instances of x2 from x1. |
| replace(x1, x2, x3) | varchar | Replaces all instances of x2 with x3 in x1. |
| Reverse(x) | varchar | Returns x with the characters in reverse order. |
| rpad(x1, bigint size, x2) | varchar | Right pads x1 to size characters with x2. If size is less than the length of x1, the result is truncated to size characters. size must not be negative and x2 must be non-empty. |
| rtrim(x) | varchar | Removes trailing whitespace from x. |
| split(x1, x2) | array(varchar) | Splits x1 on delimiter x2 and returns an array. |
| split(x1, x2, bigint limit) | array(varchar) | Splits x1 on delimiter x2 and returns an array. The last element in the array always contain everything left in the x1. limit must be a positive number. |
| split\$1part(x1, x2, bigint pos) | varchar | Splits x1 on delimiter x2 and returns the varchar field at pos. Field indexes start with 1. If pos is larger than the number of fields, then null is returned. |
| strpos(x1, x2) | bigint | Returns the starting position of the first instance of x2 in x1. Positions start with 1. If not found, 0 is returned. |
| strpos(x1, x2,bigint instance) | bigint | Returns the position of the Nth instance of x2 in x1. Instance must be a positive number. Positions start with 1. If not found, 0 is returned. |
| strrpos(x1, x2) | bigint | Returns the starting position of the last instance of x2 in x1. Positions start with 1. If not found, 0 is returned. |
| strrpos(x1, x2, bigint instance) | bigint | Returns the position of the Nth instance of x2 in x1 starting from the end of x1. instance must be a positive number. Positions start with 1. If not found, 0 is returned. |
| position(x2 IN x1) | bigint | Returns the starting position of the first instance of x2 in x1. Positions start with 1. If not found, 0 is returned. |
| substr(x, bigint start) | varchar | Returns the rest of x from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of x. |
| substr(x, bigint start, bigint len) | varchar | Returns a substring from x of length len from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of x. |
| trim(x) | varchar | Removes leading and trailing whitespace from x. |
| upper(x) | varchar | Converts x to uppercase. |
# Array operators
Timestream for LiveAnalytics supports the following array operators.
| Operator | Description |
| --- | --- |
| [] | Access an element of an array where the first index starts at 1. |
| \$1\$1 | Concatenate an array with another array or element of the same type. |
# Array functions
Timestream for LiveAnalytics supports the following array functions.
| Function | Output data type | Description |
| --- | --- | --- |
| array\$1distinct(x) | array | Remove duplicate values from the array x. SELECT array_distinct(ARRAY[1,2,2,3])
Example result: `[ 1,2,3 ]` |
| array\$1intersect(x, y) | array | Returns an array of the elements in the intersection of x and y, without duplicates. SELECT array_intersect(ARRAY[1,2,3], ARRAY[3,4,5])
Example result: `[ 3 ]` |
| array\$1union(x, y) | array | Returns an array of the elements in the union of x and y, without duplicates. SELECT array_union(ARRAY[1,2,3], ARRAY[3,4,5])
Example result: `[ 1,2,3,4,5 ]` |
| array\$1except(x, y) | array | Returns an array of elements in x but not in y, without duplicates. SELECT array_except(ARRAY[1,2,3], ARRAY[3,4,5])
Example result: `[ 1,2 ]` |
| array\$1join(x, delimiter, null\$1replacement) | varchar | Concatenates the elements of the given array using the delimiter and an optional string to replace nulls. SELECT array_join(ARRAY[1,2,3], ';', '')
Example result: `1;2;3` |
| array\$1max(x) | same as array elements | Returns the maximum value of input array. SELECT array_max(ARRAY[1,2,3])
Example result: `3` |
| array\$1min(x) | same as array elements | Returns the minimum value of input array. SELECT array_min(ARRAY[1,2,3])
Example result: `1` |
| array\$1position(x, element) | bigint | Returns the position of the first occurrence of the element in array x (or 0 if not found). SELECT array_position(ARRAY[3,4,5,9], 5)
Example result: `3` |
| array\$1remove(x, element) | array | Remove all elements that equal element from array x. SELECT array_remove(ARRAY[3,4,5,9], 4)
Example result: `[ 3,5,9 ]` |
| array\$1sort(x) | array | Sorts and returns the array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array. SELECT array_sort(ARRAY[6,8,2,9,3])
Example result: `[ 2,3,6,8,9 ]` |
| arrays\$1overlap(x, y) | boolean | Tests if arrays x and y have any non-null elements in common. Returns null if there are no non-null elements in common but either array contains null. SELECT arrays_overlap(ARRAY[6,8,2,9,3], ARRAY[6,8])
Example result: `true` |
| cardinality(x) | bigint | Returns the size of the array x. SELECT cardinality(ARRAY[6,8,2,9,3])
Example result: `5` |
| concat(array1, array2, ..., arrayN) | array | Concatenates the arrays array1, array2, ..., arrayN. SELECT concat(ARRAY[6,8,2,9,3], ARRAY[11,32], ARRAY[6,8,2,0,14])
Example result: `[ 6,8,2,9,3,11,32,6,8,2,0,14 ]` |
| element\$1at(array(E), index) | E | Returns element of array at given index. If index < 0, element\$1at accesses elements from the last to the first. SELECT element_at(ARRAY[6,8,2,9,3], 1)
Example result: `6` |
| repeat(element, count) | array | Repeat element for count times. SELECT repeat(1, 3)
Example result: `[ 1,1,1 ]` |
| reverse(x) | array | Returns an array which has the reversed order of array x. SELECT reverse(ARRAY[6,8,2,9,3])
Example result: `[ 3,9,2,8,6 ]` |
| sequence(start, stop) | array(bigint) | Generate a sequence of integers from start to stop, incrementing by 1 if start is less than or equal to stop, otherwise -1. SELECT sequence(3, 8)
Example result: `[ 3,4,5,6,7,8 ]` |
| sequence(start, stop, step) | array(bigint) | Generate a sequence of integers from start to stop, incrementing by step. SELECT sequence(3, 15, 2)
Example result: `[ 3,5,7,9,11,13,15 ]` |
| sequence(start, stop) | array(timestamp) | Generate a sequence of timestamps from start date to stop date, incrementing by 1 day. SELECT sequence('2023-04-02 19:26:12.941000000', '2023-04-06 19:26:12.941000000', 1d) Example result: `[ 2023-04-02 19:26:12.941000000,2023-04-03 19:26:12.941000000,2023-04-04 19:26:12.941000000,2023-04-05 19:26:12.941000000,2023-04-06 19:26:12.941000000 ]` |
| sequence(start, stop, step) | array(timestamp) | Generate a sequence of timestamps from start to stop, incrementing by step. The data type of step is interval. SELECT sequence('2023-04-02 19:26:12.941000000', '2023-04-10 19:26:12.941000000', 2d) Example result: `[ 2023-04-02 19:26:12.941000000,2023-04-04 19:26:12.941000000,2023-04-06 19:26:12.941000000,2023-04-08 19:26:12.941000000,2023-04-10 19:26:12.941000000 ]` |
| shuffle(x) | array | Generate a random permutation of the given array x. SELECT shuffle(ARRAY[6,8,2,9,3])
Example result: `[ 6,3,2,9,8 ]` |
| slice(x, start, length) | array | Subsets array x starting from index start (or starting from the end if start is negative) with a length of length. SELECT slice(ARRAY[6,8,2,9,3], 1, 3)
Example result: `[ 6,8,2 ]` |
| zip(array1, array2[, ...]) | array(row) | Merges the given arrays, element-wise, into a single array of rows. If the arguments have an uneven length, missing values are filled with NULL. SELECT zip(ARRAY[6,8,2,9,3], ARRAY[15,24])
Example result: `[ ( 6, 15 ),( 8, 24 ),( 2, - ),( 9, - ),( 3, - ) ]` |
# Bitwise functions
Timestream for LiveAnalytics supports the following bitwise functions.
| Function | Output data type | Description |
| --- | --- | --- |
| bit\$1count(bigint, bigint) | bigint (two's complement) | Returns the count of bits in the first bigint parameter where the second parameter is a bit signed integer such as 8 or 64. SELECT bit_count(19, 8)
Example result: `3` SELECT bit_count(19, 2)
Example result: `Number must be representable with the bits specified. 19 can not be represented with 2 bits` |
| bitwise\$1and(bigint, bigint) | bigint (two's complement) | Returns the bitwise AND of the bigint parameters. SELECT bitwise_and(12, 7)
Example result: `4` |
| bitwise\$1not(bigint) | bigint (two's complement) | Returns the bitwise NOT of the bigint parameter. SELECT bitwise_not(12)
Example result: `-13` |
| bitwise\$1or(bigint, bigint) | bigint (two's complement) | Returns the bitwise OR of the bigint parameters. SELECT bitwise_or(12, 7)
Example result: `15` |
| bitwise\$1xor(bigint, bigint) | bigint (two's complement) | Returns the bitwise XOR of the bigint parameters. SELECT bitwise_xor(12, 7)
Example result: `11` |
# Regular expression functions
The regular expression functions in Timestream for LiveAnalytics support the [Java pattern syntax](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html). Timestream for LiveAnalytics supports the following regular expression functions.
| Function | Output data type | Description |
| --- | --- | --- |
| regexp\$1extract\$1all(string, pattern) | array(varchar) | Returns the substring(s) matched by the regular expression pattern in string. SELECT regexp_extract_all('example expect complex', 'ex\w') Example result: `[ exa,exp ]` |
| regexp\$1extract\$1all(string, pattern, group) | array(varchar) | Finds all occurrences of the regular expression pattern in string and returns the [capturing group number](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html#gnumber) group. SELECT regexp_extract_all('example expect complex', '(ex)(\w)', 2) Example result: `[ a,p ]` |
| regexp\$1extract(string, pattern) | varchar | Returns the first substring matched by the regular expression pattern in string. SELECT regexp_extract('example expect', 'ex\w') Example result: `exa` |
| regexp\$1extract(string, pattern, group) | varchar | Finds the first occurrence of the regular expression pattern in string and returns the [capturing group number](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html#gnumber) group. SELECT regexp_extract('example expect', '(ex)(\w)', 2) Example result: `a` |
| regexp\$1like(string, pattern) | boolean | Evaluates the regular expression pattern and determines if it is contained within string. This function is similar to the LIKE operator, except that the pattern only needs to be contained within string, rather than needing to match all of string. In other words, this performs a contains operation rather than a match operation. You can match the entire string by anchoring the pattern using ^ and \$1. SELECT regexp_like('example', 'ex') Example result: `true` |
| regexp\$1replace(string, pattern) | varchar | Removes every instance of the substring matched by the regular expression pattern from string. SELECT regexp_replace('example expect', 'expect') Example result: `example` |
| regexp\$1replace(string, pattern, replacement) | varchar | Replaces every instance of the substring matched by the regex pattern in string with replacement. Capturing groups can be referenced in replacement using \$1g for a numbered group or \$1\$1name\$1 for a named group. A dollar sign (\$1) may be included in the replacement by escaping it with a backslash (\$1\$1). SELECT regexp_replace('example expect', 'expect', 'surprise') Example result: `example surprise` |
| regexp\$1replace(string, pattern, function) | varchar | Replaces every instance of the substring matched by the regular expression pattern in string using function. The [lambda expression](https://prestodb.io/docs/current/functions/lambda.html) function is invoked for each match with the capturing groups passed as an array. Capturing group numbers start at one; there is no group for the entire match (if you need this, surround the entire expression with parenthesis). SELECT regexp_replace('example', '(\w)', x -> upper(x[1])) Example result: `EXAMPLE` |
| regexp\$1split(string, pattern) | array(varchar) | Splits string using the regular expression pattern and returns an array. Trailing empty strings are preserved. SELECT regexp_split('example', 'x') Example result: `[ e,ample ]` |
# Date / time operators
**Note**
Timestream for LiveAnalytics does not support negative time values. Any operation resulting in negative time results in error.
Timestream for LiveAnalytics supports the following operations on `timestamps`, `dates`, and `intervals`.
| Operator | Description |
| --- | --- |
| \$1 | Addition |
| - | Subtraction |
**Topics**
+ [Operations](#date-time-operators-operations)
+ [Addition](#date-time-operators-addition)
+ [Subtraction](#date-time-operators-subtraction)
## Operations
The result type of an operation is based on the operands. Interval literals such as `1day` and `3s` can be used.
```
SELECT date '2022-05-21' + interval '2' day
```
```
SELECT date '2022-05-21' + 2d
```
```
SELECT date '2022-05-21' + 2day
```
Example result for each: `2022-05-23`
Interval units include `second`, `minute`, `hour`, `day`, `week`, `month`, and `year`. But in some cases not all are applicable. For example seconds, minutes, and hours can not be added to or subtracted from a date.
```
SELECT interval '4' year + interval '2' month
```
Example result: `4-2`
```
SELECT typeof(interval '4' year + interval '2' month)
```
Example result: `interval year to month`
Result type of interval operations may be `'interval year to month'` or `'interval day to second'` depending on the operands. Intervals can be added to or subtracted from `dates` and `timestamps`. But a `date` or `timestamp` cannot be added to or subtracted from a `date` or `timestamp`. To find intervals or durations related to dates or timestamps, see `date_diff` and related functions in [Interval and duration](date-time-functions.md#date-time-functions-interval-duration).
## Addition
**Example**
```
SELECT date '2022-05-21' + interval '2' day
```
Example result: `2022-05-23`
**Example**
```
SELECT typeof(date '2022-05-21' + interval '2' day)
```
Example result: `date`
**Example**
```
SELECT interval '2' year + interval '4' month
```
Example result: `2-4`
**Example**
```
SELECT typeof(interval '2' year + interval '4' month)
```
Example result: `interval year to month`
## Subtraction
**Example**
```
SELECT timestamp '2022-06-17 01:00' - interval '7' hour
```
Example result: `2022-06-16 18:00:00.000000000`
**Example**
```
SELECT typeof(timestamp '2022-06-17 01:00' - interval '7' hour)
```
Example result: `timestamp`
**Example**
```
SELECT interval '6' day - interval '4' hour
```
Example result: `5 20:00:00.000000000`
**Example**
```
SELECT typeof(interval '6' day - interval '4' hour)
```
Example result: `interval day to second`
# Date / time functions
**Note**
Timestream for LiveAnalytics does not support negative time values. Any operation resulting in negative time results in error.
Timestream for LiveAnalytics uses UTC timezone for date and time. Timestream supports the following functions for date and time.
**Topics**
+ [General and conversion](#date-time-functions-general)
+ [Interval and duration](#date-time-functions-interval-duration)
+ [Formatting and parsing](#date-time-functions-formatting-parsing)
+ [Extraction](#date-time-functions-extraction)
## General and conversion
Timestream for LiveAnalytics supports the following general and conversion functions for date and time.
| Function | Output data type | Description |
| --- | --- | --- |
| current\$1date | date | Returns current date in UTC. No parentheses used. SELECT current_date
Example result: `2022-07-07` This is also a reserved keyword. For a list of reserved keywords, see [Reserved keywords](ts-limits.md#limits.reserved). |
| current\$1time | time | Returns current time in UTC. No parentheses used. SELECT current_time
Example result: `17:41:52.827000000` This is also a reserved keyword. For a list of reserved keywords, see [Reserved keywords](ts-limits.md#limits.reserved). |
| current\$1timestamp or now() | timestamp | Returns current timestamp in UTC. SELECT current_timestamp
Example result: `2022-07-07 17:42:32.939000000` This is also a reserved keyword. For a list of reserved keywords, see [Reserved keywords](ts-limits.md#limits.reserved). |
| current\$1timezone() | varchar The value will be 'UTC.' | Timestream uses UTC timezone for date and time. SELECT current_timezone()
Example result: `UTC` |
| date(varchar(x)), date(timestamp) | date | SELECT date(TIMESTAMP '2022-07-07 17:44:43.771000000')
Example result: `2022-07-07` |
| last\$1day\$1of\$1month(timestamp), last\$1day\$1of\$1month(date) | date | SELECT last_day_of_month(TIMESTAMP '2022-07-07 17:44:43.771000000')
Example result: `2022-07-31` |
| from\$1iso8601\$1timestamp(string) | timestamp | Parses the ISO 8601 timestamp into internal timestamp format. SELECT from_iso8601_timestamp('2022-06-17T08:04:05.000000000+05:00') Example result: `2022-06-17 03:04:05.000000000` |
| from\$1iso8601\$1date(string) | date | Parses the ISO 8601 date string into internal timestamp format for UTC 00:00:00 of the specified date. SELECT from_iso8601_date('2022-07-17') Example result: `2022-07-17` |
| to\$1iso8601(timestamp), to\$1iso8601(date) | varchar | Returns an ISO 8601 formatted string for the input. SELECT to_iso8601(from_iso8601_date('2022-06-17')) Example result: `2022-06-17` |
| from\$1milliseconds(bigint) | timestamp | SELECT from_milliseconds(1)
Example result: `1970-01-01 00:00:00.001000000` |
| from\$1nanoseconds(bigint) | timestamp | select from_nanoseconds(300000001)
Example result: `1970-01-01 00:00:00.300000001` |
| from\$1unixtime(double) | timestamp | Returns a timestamp which corresponds to the provided unixtime. SELECT from_unixtime(1)
Example result: `1970-01-01 00:00:01.000000000` |
| localtime | time | Returns current time in UTC. No parentheses used. SELECT localtime
Example result: `17:58:22.654000000` This is also a reserved keyword. For a list of reserved keywords, see [Reserved keywords](ts-limits.md#limits.reserved). |
| localtimestamp | timestamp | Returns current timestamp in UTC. No parentheses used. SELECT localtimestamp
Example result: `2022-07-07 17:59:04.368000000` This is also a reserved keyword. For a list of reserved keywords, see [Reserved keywords](ts-limits.md#limits.reserved). |
| to\$1milliseconds(interval day to second), to\$1milliseconds(timestamp) | bigint | SELECT to_milliseconds(INTERVAL '2' DAY + INTERVAL '3' HOUR)
Example result: `183600000` SELECT to_milliseconds(TIMESTAMP '2022-06-17 17:44:43.771000000')
Example result: `1655487883771` |
| to\$1nanoseconds(interval day to second), to\$1nanoseconds(timestamp) | bigint | SELECT to_nanoseconds(INTERVAL '2' DAY + INTERVAL '3' HOUR)
Example result: `183600000000000` SELECT to_nanoseconds(TIMESTAMP '2022-06-17 17:44:43.771000678')
Example result: `1655487883771000678` |
| to\$1unixtime(timestamp) | double | Returns unixtime for the provided timestamp. SELECT to_unixtime('2022-06-17 17:44:43.771000000') Example result: `1.6554878837710001E9` |
| date\$1trunc(unit, timestamp) | timestamp | Returns the timestamp truncated to unit, where unit is one of [second, minute, hour, day, week, month, quarter, or year]. SELECT date_trunc('minute', TIMESTAMP '2022-06-17 17:44:43.771000000') Example result: `2022-06-17 17:44:00.000000000` |
## Interval and duration
Timestream for LiveAnalytics supports the following interval and duration functions for date and time.
| Function | Output data type | Description |
| --- | --- | --- |
| date\$1add(unit, bigint, date), date\$1add(unit, bigint, time), date\$1add(varchar(x), bigint, timestamp) | timestamp | Adds a bigint of units, where unit is one of [second, minute, hour, day, week, month, quarter, or year]. SELECT date_add('hour', 9, TIMESTAMP '2022-06-17 00:00:00') Example result: `2022-06-17 09:00:00.000000000` |
| date\$1diff(unit, date, date) , date\$1diff(unit, time, time) , date\$1diff(unit, timestamp, timestamp) | bigint | Returns a difference, where unit is one of [second, minute, hour, day, week, month, quarter, or year]. SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02') Example result: `1` |
| parse\$1duration(string) | interval | Parses the input string to return an `interval` equivalent. SELECT parse_duration('42.8ms') Example result: `0 00:00:00.042800000` SELECT typeof(parse_duration('42.8ms')) Example result: `interval day to second` |
| bin(timestamp, interval) | timestamp | Rounds down the `timestamp` parameter's integer value to the nearest multiple of the `interval` parameter's integer value. The meaning of this return value may not be obvious. It is calculated using integer arithmetic first by dividing the timestamp integer by the interval integer and then by multiplying the result by the interval integer. Keeping in mind that a timestamp specifies a UTC point in time as a number of fractions of a second elapsed since the POSIX epoch (January 1, 1970), the return value will seldom align with calendar units. For example, if you specify an interval of 30 days, all the days since the epoch are divided into 30-day increments, and the start of the most recent 30-day increment is returned, which has no relationship to calendar months. Here are some examples: bin(TIMESTAMP '2022-06-17 10:15:20', 5m) ==> 2022-06-17 10:15:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 1d) ==> 2022-06-17 00:00:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 10day) ==> 2022-06-17 00:00:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 30day) ==> 2022-05-28 00:00:00.000000000
|
| ago(interval) | timestamp | Returns the value corresponding to current\$1timestamp `interval`. SELECT ago(1d)
Example result: `2022-07-06 21:08:53.245000000` |
| interval literals such as 1h, 1d, and 30m | interval | Interval literals are a convenience for parse\$1duration(string). For example, `1d` is the same as `parse_duration('1d')`. This allows the use of the literals wherever an interval is used. For example, `ago(1d)` and `bin(, 1m)`. |
Some interval literals act as shorthand for parse\$1duration. For example, `parse_duration('1day')`, `1day`, `parse_duration('1d')`, and `1d` each return `1 00:00:00.000000000` where the type is `interval day to second`. Space is allowed in the format provided to `parse_duration`. For example `parse_duration('1day')` also returns `00:00:00.000000000`. But `1 day` is not an interval literal.
The units related to `interval day to second` are ns, nanosecond, us, microsecond, ms, millisecond, s, second, m, minute, h, hour, d, and day.
There is also `interval year to month`. The units related to interval year to month are y, year, and month. For example, `SELECT 1year` returns `1-0`. `SELECT 12month` also returns `1-0`. `SELECT 8month` returns `0-8`.
Although the unit of `quarter` is also available for some functions such as `date_trunc` and `date_add`, `quarter` is not available as part of an interval literal.
## Formatting and parsing
Timestream for LiveAnalytics supports the following formatting and parsing functions for date and time.
| Function | Output data type | Description |
| --- | --- | --- |
| date\$1format(timestamp, varchar(x)) | varchar | For more information about the format specifiers used by this function, see [https://trino.io/docs/current/functions/datetime.html\$1mysql-date-functions](https://trino.io/docs/current/functions/datetime.html#mysql-date-functions) SELECT date_format(TIMESTAMP '2019-10-20 10:20:20', '%Y-%m-%d %H:%i:%s')
Example result: `2019-10-20 10:20:20` |
| date\$1parse(varchar(x), varchar(y)) | timestamp | For more information about the format specifiers used by this function, see [https://trino.io/docs/current/functions/datetime.html\$1mysql-date-functions](https://trino.io/docs/current/functions/datetime.html#mysql-date-functions) SELECT date_parse('2019-10-20 10:20:20', '%Y-%m-%d %H:%i:%s') Example result: `2019-10-20 10:20:20.000000000` |
| format\$1datetime(timestamp, varchar(x)) | varchar | For more information about the format string used by this function, see [http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html](http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) SELECT format_datetime(parse_datetime('1968-01-13 12', 'yyyy-MM-dd HH'), 'yyyy-MM-dd HH') Example result: `1968-01-13 12` |
| parse\$1datetime(varchar(x), varchar(y)) | timestamp | For more information about the format string used by this function, see [http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html](http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) SELECT parse_datetime('2019-12-29 10:10 PST', 'uuuu-LL-dd HH:mm z') Example result: `2019-12-29 18:10:00.000000000` |
## Extraction
Timestream for LiveAnalytics supports the following extraction functions for date and time. The extract function is the basis for the remaining convenience functions.
| Function | Output data type | Description |
| --- | --- | --- |
| extract | bigint | Extracts a field from a timestamp, where field is one of [YEAR, QUARTER, MONTH, WEEK, DAY, DAY\$1OF\$1MONTH, DAY\$1OF\$1WEEK, DOW, DAY\$1OF\$1YEAR, DOY, YEAR\$1OF\$1WEEK, YOW, HOUR, MINUTE, or SECOND]. SELECT extract(YEAR FROM '2019-10-12 23:10:34.000000000')
Example result: `2019` |
| day(timestamp), day(date), day(interval day to second) | bigint | SELECT day('2019-10-12 23:10:34.000000000') Example result: `12` |
| day\$1of\$1month(timestamp), day\$1of\$1month(date), day\$1of\$1month(interval day to second) | bigint | SELECT day_of_month('2019-10-12 23:10:34.000000000') Example result: `12` |
| day\$1of\$1week(timestamp), day\$1of\$1week(date) | bigint | SELECT day_of_week('2019-10-12 23:10:34.000000000') Example result: `6` |
| day\$1of\$1year(timestamp), day\$1of\$1year(date) | bigint | SELECT day_of_year('2019-10-12 23:10:34.000000000') Example result: `285` |
| dow(timestamp), dow(date) | bigint | Alias for day\$1of\$1week |
| doy(timestamp), doy(date) | bigint | Alias for day\$1of\$1year |
| hour(timestamp), hour(time), hour(interval day to second) | bigint | SELECT hour('2019-10-12 23:10:34.000000000') Example result: `23` |
| millisecond(timestamp), millisecond(time), millisecond(interval day to second) | bigint | SELECT millisecond('2019-10-12 23:10:34.000000000') Example result: `0` |
| minute(timestamp), minute(time), minute(interval day to second) | bigint | SELECT minute('2019-10-12 23:10:34.000000000') Example result: `10` |
| month(timestamp), month(date), month(interval year to month) | bigint | SELECT month('2019-10-12 23:10:34.000000000') Example result: `10` |
| nanosecond(timestamp), nanosecond(time), nanosecond(interval day to second) | bigint | SELECT nanosecond(current_timestamp)
Example result: `162000000` |
| quarter(timestamp), quarter(date) | bigint | SELECT quarter('2019-10-12 23:10:34.000000000') Example result: `4` |
| second(timestamp), second(time), second(interval day to second) | bigint | SELECT second('2019-10-12 23:10:34.000000000') Example result: `34` |
| week(timestamp), week(date) | bigint | SELECT week('2019-10-12 23:10:34.000000000') Example result: `41` |
| week\$1of\$1year(timestamp), week\$1of\$1year(date) | bigint | Alias for week |
| year(timestamp), year(date), year(interval year to month) | bigint | SELECT year('2019-10-12 23:10:34.000000000') Example result: `2019` |
| year\$1of\$1week(timestamp), year\$1of\$1week(date) | bigint | SELECT year_of_week('2019-10-12 23:10:34.000000000') Example result: `2019` |
| yow(timestamp), yow(date) | bigint | Alias for year\$1of\$1week |
# Aggregate functions
Timestream for LiveAnalytics supports the following aggregate functions.
| Function | Output data type | Description |
| --- | --- | --- |
| arbitrary(x) | [same as input] | Returns an arbitrary non-null value of x, if one exists. SELECT arbitrary(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `1` |
| array\$1agg(x) | array<[same as input] | Returns an array created from the input x elements. SELECT array_agg(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `[ 1,2,3,4 ]` |
| avg(x) | double | Returns the average (arithmetic mean) of all input values. SELECT avg(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `2.5` |
| bool\$1and(boolean) every(boolean) | boolean | Returns TRUE if every input value is TRUE, otherwise FALSE. SELECT bool_and(t.c) FROM (VALUES true, true, false, true) AS t(c)
Example result: `false` |
| bool\$1or(boolean) | boolean | Returns TRUE if any input value is TRUE, otherwise FALSE. SELECT bool_or(t.c) FROM (VALUES true, true, false, true) AS t(c)
Example result: `true` |
| count(\$1) count(x) | bigint | count(\$1) returns the number of input rows. count(x) returns the number of non-null input values. SELECT count(t.c) FROM (VALUES true, true, false, true) AS t(c)
Example result: `4` |
| count\$1if(x) | bigint | Returns the number of TRUE input values. SELECT count_if(t.c) FROM (VALUES true, true, false, true) AS t(c)
Example result: `3` |
| geometric\$1mean(x) | double | Returns the geometric mean of all input values. SELECT geometric_mean(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `2.213363839400643` |
| max\$1by(x, y) | [same as x] | Returns the value of x associated with the maximum value of y over all input values. SELECT max_by(t.c1, t.c2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) Example result: `d` |
| max\$1by(x, y, n) | array<[same as x]> | Returns n values of x associated with the n largest of all input values of y in descending order of y. SELECT max_by(t.c1, t.c2, 2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) Example result: `[ d,c ]` |
| min\$1by(x, y) | [same as x] | Returns the value of x associated with the minimum value of y over all input values. SELECT min_by(t.c1, t.c2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) Example result: `a` |
| min\$1by(x, y, n) | array<[same as x]> | Returns n values of x associated with the n smallest of all input values of y in ascending order of y. SELECT min_by(t.c1, t.c2, 2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) Example result: `[ a,b ]` |
| max(x) | [same as input] | Returns the maximum value of all input values. SELECT max(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `4` |
| max(x, n) | array<[same as x]> | Returns n largest values of all input values of x. SELECT max(t.c, 2) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `[ 4,3 ]` |
| min(x) | [same as input] | Returns the minimum value of all input values. SELECT min(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `1` |
| min(x, n) | array<[same as x]> | Returns n smallest values of all input values of x. SELECT min(t.c, 2) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `[ 1,2 ]` |
| sum(x) | [same as input] | Returns the sum of all input values. SELECT sum(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `10` |
| bitwise\$1and\$1agg(x) | bigint | Returns the bitwise AND of all input values in 2s complement representation. SELECT bitwise_and_agg(t.c) FROM (VALUES 1, -3) AS t(c)
Example result: `1` |
| bitwise\$1or\$1agg(x) | bigint | Returns the bitwise OR of all input values in 2s complement representation. SELECT bitwise_or_agg(t.c) FROM (VALUES 1, -3) AS t(c)
Example result: `-3` |
| approx\$1distinct(x) | bigint | Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null. This function should produce a standard error of 2.3%, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. SELECT approx_distinct(t.c) FROM (VALUES 1, 2, 3, 4, 8) AS t(c)
Example result: `5` |
| approx\$1distinct(x, e) | bigint | Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null. This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range of [0.0040625, 0.26000]. SELECT approx_distinct(t.c, 0.2) FROM (VALUES 1, 2, 3, 4, 8) AS t(c)
Example result: `5` |
| approx\$1percentile(x, percentage) | [same as x] | Returns the approximate percentile for all input values of x at the given percentage. The value of percentage must be between zero and one and must be constant for all input rows. SELECT approx_percentile(t.c, 0.4) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `2` |
| approx\$1percentile(x, percentages) | array<[same as x]> | Returns the approximate percentile for all input values of x at each of the specified percentages. Each element of the percentages array must be between zero and one, and the array must be constant for all input rows. SELECT approx_percentile(t.c, ARRAY[0.1, 0.8, 0.8]) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `[ 1,4,4 ]` |
| approx\$1percentile(x, w, percentage) | [same as x] | Returns the approximate weighed percentile for all input values of x using the per-item weight w at the percentage p. The weight must be an integer value of at least one. It is effectively a replication count for the value x in the percentile set. The value of p must be between zero and one and must be constant for all input rows. SELECT approx_percentile(t.c, 1, 0.1) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `1` |
| approx\$1percentile(x, w, percentages) | array<[same as x]> | Returns the approximate weighed percentile for all input values of x using the per-item weight w at each of the given percentages specified in the array. The weight must be an integer value of at least one. It is effectively a replication count for the value x in the percentile set. Each element of the array must be between zero and one, and the array must be constant for all input rows. SELECT approx_percentile(t.c, 1, ARRAY[0.1, 0.8, 0.8]) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `[ 1,4,4 ]` |
| approx\$1percentile(x, w, percentage, accuracy) | [same as x] | Returns the approximate weighed percentile for all input values of x using the per-item weight w at the percentage p, with a maximum rank error of accuracy. The weight must be an integer value of at least one. It is effectively a replication count for the value x in the percentile set. The value of p must be between zero and one and must be constant for all input rows. The accuracy must be a value greater than zero and less than one, and it must be constant for all input rows. SELECT approx_percentile(t.c, 1, 0.1, 0.5) FROM (VALUES 1, 2, 3, 4) AS t(c)
Example result: `1` |
| corr(y, x) | double | Returns correlation coefficient of input values. SELECT corr(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
Example result: `1.0` |
| covar\$1pop(y, x) | double | Returns the population covariance of input values. SELECT covar_pop(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
Example result: `1.25` |
| covar\$1samp(y, x) | double | Returns the sample covariance of input values. SELECT covar_samp(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
Example result: `1.6666666666666667` |
| regr\$1intercept(y, x) | double | Returns linear regression intercept of input values. y is the dependent value. x is the independent value. SELECT regr_intercept(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
Example result: `0.0` |
| regr\$1slope(y, x) | double | Returns linear regression slope of input values. y is the dependent value. x is the independent value. SELECT regr_slope(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
Example result: `1.0` |
| skewness(x) | double | Returns the skewness of all input values. SELECT skewness(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
Example result: `0.8978957037987335` |
| stddev\$1pop(x) | double | Returns the population standard deviation of all input values. SELECT stddev_pop(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
Example result: `2.4166091947189146` |
| stddev\$1samp(x) stddev(x) | double | Returns the sample standard deviation of all input values. SELECT stddev_samp(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
Example result: `2.701851217221259` |
| var\$1pop(x) | double | Returns the population variance of all input values. SELECT var_pop(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
Example result: `5.840000000000001` |
| var\$1samp(x) variance(x) | double | Returns the sample variance of all input values. SELECT var_samp(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
Example result: `7.300000000000001` |
# Window functions
Window functions perform calculations across rows of the query result. They run after the HAVING clause but before the ORDER BY clause. Invoking a window function requires special syntax using the OVER clause to specify the window. A window has three components:
+ The partition specification, which separates the input rows into different partitions. This is analogous to how the GROUP BY clause separates rows into different groups for aggregate functions.
+ The ordering specification, which determines the order in which input rows will be processed by the window function.
+ The window frame, which specifies a sliding window of rows to be processed by the function for a given row. If the frame is not specified, it defaults to RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This frame contains all rows from the start of the partition up to the last peer of the current row.
All Aggregate Functions can be used as window functions by adding the OVER clause. The aggregate function is computed for each row over the rows within the current row's window frame. In addition to aggregate functions, Timestream for LiveAnalytics supports the following ranking and value functions.
| Function | Output data type | Description |
| --- | --- | --- |
| cume\$1dist() | bigint | Returns the cumulative distribution of a value in a group of values. The result is the number of rows preceding or peer with the row in the window ordering of the window partition divided by the total number of rows in the window partition. Thus, any tie values in the ordering will evaluate to the same distribution value. |
| dense\$1rank() | bigint | Returns the rank of a value in a group of values. This is similar to rank(), except that tie values do not produce gaps in the sequence. |
| ntile(n) | bigint | Divides the rows for each window partition into n buckets ranging from 1 to at most n. Bucket values will differ by at most 1. If the number of rows in the partition does not divide evenly into the number of buckets, then the remainder values are distributed one per bucket, starting with the first bucket. |
| percent\$1rank() | double | Returns the percentage ranking of a value in group of values. The result is (r - 1) / (n - 1) where r is the rank() of the row and n is the total number of rows in the window partition. |
| rank() | bigint | Returns the rank of a value in a group of values. The rank is one plus the number of rows preceding the row that are not peer with the row. Thus, tie values in the ordering will produce gaps in the sequence. The ranking is performed for each window partition. |
| row\$1number() | bigint | Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition. |
| first\$1value(x) | [same as input] | Returns the first value of the window. This function is scoped to the window frame. The function takes an expression or target as its parameter. |
| last\$1value(x) | [same as input] | Returns the last value of the window. This function is scoped to the window frame. The function takes an expression or target as its parameter. |
| nth\$1value(x, offset) | [same as input] | Returns the value at the specified offset from beginning the window. Offsets start at 1. The offset can be any scalar expression. If the offset is null or greater than the number of values in the window, null is returned. It is an error for the offset to be zero or negative. The function takes an expression or target as its first parameter. |
| lead(x[, offset[, default\$1value]]) | [same as input] | Returns the value at offset rows after the current row in the window. Offsets start at 0, which is the current row. The offset can be any scalar expression. The default offset is 1. If the offset is null or larger than the window, the default\$1value is returned, or if it is not specified null is returned. The function takes an expression or target as its first parameter. |
| lag(x[, offset[, default\$1value]]) | [same as input] | Returns the value at offset rows before the current row in the window Offsets start at 0, which is the current row. The offset can be any scalar expression. The default offset is 1. If the offset is null or larger than the window, the default\$1value is returned, or if it is not specified null is returned. The function takes an expression or target as its first parameter. |
# Sample queries
This section includes example use cases of Timestream for LiveAnalytics's query language.
**Topics**
+ [Simple queries](sample-queries.basic-scenarios.md)
+ [Queries with time series functions](sample-queries.devops-scenarios.md)
+ [Queries with aggregate functions](sample-queries.iot-scenarios.md)
# Simple queries
The following gets the 10 most recently added data points for a table.
```
SELECT * FROM .
ORDER BY time DESC
LIMIT 10
```
The following gets the 5 oldest data points for a specific measure.
```
SELECT * FROM .
WHERE measure_name = ''
ORDER BY time ASC
LIMIT 5
```
The following works with nanosecond granularity timestamps.
```
SELECT now() AS time_now
, now() - (INTERVAL '12' HOUR) AS twelve_hour_earlier -- Compatibility with ANSI SQL
, now() - 12h AS also_twelve_hour_earlier -- Convenient time interval literals
, ago(12h) AS twelve_hours_ago -- More convenience with time functionality
, bin(now(), 10m) AS time_binned -- Convenient time binning support
, ago(50ns) AS fifty_ns_ago -- Nanosecond support
, now() + (1h + 50ns) AS hour_fifty_ns_future
```
Measure values for multi-measure records are identified by column name. Measure values for single-measure records are identified by `measure_value::`, where `` is one of `double`, `bigint`, `boolean`, or `varchar` as described in [Supported data types](supported-data-types.md). For more information about how measure values are modeled, see [Single table vs. multiple tables](https://docs.aws.amazon.com/timestream/latest/developerguide/data-modeling.html#data-modeling-multiVsinglerecords).
The following retrieves values for a measure called `speed` from multi-measure records with a `measure_name` of `IoTMulti-stats`.
```
SELECT speed FROM . where measure_name = 'IoTMulti-stats'
```
The following retrieves `double` values from single-measure records with a `measure_name` of `load`.
```
SELECT measure_value::double FROM . WHERE measure_name = 'load'
```
# Queries with time series functions
**Topics**
+ [Example dataset and queries](#sample-queries.devops-scenarios.example)
## Example dataset and queries
You can use Timestream for LiveAnalytics to understand and improve the performance and availability of your services and applications. Below is an example table and sample queries run on that table.
The table `ec2_metrics` stores telemetry data, such as CPU utilization and other metrics from EC2 instances. You can view the table below.
| Time | region | az | Hostname | measure\$1name | measure\$1value::double | measure\$1value::bigint |
| --- | --- | --- | --- | --- | --- | --- |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\$1utilization | 35.1 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | memory\$1utilization | 55.3 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1in | null | 1,500 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1out | null | 6,700 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\$1utilization | 38.5 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | memory\$1utilization | 58.4 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1in | null | 23,000 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1out | null | 12,000 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\$1utilization | 45.0 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | memory\$1utilization | 65.8 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1in | null | 15,000 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1out | null | 836,000 |
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\$1utilization | 55.2 | null |
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | memory\$1utilization | 75.0 | null |
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1in | null | 1,245 |
| 2019-12-04 19:00:05.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1out | null | 68,432 |
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\$1utilization | 65.6 | null |
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | memory\$1utilization | 85.3 | null |
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1in | null | 1,245 |
| 2019-12-04 19:00:08.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1out | null | 68,432 |
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\$1utilization | 12.1 | null |
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | memory\$1utilization | 32.0 | null |
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1in | null | 1,400 |
| 2019-12-04 19:00:20.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1out | null | 345 |
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\$1utilization | 15.3 | null |
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | memory\$1utilization | 35.4 | null |
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1in | null | 23 |
| 2019-12-04 19:00:10.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1out | null | 0 |
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\$1utilization | 44.0 | null |
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | memory\$1utilization | 64.2 | null |
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1in | null | 1,450 |
| 2019-12-04 19:00:16.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1out | null | 200 |
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\$1utilization | 66.4 | null |
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | memory\$1utilization | 86.3 | null |
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1in | null | 300 |
| 2019-12-04 19:00:40.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1out | null | 423 |
Find the average, p90, p95, and p99 CPU utilization for a specific EC2 host over the past 2 hours:
```
SELECT region, az, hostname, BIN(time, 15s) AS binned_timestamp,
ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization,
ROUND(APPROX_PERCENTILE(measure_value::double, 0.9), 2) AS p90_cpu_utilization,
ROUND(APPROX_PERCENTILE(measure_value::double, 0.95), 2) AS p95_cpu_utilization,
ROUND(APPROX_PERCENTILE(measure_value::double, 0.99), 2) AS p99_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY region, hostname, az, BIN(time, 15s)
ORDER BY binned_timestamp ASC
```
Identify EC2 hosts with CPU utilization that is higher by 10 % or more compared to the average CPU utilization of the entire fleet for the past 2 hours:
```
WITH avg_fleet_utilization AS (
SELECT COUNT(DISTINCT hostname) AS total_host_count, AVG(measure_value::double) AS fleet_avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND time > ago(2h)
), avg_per_host_cpu AS (
SELECT region, az, hostname, AVG(measure_value::double) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND time > ago(2h)
GROUP BY region, az, hostname
)
SELECT region, az, hostname, avg_cpu_utilization, fleet_avg_cpu_utilization
FROM avg_fleet_utilization, avg_per_host_cpu
WHERE avg_cpu_utilization > 1.1 * fleet_avg_cpu_utilization
ORDER BY avg_cpu_utilization DESC
```
Find the average CPU utilization binned at 30 second intervals for a specific EC2 host over the past 2 hours:
```
SELECT BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
ORDER BY binned_timestamp ASC
```
Find the average CPU utilization binned at 30 second intervals for a specific EC2 host over the past 2 hours, filling in the missing values using linear interpolation:
```
WITH binned_timeseries AS (
SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
SELECT hostname,
INTERPOLATE_LINEAR(
CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
FROM binned_timeseries
GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```
Find the average CPU utilization binned at 30 second intervals for a specific EC2 host over the past 2 hours, filling in the missing values using interpolation based on the last observation carried forward:
```
WITH binned_timeseries AS (
SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
SELECT hostname,
INTERPOLATE_LOCF(
CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
FROM binned_timeseries
GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```
# Queries with aggregate functions
Below is an example IoT scenario example data set to illustrate queries with aggregate functions.
**Topics**
+ [Example data](#sample-queries.iot-scenarios.example-data)
+ [Example queries](#sample-queries.iot-scenarios.example-queries)
## Example data
Timestream enables you to store and analyze IoT sensor data such as the location, fuel consumption, speed, and load capacity of one or more fleets of trucks to enable effective fleet management. Below is the schema and some of the data of a table iot\$1trucks that stores telemetry such as location, fuel consumption, speed, and load capacity of trucks.
| Time | truck\$1id | Make | Model | Fleet | fuel\$1capacity | load\$1capacity | measure\$1name | measure\$1value::double | measure\$1value::varchar |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | fuel\$1reading | 65.2 | null |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | load | 400.0 | null |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | speed | 90.2 | null |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | location | null | 47.6062 N, 122.3321 W |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1000 | fuel\$1reading | 10.1 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1000 | load | 950.3 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1000 | speed | 50.8 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1000 | location | null | 40.7128 degrees N, 74.0060 degrees W |
## Example queries
Get a list of all the sensor attributes and values being monitored for each truck in the fleet.
```
SELECT
truck_id,
fleet,
fuel_capacity,
model,
load_capacity,
make,
measure_name
FROM "sampleDB".IoT
GROUP BY truck_id, fleet, fuel_capacity, model, load_capacity, make, measure_name
```
Get the most recent fuel reading of each truck in the fleet in the past 24 hours.
```
WITH latest_recorded_time AS (
SELECT
truck_id,
max(time) as latest_time
FROM "sampleDB".IoT
WHERE measure_name = 'fuel-reading'
AND time >= ago(24h)
GROUP BY truck_id
)
SELECT
b.truck_id,
b.fleet,
b.make,
b.model,
b.time,
b.measure_value::double as last_reported_fuel_reading
FROM
latest_recorded_time a INNER JOIN "sampleDB".IoT b
ON a.truck_id = b.truck_id AND b.time = a.latest_time
WHERE b.measure_name = 'fuel-reading'
AND b.time > ago(24h)
ORDER BY b.truck_id
```
Identify trucks that have been running on low fuel(less than 10 %) in the past 48 hours:
```
WITH low_fuel_trucks AS (
SELECT time, truck_id, fleet, make, model, (measure_value::double/cast(fuel_capacity as double)*100) AS fuel_pct
FROM "sampleDB".IoT
WHERE time >= ago(48h)
AND (measure_value::double/cast(fuel_capacity as double)*100) < 10
AND measure_name = 'fuel-reading'
),
other_trucks AS (
SELECT time, truck_id, (measure_value::double/cast(fuel_capacity as double)*100) as remaining_fuel
FROM "sampleDB".IoT
WHERE time >= ago(48h)
AND truck_id IN (SELECT truck_id FROM low_fuel_trucks)
AND (measure_value::double/cast(fuel_capacity as double)*100) >= 10
AND measure_name = 'fuel-reading'
),
trucks_that_refuelled AS (
SELECT a.truck_id
FROM low_fuel_trucks a JOIN other_trucks b
ON a.truck_id = b.truck_id AND b.time >= a.time
)
SELECT DISTINCT truck_id, fleet, make, model, fuel_pct
FROM low_fuel_trucks
WHERE truck_id NOT IN (
SELECT truck_id FROM trucks_that_refuelled
)
```
Find the average load and max speed for each truck for the past week:
```
SELECT
bin(time, 1d) as binned_time,
fleet,
truck_id,
make,
model,
AVG(
CASE WHEN measure_name = 'load' THEN measure_value::double ELSE NULL END
) AS avg_load_tons,
MAX(
CASE WHEN measure_name = 'speed' THEN measure_value::double ELSE NULL END
) AS max_speed_mph
FROM "sampleDB".IoT
WHERE time >= ago(7d)
AND measure_name IN ('load', 'speed')
GROUP BY fleet, truck_id, make, model, bin(time, 1d)
ORDER BY truck_id
```
Get the load efficiency for each truck for the past week:
```
WITH average_load_per_truck AS (
SELECT
truck_id,
avg(measure_value::double) AS avg_load
FROM "sampleDB".IoT
WHERE measure_name = 'load'
AND time >= ago(7d)
GROUP BY truck_id, fleet, load_capacity, make, model
),
truck_load_efficiency AS (
SELECT
a.truck_id,
fleet,
load_capacity,
make,
model,
avg_load,
measure_value::double,
time,
(measure_value::double*100)/avg_load as load_efficiency -- , approx_percentile(avg_load_pct, DOUBLE '0.9')
FROM "sampleDB".IoT a JOIN average_load_per_truck b
ON a.truck_id = b.truck_id
WHERE a.measure_name = 'load'
)
SELECT
truck_id,
time,
load_efficiency
FROM truck_load_efficiency
ORDER BY truck_id, time
```