# Functions The topics in this section describe functions supported by streaming SQL. **Topics** + [Aggregate Functions](sql-reference-aggregate-functions.md) + [Analytic Functions](sql-reference-analytic-functions.md) + [Boolean Functions](sql-reference-boolean-functions.md) + [Conversion Functions](sql-reference-conversion-functions.md) + [Date and Time Functions](sql-reference-date-time-functions.md) + [Null Functions](sql-reference-null-functions.md) + [Numeric Functions](sql-reference-numeric-functions.md) + [Log Parsing Functions](sql-reference-pattern-matching-functions.md) + [Sorting Functions](sql-reference-sorting-functions.md) + [Statistical Variance and Deviation Functions](sql-reference-statistical-variance-deviation-functions.md) + [Streaming SQL Functions](sql-reference-streaming-sql-functions.md) + [String and Search Functions](sql-reference-string-and-search-functions.md) # Aggregate Functions Instead of returning a result calculated from a single row, an aggregate function returns a result calculated from aggregated data contained in a finite set of rows, or from information about a finite set of rows. An aggregate function may appear in any of the following: + portion of a [SELECT clause](sql-reference-select-clause.md) + [ORDER BY clause](sql-reference-order-by-clause.md) + [HAVING clause](sql-reference-having-clause.md) An aggregate function is different from [Analytic Functions](sql-reference-analytic-functions.md), which are always evaluated relative to a window that must be specified, and so they can't appear in a HAVING clause. Other differences are described in the table later in this topic. Aggregate functions operate slightly differently in aggregate queries on tables than when you use them in aggregate queries on streams, as follows. If an aggregate query on tables contains a GROUP BY clause, the aggregate function returns one result per group in the set of input rows. Lacking an explicit GROUP BY clause is equivalent to GROUP BY (), and returns only one result for the entire set of input rows. On streams, an aggregate query must contain an explicit GROUP BY clause on a monotonic expression based on rowtime. Without one, the sole group is the whole stream, which never ends, preventing any result from being reported. Adding a GROUP BY clause based on a monotonic expression breaks the stream into finite sets of rows, contiguous in time, and each such set can then be aggregated and reported. Whenever a row arrives that changes the value of the monotonic grouping expression, a new group is started and the previous group is considered complete. Then, the Amazon Kinesis Data Analytics application outputs the value of the aggregate functions. Note that the GROUP BY clause may also include other non-monotonic expressions, in which case more than one result per set of rows may be produced. Performing an aggregate query on streams is often referred to as streaming aggregation, as distinct from the windowed aggregation discussed in [Analytic Functions](sql-reference-analytic-functions.md) and [Windowed Aggregation on Streams](sql-reference-windowed-aggregation-stream.md). For more information about stream-to-stream joins, see [JOIN clause](sql-reference-join-clause.md). If an input row contains a `null` in a column used as an input to a data analysis function, the data analysis function ignores the row (except for COUNT). **Differences Between Aggregate and Analytic Functions** | Function Type | Outputs | Rows or Windows Used | Notes | | --- | --- | --- | --- | | Aggregate Functions | One output row per group of input rows. | All output columns are calculated over the same window or same group of rows. | COUNT DISTINCT is not allowed in streaming aggregation. Statements of the following type are not allowed: SELECT COUNT(DISTINCT x) ... FROM ... GROUP BY ... | | [Analytic Functions](sql-reference-analytic-functions.md) | One output row for each input row. | Each output column may be calculated using a different window or partition. | COUNT DISTINCT can't be used as [Analytic Functions](sql-reference-analytic-functions.md) or in windowed aggregation. | ## Streaming Aggregation and Rowtime Bounds Normally, an aggregate query generates a result when a row arrives that changes the value of the monotonic expression in the GROUP BY. For example, if the query is grouped by FLOOR(rowtime TO MINUTE), and the rowtime of the current row is 9:59.30, then a new row with a rowtime of 10:00.00 will trigger the result. Alternately, a rowtime bound can be used to advance the monotonic expression and enable the query to return a result. For example, if the query is grouped by FLOOR(rowtime TO MINUTE), and the rowtime of the current row is 9:59.30, then an incoming rowtime bound of 10:00.00 the query to return a result. ## Aggregate Function List Amazon Kinesis Data Analytics supports the following aggregate functions: + [AVG](sql-reference-avg.md) + [COUNT](sql-reference-count.md) + [COUNT\$1DISTINCT\$1ITEMS\$1TUMBLING Function](count-distinct-items.md) + [EXP\$1AVG](sql-reference-exp-avg.md) + [FIRST\$1VALUE](sql-reference-first-value.md) + [LAST\$1VALUE](sql-reference-last-value.md) + [MAX](sql-reference-max.md) + [MIN](sql-reference-min.md) + [SUM](sql-reference-sum.md) + [TOP\$1K\$1ITEMS\$1TUMBLING Function](top-k.md) The following SQL uses the AVG aggregate function as part of a query to find the average age of all employees: ``` SELECT    AVG(AGE) AS AVERAGE_AGE FROM SALES.EMPS; ``` Result: | AVERAGE\$1AGE | | --- | | 38 | To find the average age of employees in each department, we can add an explicit GROUP BY clause to the query: ``` SELECT    DEPTNO,    AVG(AGE) AS AVERAGE_AGE FROM SALES.EMPS GROUP BY DEPTNO; ``` Returns: | DEPTNO | AVERAGE\$1AGE | | --- | --- | | 10 | 30 | | 20 | 25 | | 30 | 40 | | 40 | 57 | ## Examples of Aggregate Queries on Streams (Streaming Aggregation) For this example, assume that the data in the following table is flowing through the stream called WEATHERSTREAM. | ROWTIME | CITY | TEMP | | --- | --- | --- | | 2018-11-01 01:00:00.0 | Denver | 29 | | 2018-11-01 01:00:00.0 | Anchorage | 2 | | 2018-11-01 06:00:00.0 | Miami | 65 | | 2018-11-01 07:00:00.0 | Denver | 32 | | 2018-11-01 09:00:00.0 | Anchorage | 9 | | 2018-11-01 13:00:00.0 | Denver | 50 | | 2018-11-01 17:00:00.0 | Anchorage | 10 | | 2018-11-01 18:00:00.0 | Miami | 71 | | 2018-11-01 19:00:00.0 | Denver | 43 | | 2018-11-02 01:00:00.0 | Anchorage | 4 | | 2018-11-02 01:00:00.0 | Denver | 39 | | 2018-11-02 07:00:00.0 | Denver | 46 | | 2018-11-02 09:00:00.0 | Anchorage | 3 | | 2018-11-02 13:00:00.0 | Denver | 56 | | 2018-11-02 17:00:00.0 | Anchorage | 2 | | 2018-11-02 19:00:00.0 | Denver | 50 | | 2018-11-03 01:00:00.0 | Denver | 36 | | 2018-11-03 01:00:00.0 | Anchorage | 1 | If you want to find the minimum and maximum temperature recorded anywhere each day (globally regardless of city), the minimum and maximum temperature can be calculated using the aggregate functions MIN and MAX respectively. To indicate that we want this information on a per-day basis (and to provide a monotonic expression as the argument of the GROUP BY clause), we use the FLOOR function to round each row's rowtime down to the nearest day: ``` SELECT STREAM     FLOOR(WEATHERSTREAM.ROWTIME to DAY) AS FLOOR_DAY,    MIN(TEMP) AS MIN_TEMP,    MAX(TEMP) AS MAX_TEMP FROM WEATHERSTREAM GROUP BY FLOOR(WEATHERSTREAM.ROWTIME TO DAY); ``` The result of the aggregate query is shown in the following table. | FLOOR\$1DAY | MIN\$1TEMP | MAX\$1TEMP | | --- | --- | --- | | 2018-11-01 00:00:00.0 | 2 | 71 | | 2018-11-02 00:00:00.0 | 2 | 56 | There is no row for 2018-11-03, even though the example data does include temperature measurements on that day. This is because the rows for 2018-11-03 cannot be aggregated until all rows for that day are known to have arrived, and that will only happen when either a row with a rowtime of 2018-11-04 00:00:00.0 (or later) or a rowtime bound of 2018-11-04 00:00:00.0 (or later) arrives. If and when either did arrive, the next result would be as described in the following table. | FLOOR\$1DAY | MIN\$1TEMP | MAX\$1TEMP | | --- | --- | --- | | 2018-11-03 00:00:00.0 | 1 | 36 | Let's say that instead of finding the global minimum and maximum temperatures each day, we want to find the minimum, maximum, and average temperature for each city each day. To do this, we use the SUM and COUNT aggregate functions to compute the average, and add CITY to the GROUP BY clause, as shown following: ``` SELECT STREAM FLOOR(WEATHERSTREAM.ROWTIME TO DAY) AS FLOOR_DAY,        CITY,    MIN(TEMP) AS MIN_TEMP,    MAX(TEMP) AS MAX_TEMP,    SUM(TEMP)/COUNT(TEMP) AS AVG_TEMP FROM WEATHERSTREAM GROUP BY FLOOR(WEATHERSTREAM.ROWTIME TO DAY), CITY; ``` The result of the aggregate query is shown in the following table. | FLOOR\$1DAY | CITY | MIN\$1TEMP | MAX\$1TEMP | AVG\$1TEMP | | --- | --- | --- | --- | --- | | 2018-11-01 00:00:00.0 | Anchorage | 2 | 10 | 7 | | 2018-11-01 00:00:00.0 | Denver | 29 | 50 | 38 | | 2018-11-01 00:00:00.0 | Miami | 65 | 71 | 68 | | 2018-11-02 00:00:00.0 | Anchorage | 2 | 4 | 3 | | 2018-11-02 00:00:00.0 | Denver | 39 | 56 | 47 | In this case, the arrival of rows for a new day's temperature measurements triggers the aggregation of the previous day's data, grouped by CITY, which then results in one row being produced per city included in the day's measurements. Here again, a rowtime bound 2018-11-04 00:00:00.0 could be used to prompt a result for 2018-11-03 prior to any actual measurements for 2018-11-04 coming in is shown in the following table. | FLOOR\$1DAY | CITY | MIN\$1TEMP | MAX\$1TEMP | AVG\$1TEMP | | --- | --- | --- | --- | --- | | 2018-11-03 00:00:00.0 | Anchorage | 1 | 1 | 1 | | 2018-11-03 00:00:00.0 | Denver | 36 | 36 | 36 | # Windowed Aggregation on Streams To illustrate how windowed aggregation works on Amazon Kinesis data streams, assume that the data in the following table is flowing through a stream called WEATHERSTREAM. | ROWTIME | CITY | TEMP | | --- | --- | --- | | 2018-11-01 01:00:00.0 | Denver | 29 | | 2018-11-01 01:00:00.0 | Anchorage | 2 | | 2018-11-01 06:00:00.0 | Miami | 65 | | 2018-11-01 07:00:00.0 | Denver | 32 | | 2018-11-01 09:00:00.0 | Anchorage | 9 | | 2018-11-01 13:00:00.0 | Denver | 50 | | 2018-11-01 17:00:00.0 | Anchorage | 10 | | 2018-11-01 18:00:00.0 | Miami | 71 | | 2018-11-01 19:00:00.0 | Denver | 43 | | 2018-11-02 01:00:00.0 | Anchorage | 4 | | 2018-11-02 01:00:00.0 | Denver | 39 | | 2018-11-02 07:00:00.0 | Denver | 46 | | 2018-11-02 09:00:00.0 | Anchorage | 3 | | 2018-11-02 13:00:00.0 | Denver | 56 | | 2018-11-02 17:00:00.0 | Anchorage | 2 | | 2018-11-02 19:00:00.0 | Denver | 50 | | 2018-11-03 01:00:00.0 | Denver | 36 | | 2018-11-03 01:00:00.0 | Anchorage | 1 | Suppose that you want to find the minimum and maximum temperature recorded in the 24-hour period prior to any given reading, globally, regardless of city. To do this, you define a window of `RANGE INTERVAL '1' DAY PRECEDING`, and use it in the `OVER` clause for the `MIN` and `MAX` analytic functions: ``` SELECT STREAM        ROWTIME,        MIN(TEMP) OVER W1 AS WMIN_TEMP,        MAX(TEMP) OVER W1 AS WMAX_TEMP FROM WEATHERSTREAM WINDOW W1 AS (    RANGE INTERVAL '1' DAY PRECEDING ); ``` ## Results | ROWTIME | WMIN\$1TEMP | WMAX\$1TEMP | | --- | --- | --- | | 2018-11-01 01:00:00.0 | 29 | 29 | | 2018-11-01 01:00:00.0 | 2 | 29 | | 2018-11-01 06:00:00.0 | 2 | 65 | | 2018-11-01 07:00:00.0 | 2 | 65 | | 2018-11-01 09:00:00.0 | 2 | 65 | | 2018-11-01 13:00:00.0 | 2 | 65 | | 2018-11-01 17:00:00.0 | 2 | 65 | | 2018-11-01 18:00:00.0 | 2 | 71 | | 2018-11-01 19:00:00.0 | 2 | 71 | | 2018-11-02 01:00:00.0 | 2 | 71 | | 2018-11-02 01:00:00.0 | 2 | 71 | | 2018-11-02 07:00:00.0 | 4 | 71 | | 2018-11-02 09:00:00.0 | 3 | 71 | | 2018-11-02 13:00:00.0 | 3 | 71 | | 2018-11-02 17:00:00.0 | 2 | 71 | | 2018-11-02 19:00:00.0 | 2 | 56 | | 2018-11-03 01:00:00.0 | 2 | 56 | | 2018-11-03 01:00:00.0 | 1 | 56 | Now, assume that you want to find the minimum, maximum, and average temperature recorded in the 24-hour period prior to any given reading, broken down by city. To do this, you add a `PARTITION BY` clause on `CITY` to the window specification, and add the `AVG` analytic function over the same window to the selection list: ``` SELECT STREAM        ROWTIME,        CITY,        MIN(TEMP) over W1 AS WMIN_TEMP,        MAX(TEMP) over W1 AS WMAX_TEMP,        AVG(TEMP) over W1 AS WAVG_TEMP FROM AGGTEST.WEATHERSTREAM WINDOW W1 AS (        PARTITION BY CITY        RANGE INTERVAL '1' DAY PRECEDING ); ``` ### Results | ROWTIME | CITY | WMIN\$1TEMP | WMAX\$1TEMP | WAVG\$1TEMP | | --- | --- | --- | --- | --- | | 2018-11-01 01:00:00.0 | Denver | 29 | 29 | 29 | | 2018-11-01 01:00:00.0 | Anchorage | 2 | 2 | 2 | | 2018-11-01 06:00:00.0 | Miami | 65 | 65 | 65 | | 2018-11-01 07:00:00.0 | Denver | 29 | 32 | 30 | | 2018-11-01 09:00:00.0 | Anchorage | 2 | 9 | 5 | | 2018-11-01 13:00:00.0 | Denver | 29 | 50 | 37 | | 2018-11-01 17:00:00.0 | Anchorage | 2 | 10 | 7 | | 2018-11-01 18:00:00.0 | Miami | 65 | 71 | 68 | | 2018-11-01 19:00:00.0 | Denver | 29 | 50 | 38 | | 2018-11-02 01:00:00.0 | Anchorage | 2 | 10 | 6 | | 2018-11-02 01:00:00.0 | Denver | 29 | 50 | 38 | | 2018-11-02 07:00:00.0 | Denver | 32 | 50 | 42 | | 2018-11-02 09:00:00.0 | Anchorage | 3 | 10 | 6 | | 2018-11-02 13:00:00.0 | Denver | 39 | 56 | 46 | | 2018-11-02 17:00:00.0 | Anchorage | 2 | 10 | 4 | | 2018-11-02 19:00:00.0 | Denver | 39 | 56 | 46 | | 2018-11-03 01:00:00.0 | Denver | 36 | 56 | 45 | | 2018-11-03 01:00:00.0 | Anchorage | 1 | 4 | 2 | ## Examples of Rowtime Bounds and Windowed Aggregation This is an example of a windowed aggregate query: ``` SELECT STREAM ROWTIME, ticker, amount, SUM(amount)    OVER (        PARTITION BY ticker        RANGE INTERVAL '1' HOUR PRECEDING) AS hourlyVolume FROM Trades ``` Because this is a query on a stream, rows pop out of this query as soon as they go in. For example, given the following inputs: ``` Trades: IBM 10 10 10:00:00 Trades: ORCL 20 10:10:00 Trades.bound: 10:15:00 Trades: ORCL 15 10:25:00 Trades: IBM 30 11:05:00 Trades.bound: 11:10:00 ``` In this example, the output is as follows: ``` Trades: IBM 10 10 10:00:00 Trades: ORCL 20 20 10:10:00 Trades.bound: 10:15:00 Trades: ORCL 15 35 10:25:00 Trades: IBM 30 30 11:05:00 Trades.bound: 11:10:00 ``` The rows still hang around behind the scenes for an hour, and thus the second ORCL row output has a total of 35; but the original IBM trade falls outside the "hour preceding" window, and so it is excluded from the IBM sum. ## Example Some business problems seem to need totals over the whole history of a stream, but this is usually not practical to compute. However, such business problems are often solvable by looking at the last day, the last hour, or the last N records. Sets of such records are called *windowed aggregates*. They are easy to compute in a stream database, and can be expressed in ANSI (SQL:2008) standard SQL as follows: ``` SELECT STREAM ticker,  avg(price) OVER lastHour AS avgPrice,      max(price) OVER lastHour AS maxPrice   FROM Bids   WINDOW lastHour AS  (      PARTITION BY ticker      RANGE INTERVAL '1' HOUR PRECEDING) ``` ## **Note** The `Interval_clause` must be of one of the following appropriate types: Integer literal with ROWS Numeric value for RANGE over a numeric column INTERVAL for a RANGE over a date/time/timestamp # AVG Returns the average of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about windowed queries, see [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html). To return an exponentially weighted average of a stream of value expressions selected in a specified time window, see [EXP\$1AVG](sql-reference-exp-avg.md). When you use AVG, be aware of the following: + If you don't use the `OVER` clause, `AVG` is calculated as an aggregate function. In this case, the aggregate query must contain a [GROUP BY clause](sql-reference-group-by-clause.md) on a monotonic expression based on `ROWTIME` that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see [Aggregate Functions](sql-reference-aggregate-functions.md). + A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see [Tumbling Windows (Aggregations Using GROUP BY)](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). + If you use the `OVER` clause, `AVG` is calculated as an analytic function. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). + A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html) ## Syntax ### Tumbling Windowed Query ``` AVG(number-expression) ... GROUP BY monotonic-expression | time-based-expression ``` ### Sliding Windowed Query ``` AVG([DISTINCT | ALL] number-expression) OVER window-specification ``` ## Parameters DISTINCT Performs the aggregate function only on each unique instance of a value. ALL Performs the aggregate function on all values. `ALL` is the default. *number-expression* Specifies the value expressions evaluated for each row in the aggregation. OVER *window-specification* Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows. GROUP BY *monotonic-expression* \$1 *time-based-expression* Groups records based on the value of the grouping expression, returning a single summary row for each group of rows that has identical values in all columns. ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of the [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Return the Average of Values Using the GROUP BY Clause In this example, the aggregate query has a `GROUP BY` clause on `ROWTIME` that groups the stream into finite rows. The `AVG` function is then calculated from the rows returned by the `GROUP BY` clause. #### Using STEP (Recommended) ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), avg_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, AVG(price) AS avg_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND); ``` #### Using FLOOR ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), avg_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, AVG(price) AS avg_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE); ``` #### Results The preceding examples output a stream similar to the following. ![\[Table showing stock ticker symbols NFS, WAS, PPL, ALY with corresponding dates and average prices.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-avg-example-1.png) ### Example 2: Return the Average of Values Using the OVER Clause In this example, the `OVER` clause divides records in a stream partitioned by the time range interval of '1' hour preceding. The `AVG` function is then calculated from the rows returned by the `OVER` clause. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), avg_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, AVG(price) OVER ( PARTITION BY ticker_symbol RANGE INTERVAL '1' HOUR PRECEDING) AS avg_price FROM "SOURCE_SQL_STREAM_001" ``` The preceding example outputs a stream similar to the following. ![\[Table showing stock data with columns for timestamp, ticker symbol, and average price.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-avg-example-2.png) ## Usage Notes Amazon Kinesis Analytics doesn't support `AVG` applied to interval types. This functionality is a departure from the SQL:2008 standard. When used as an analytic function, `AVG` returns null if the window being evaluated contains no rows, or if all rows contain null values. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). AVG also returns null for a `PARTITION BY` clause for which the partition within the window matching the input row contains no rows or all rows are null. For more information about `PARTITION BY`, see [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md). `AVG` ignores null values from the set of values or a numeric expression. For example, each of the following return the value of 2: + AVG(1, 2, 3) = 2 + AVG(1,null, 2, null, 3, null) = 2 ## Related Topics + [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html) + [EXP\$1AVG](sql-reference-exp-avg.md) + [Aggregate Functions](sql-reference-aggregate-functions.md) + [GROUP BY clause](sql-reference-group-by-clause.md) + [Analytic Functions](sql-reference-analytic-functions.md) + [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) + [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md) # COUNT Returns the number of qualifying rows of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about windowed queries, see [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html). When you use COUNT, be aware of the following: + If you don't use the `OVER` clause, `COUNT` is calculated as an aggregate function. In this case, the aggregate query must contain a [GROUP BY clause](sql-reference-group-by-clause.md) on a monotonic expression based on `ROWTIME` that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see [Aggregate Functions](sql-reference-aggregate-functions.md). + A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see [Tumbling Windows (Aggregations Using GROUP BY)](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). + If you use the `OVER` clause, `COUNT` is calculated as an analytic function. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). + A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html) ## Syntax ### Tumbling Windowed Query ``` COUNT(number-expression) ... GROUP BY monotonic-expression | time-based-expression ``` ### Sliding Windowed Query ``` COUNT(* | ALL number-expression) OVER window-specification ``` ## Parameters \$1 Counts all rows. ALL Counts all rows. `ALL` is the default. *number-expression* Specifies the value expressions evaluated for each row in the aggregation. OVER *window-specification* Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows. GROUP BY *monotonic-expression* \$1 *time-based-expression* Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns. ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Return the Number of Values Using the GROUP BY Clause In this example, the aggregate query has a `GROUP BY` clause on `ROWTIME` that groups the stream into finite rows. The `COUNT` function is then calculated from the rows returned by the `GROUP BY` clause. #### Using STEP (Recommended) ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), count_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, COUNT(Price) AS count_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND); ``` #### Using FLOOR ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), count_price DOUBLE); -- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, COUNT(Price) AS count_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE); ``` #### Results The preceding examples output a stream similar to the following. ![\[Table showing stock ticker symbols and prices for AAPL, WSB, and UHN at specific times.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-count-example-1.png) ### Example 2: Return the Number of Values Using the OVER Clause In this example, the `OVER` clause divides records in a stream partitioned by the time range interval of '1' hour preceding. The `COUNT` function is then calculated from the rows returned by the `OVER` clause. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), count_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, COUNT(price) OVER ( PARTITION BY ticker_symbol RANGE INTERVAL '1' HOUR PRECEDING) AS count_price FROM "SOURCE_SQL_STREAM_001" ``` The preceding example outputs a stream similar to the following. ![\[Table showing timestamp, stock ticker symbols, and prices for four different stocks.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-count-example-2.png) ## Usage Notes Amazon Kinesis Analytics doesn't support the `FILTER` clause of the `COUNT` function or the use of `COUNT DISTINCT` in either aggregate functions or analytic functions. For more information on aggregate and analytic functions, see [Aggregate Functions](sql-reference-aggregate-functions.md) and [Analytic Functions](sql-reference-analytic-functions.md). This functionality is a departure from the SQL:2008 standard. When used as an analytic function, `COUNT` returns zero if the window being evaluated contains no rows. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). COUNT also returns zero for a `PARTITION BY` clause for which the partition within the window matching the input row contains no rows. For more information about `PARTITION BY`, see [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md). `COUNT` ignores null values from the set of values or a numeric expression. For example, each of the following return the value of 3: + COUNT(1, 2, 3) = 3 + COUNT(1,null, 2, null, 3, null) = 3 ## Related Topics + [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html) + [Aggregate Functions](sql-reference-aggregate-functions.md) + [GROUP BY clause](sql-reference-group-by-clause.md) + [Analytic Functions](sql-reference-analytic-functions.md) + [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) + [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md) # COUNT\$1DISTINCT\$1ITEMS\$1TUMBLING Function Returns a count of the number of distinct items in the specified in-application stream column over a tumbling window. The resulting count is approximate; the function uses the HyperLogLog algorithm. For more information, see [HyperLogLog](https://en.wikipedia.org/wiki/HyperLogLog). When you use `COUNT_DISTINCT_ITEMS_TUMBLING`, be aware of the following: + When there are less than or equal to 10,000 items in the window, the function returns an exact count. + Getting an exact count of the number of distinct items can be inefficient and costly. Therefore, this function approximates the count. For example, if there are 100,000 distinct items, the algorithm may return 99,700. If cost and efficiency is not a consideration, you can write your own SELECT statement to get the exact count. The following example demonstrates how to get an exact count of distinct rows for each ticker symbol in a five second tumbling window. The SELECT statement uses all of the columns (except ROWTIME) in determining the uniqueness. ``` CREATE OR REPLACE STREAM output_stream (ticker_symbol VARCHAR(4), unique_count BIGINT); CREATE OR REPLACE PUMP stream_pump AS INSERT INTO output_stream SELECT STREAM TICKER_SYMBOL, COUNT(distinct_stream.price) AS unique_count FROM ( SELECT STREAM DISTINCT rowtime as window_time, TICKER_SYMBOL, CHANGE, PRICE, STEP((SOURCE_SQL_STREAM_001.rowtime) BY INTERVAL '5' SECOND) FROM SOURCE_SQL_STREAM_001) as distinct_stream GROUP BY TICKER_SYMBOL, STEP((distinct_stream.window_time) BY INTERVAL '5' SECOND); ``` The function operates on a [tumbling window](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). You specify the size of the tumbling window as a parameter. ## Syntax ``` COUNT_DISTINCT_ITEMS_TUMBLING ( in-application-streamPointer, 'columnName',      windowSize ) ``` ## Parameters The following sections describe the parameters. ### in-application-streamPointer Using this parameter, you provide a pointer to an in-application stream. You can set a pointer using the `CURSOR` function. For example, the following statement sets a pointer to `InputStream`. ``` CURSOR(SELECT STREAM * FROM InputStream) ``` ### columnName Column name in your in-application stream that you want the function to use to count distinct values. Note the following about the column name: + Must appear in single quotation marks ('). For example, `'column1'`. ### windowSize Size of the tumbling window in seconds. The size should be at least 1 second and should not exceed 1 hour = 3600 seconds. ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Approximate the number of distinct values in a column The following example demonstrates how to use the `COUNT_DISTINCT_ITEMS_TUMBLING` function to approximate the number of distinct `TICKER_SYMBOL` values in the current tumbling window of the in-application stream. For more information about tumbling windows, see [Tumbling Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). ``` CREATE OR REPLACE STREAM DESTINATION_SQL_STREAM ( NUMBER_OF_DISTINCT_ITEMS BIGINT); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM * FROM TABLE(COUNT_DISTINCT_ITEMS_TUMBLING( CURSOR(SELECT STREAM * FROM "SOURCE_SQL_STREAM_001"), -- pointer to the data stream 'TICKER_SYMBOL', -- name of column in single quotes 60 -- tumbling window size in seconds ) ); ``` The preceding example outputs a stream similar to the following: ![\[Table showing ROWTIME and NUMBER_OF_DISTINCT_ITEMS columns with four identical entries.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-count-distinct-example-1.png) # EXP\$1AVG ``` EXP_AVG ( expression, ) ``` EXP\$1AVG returns an exponentially-weighted average ([exponential moving average](https://en.wikipedia.org/wiki/Moving_average)) of a stream of value expressions selected in a specified time window. EXP\$1AVG divides the specified window into intervals based on the value of . The values of the specified expression are weighted the most heavily for the most recent time-intervals and exponentially less heavily for earlier intervals. ## Example This example creates an exponentially-weighted average of the price of each stock ticker over a 30-second window such that the prices (for that ticker symbol) in the most recent 10-second subwindow carry double the weight of the prices in the middle 10-second subwindow and four times the weight of the prices in the oldest 10-second subwindow. ``` select stream t.rowtime, ticker, price, exp_avg(price, INTERVAL '10' SECOND) over w as avgPrice from t window w as (partition by ticker range interval '30' second preceding); ``` In this example, 10 seconds is the half-life of the decay function, that is, the period over which the weights applied to the prices being averaged decrease by a factor of two. In other words, the older one will be given half as much weight as the newer one. It is specified as the time\$1interval in the call to EXP\$1AVG as interval '10' second . # FIRST\$1VALUE ``` FIRST_VALUE( ) OVER ``` FIRST\$1VALUE returns the evaluation of the from the first row that qualifies for the aggregate. FIRST\$1VALUE requires the OVER clause, and is considered an [Analytic Functions](sql-reference-analytic-functions.md). FIRST\$1VALUE has a null treatment option defined in the following table. | Null treatment option | Effect | | --- | --- | | FIRST\$1VALUE(x) IGNORE NULLS OVER | Returns first non null value of x in | | FIRST\$1VALUE(x) RESPECT NULLS OVER | Returns first value, including null of x in | | FIRST\$1VALUE(x) OVER | Returns first value, including null of x in | # LAST\$1VALUE ``` LAST_VALUE ( )  OVER ``` LAST\$1VALUE returns the evaluation of the from the last row that qualifies for the aggregate. | Null Treatment Option | Effect | | --- | --- | | LAST\$1VALUE(x) IGNORE NULLS OVER | Returns last non null value of x in | | LAST\$1VALUE(x) RESPECT NULLS OVER | Returns last value, including null of x in | | LAST\$1VALUE(x) OVER | Returns last value, including null of x in | # MAX Returns the maximum value of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about window queries, see [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html). When you use MAX, be aware of the following: + If you don't use the `OVER` clause, `MAX` is calculated as an aggregate function. In this case, the aggregate query must contain a [GROUP BY clause](sql-reference-group-by-clause.md) on a monotonic expression based on `ROWTIME` that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see [Aggregate Functions](sql-reference-aggregate-functions.md). + A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see [Tumbling Windows (Aggregations Using GROUP BY)](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). + If you use the `OVER` clause, `MAX` is calculated as an analytic function. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). + A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html) ## Syntax ### Tumbling Windowed Query ``` MAX(number-expression) ... GROUP BY monotonic-expression | time-based-expression ``` ### Sliding Windowed Query ``` MAX(number-expression) OVER window-specification ``` ## Parameters *number-expression* Specifies the value expressions evaluated for each row in the aggregation. OVER *window-specification* Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows. GROUP BY *monotonic-expression* \$1 *time-based-expression* Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns. ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Return the Maximum Value Using the GROUP BY Clause In this example, the aggregate query has a `GROUP BY` clause on `ROWTIME` that groups the stream into finite rows. The `MAX` function is then calculated from the rows returned by the `GROUP BY` clause. #### Using STEP (recommended) ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), max_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, MAX(Price) AS max_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND); ``` #### Using FLOOR ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), max_price DOUBLE); -- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, MAX(Price) AS max_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE); ``` #### Results The preceding examples output a stream similar to the following. ![\[Table showing ROWTIME, TICKER_SYMBOL, and MAX_PRICE columns with sample data entries.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-max-example-1.png) ### Example 2: Return the Maximum Value Using the OVER Clause In this example, the `OVER` clause divides records in a stream partitioned by the time range interval of '1' hour preceding. The `MAX` function is then calculated from the rows returned by the `OVER` clause. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), max_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, MAX(price) OVER ( PARTITION BY ticker_symbol RANGE INTERVAL '1' HOUR PRECEDING) AS max_price FROM "SOURCE_SQL_STREAM_001" ``` The preceding example outputs a stream similar to the following. ![\[Table showing stock ticker symbols QAZ, QXZ, MJN, WSB with corresponding timestamps and maximum prices.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-max-example-2.png) ## Usage Notes For string values, MAX is determined by which string is last in the collating sequence. If MAX is used as an analytic function and the window being evaluated contains no rows, MAX returns null. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). ## Related Topics + [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html) + [Aggregate Functions](sql-reference-aggregate-functions.md) + [GROUP BY clause](sql-reference-group-by-clause.md) + [Analytic Functions](sql-reference-analytic-functions.md) + [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) + [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md) # MIN Returns the minimum value of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about windowed queries, see [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html). When you use MIN, be aware of the following: + If you don't use the `OVER` clause, `MIN` is calculated as an aggregate function. In this case, the aggregate query must contain a [GROUP BY clause](sql-reference-group-by-clause.md) on a monotonic expression based on `ROWTIME` that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see [Aggregate Functions](sql-reference-aggregate-functions.md). + A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see [Tumbling Windows (Aggregations Using GROUP BY)](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). + If you use the `OVER` clause, `MIN` is calculated as an analytic function. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). + A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html) ## Syntax ### Tumbling Windowed Query ``` MIN(number-expression) ... GROUP BY monotonic-expression | time-based-expression ``` ### Sliding Windowed Query ``` MIN(number-expression) OVER window-specification ``` ## Parameters *number-expression* Specifies the value expressions evaluated for each row in the aggregation. OVER *window-specification* Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows. GROUP BY *monotonic-expression* \$1 *time-based-expression* Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns. ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of the [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Return the Minimum Value Using the GROUP BY Clause In this example, the aggregate query has a `GROUP BY` clause on `ROWTIME` that groups the stream into finite rows. The `MIN` function is then calculated from the rows returned by the `GROUP BY` clause. #### Using STEP (Recommended) ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), min_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, MIN(Price) AS min_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND); ``` #### Using FLOOR ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), min_price DOUBLE); -- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, MIN(Price) AS min_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE); ``` #### Results The preceding examples output a stream similar to the following. ![\[Table showing stock ticker symbols, timestamps, and minimum prices for four different stocks.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-min-example-1.png) ### Example 2: Return the Minimum Value Using the OVER Clause In this example, the `OVER` clause divides records in a stream partitioned by the time range interval of '1' hour preceding. The `MIN` function is then calculated from the rows returned by the `OVER` clause. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), min_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, MIN(price) OVER ( PARTITION BY ticker_symbol RANGE INTERVAL '1' HOUR PRECEDING) AS min_price FROM "SOURCE_SQL_STREAM_001" ``` The preceding example outputs a stream similar to the following. ![\[Table showing stock ticker symbols and minimum prices for NFS, NFLX, ASD, and DFG on 2017-02-17.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-min-example-2.png) ## Usage Notes For string values, MIN is determined by which string is last in the collating sequence. If MIN is used as an analytic function and the window being evaluated contains no rows, MIN returns null. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). ## Related Topics + [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html) + [Aggregate Functions](sql-reference-aggregate-functions.md) + [GROUP BY clause](sql-reference-group-by-clause.md) + [Analytic Functions](sql-reference-analytic-functions.md) + [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) + [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md) # SUM Returns the sum of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about windowed queries, see [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html). When you use SUM, be aware of the following: + If you don't use the `OVER` clause, `SUM` is calculated as an aggregate function. In this case, the aggregate query must contain a [GROUP BY clause](sql-reference-group-by-clause.md) on a monotonic expression based on `ROWTIME` that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see [Aggregate Functions](sql-reference-aggregate-functions.md). + A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see [Tumbling Windows (Aggregations Using GROUP BY)](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). + If you use the `OVER` clause, `SUM` is calculated as an analytic function. For more information, see [Analytic Functions](sql-reference-analytic-functions.md). + A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html) ## Syntax ### Tumbling Windowed Query ``` SUM(number-expression) ... GROUP BY monotonic-expression | time-based-expression ``` ### Sliding Windowed Query ``` SUM([DISTINCT | ALL] number-expression) OVER window-specification ``` ## Parameters DISTINCT Counts only distinct values. ALL Counts all rows. `ALL` is the default. *number-expression* Specifies the value expressions evaluated for each row in the aggregation. OVER *window-specification* Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows. GROUP BY *monotonic-expression* \$1 *time-based-expression* Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns. ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of the [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Return the Sum of Values Using the GROUP BY Clause In this example, the aggregate query has a `GROUP BY` clause on `ROWTIME` that groups the stream into finite rows. The `SUM` function is then calculated from the rows returned by the `GROUP BY` clause. #### Using STEP (Recommended) ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), sum_price DOUBLE); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, SUM(price) AS sum_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND); ``` #### Using FLOOR ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), sum_price DOUBLE); -- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, SUM(price) AS sum_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE); ``` #### Results The preceding examples output a stream similar to the following. ![\[Table showing data stream with timestamp, ticker symbol, and sum price columns.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-sum-example-1.png) ## Usage Notes Amazon Kinesis Analytics doesn't support `SUM` applied to interval types. This functionality is a departure from the SQL:2008 standard. `SUM` ignores null values from the set of values or a numeric expression. For example, each of the following return the value of 6: + SUM(1, 2, 3) = 6 + SUM(1,null, 2, null, 3, null) = 6 ## Related Topics + [Windowed Queries](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/windowed-sql.html) + [Aggregate Functions](sql-reference-aggregate-functions.md) + [GROUP BY clause](sql-reference-group-by-clause.md) + [Analytic Functions](sql-reference-analytic-functions.md) + [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) + [WINDOW Clause (Sliding Windows)](sql-reference-window-clause.md) # TOP\$1K\$1ITEMS\$1TUMBLING Function Returns the most frequently occurring values in the specified in-application stream column over a tumbling window. This can be used to find trending (most popular) values in a specified column. For example, the Getting Started exercise uses a demo stream that provides continuous stock price updates (ticker\$1symbol, price, change, and other columns). Suppose you want to find the three most frequently traded stocks in each 1-minute tumbling window. You can use this function to find those ticker symbols. When you use `TOP_K_ITEMS_TUMBLING`, be aware of the following: + Counting each incoming record on your streaming source is not efficient, therefore the function approximates the most frequently occurring values. For example, when seeking the three most traded stocks, the function may return three of the five most traded stocks. The function operates on a [tumbling window](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html). You specify the window size as a parameter. For a sample application with step-by-step instructions, see [Most Frequently Occurring Values](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/top-k-example.html). ## Syntax ``` TOP_K_ITEMS_TUMBLING ( in-application-streamPointer, 'columnName',      K, windowSize, ) ``` ## Parameters The following sections describe the parameters. ### in-application-streamPointer Pointer to an in-application stream. You can set a pointer using the CURSOR function. For example, the following statement sets a pointer to InputStream. ``` CURSOR(SELECT STREAM * FROM InputStream) ``` ### columnName Column name in your in-application stream that you want to use to compute the topK values. Note the following about the column name: **Note** The column name must appear in single quotation marks ('). For example, `'column1'`. ### K Using this parameter, you specify how many of the most frequently occurring values from a specific column you want returned. The value K must be greater than or equal to one and cannot exceed 100,000. ### windowSize Size of the tumbling window in seconds. The size must be greater than or equal to one second and must not exceed 3600 seconds (one hour). ## Examples ### Example Dataset The examples following are based on the sample stock dataset that is part of [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL) ``` ### Example 1: Return the Most Frequently Occurring Values The following example retrieves the most frequently occuring values in the sample stream created in the [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/getting-started.html) tutorial. ``` CREATE OR REPLACE STREAM DESTINATION_SQL_STREAM ( "TICKER_SYMBOL" VARCHAR(4), "MOST_FREQUENT_VALUES" BIGINT ); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM * FROM TABLE (TOP_K_ITEMS_TUMBLING( CURSOR(SELECT STREAM * FROM "SOURCE_SQL_STREAM_001"), 'TICKER_SYMBOL', -- name of column in single quotes 5, -- number of the most frequently occurring values 60 -- tumbling window size in seconds ) ); ``` The preceding example outputs a stream similar to the following. ![\[Table showing data stream with columns for timestamp, ticker symbol, and frequency values.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-topk-example-1.png) # Analytic Functions An analytic function is one that returns a result calculated from data in (or about) a finite set of rows identified by a [SELECT clause](sql-reference-select-clause.md) or in the [ORDER BY clause](sql-reference-order-by-clause.md). The SELECT topic explains the order-by clause, showing the order-by chart, as well as the windowing clause (and window-specification chart). To see where an order-by clause is used in Select statements, see the Select chart in the SELECT topic of this guide. 1. Analytic functions must specify a window. Since there are a few restrictions on window specifications, and a few differences between specifying windows for windowed aggregation and windowed join, please see [Allowed and Disallowed Window Specifications](sql-reference-allowed-disallowed-window.md) for explanations. 1. Analytic functions may only appear in the portion of a SELECT clause or in the ORDER BY clause. Other differences are described in the table later in this topic. Performing queries using analytic functions is commonly referred to as windowed aggregation (discussed below), as distinct from [Aggregate Functions](sql-reference-aggregate-functions.md). Because of the presence of the window specification, queries that use analytic functions produce results in a different manner than do aggregate queries. For each row in the input set, the window specification identifies a different set of rows on which the analytic function operates. If the window specification also includes a PARTITION BY clause, then the only rows in the window that will be considered in producing a result will be those that share the same partition as the input row. If an input row contains a null in a column used as an input to an analytic function, the analytic function ignores the row, except for COUNT, which does count rows with null values. In cases where the window (or in the case of a PARTITION BY, a partition within the window) contains no rows, an analytic function will return null. The exception to this is COUNT, which returns zero. **Differences Between Aggregate and Analytic Functions** | Function Type | Outputs | Rows or Windows Used | Notes | | --- | --- | --- | --- | | [Aggregate Functions](sql-reference-aggregate-functions.md) | One output row per group of input rows. | All output columns are calculated over the same window or same group of rows. | COUNT DISTINCT is not allowed in [Aggregate Functions](sql-reference-aggregate-functions.md). Statements of the following type are not allowed: SELECT COUNT(DISTINCT x) ... FROM ... GROUP BY ... | | Analytic Functions | One output row for each input row. | Each output column may be calculated using a different window or partition. | COUNT DISTINCT can't be used as analytic functions or in windowed aggregation. | ## Related Topics + [Windowed Aggregation on Streams](sql-reference-windowed-aggregation-stream.md) + [SELECT statement](sql-reference-select.md) + [SELECT clause](sql-reference-select-clause.md) # Boolean Functions The topics in this section describe the boolean functions for Amazon Kinesis Data Analytics streaming SQL. **Topics** + [ANY](sql-reference-any.md) + [EVERY](sql-reference-every.md) # ANY ``` ANY ( ) ``` ANY returns true if the supplied boolean\$1expression is true in any of the selected rows. Returns false if the supplied boolean\$1expression is true in none of the selected rows. **Example** The following SQL snippet returns 'true' if the price for any ticker in the stream of trades is below 1. Returns 'false' if every price in the stream is 1 or greater. ``` SELECT STREAM ANY (price < 1) FROM trades  GROUP BY (FLOOR trades.rowtime to hour) ``` # EVERY ``` EVERY ( ) ``` EVERY returns true if the supplied boolean\$1expression is true in all of the selected rows. Returns false if the supplied boolean\$1expression is false in any of the selected rows. **Example** The following SQL snippet returns 'true' if the price for every ticker in the stream of trades is below 1. Returns 'false' if any price is 1 or greater. ``` SELECT STREAM EVERY (price < 1) FROM trades  GROUP BY (FLOOR trades.rowtime to hour) ``` # Conversion Functions The topics in this section describe the conversion functions for Amazon Kinesis Data Analytics streaming SQL. For functions that convert to and from Datetime and Timestamp values, see [Datetime Conversion Functions](sql-reference-datetime-conversion-functions.md). **Topics** + [CAST](sql-reference-cast.md) # CAST CAST lets you convert one value expression or data type to another value expression or data type. ``` CAST ( AS ) :=  := ``` ## Valid Conversions Using CAST with source operands of the types listed in the first column below can create cast target types as listed in the second column, without restriction. Other target types are not supported. | Source Operand Types | Target Operand Types | | --- | --- | | Any numeric type (NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE) | VARCHAR, CHAR, or any numeric type (See Note A.) | | VARCHAR, CHAR | All of the above, plus, DATE, TIME, TIMESTAMP, DAY-TIME INTERVAL, BOOLEAN | | DATE | DATE, VARCHAR, CHAR, TIMESTAMP | | TIME | TIME, VARCHAR, CHAR, TIMESTAMP | | TIMESTAMP | TIME, VARCHAR, CHAR, TIMESTAMP, DATE | | DAY-TIME INTERVAL | DAY-TIME INTERVAL, BIGINT, DECIMAL, CHAR, VARCHAR | | BOOLEAN | VARCHAR, CHAR, BOOLEAN | | BINARY, VARBINARY | BINARY, VARBINARY | ### Examples #### 2.1 DATE to CHAR/VARCHAR ``` +-------------+ |   EXPR$0    | +-------------+ | 2008-08-23  | +-------------+ 1 row selected ``` (Note that if an inadequate output specification is supplied, no rows are selected: ``` values(cast(date'2008-08-23' as varchar(9))); 'EXPR$0' No rows selected ``` (Because the date literal requires 10 characters) In the next case, the date is blank-padded on the right (because of the semantics of the CHAR datatype): ``` +----------------------------+ |           EXPR$0           | +----------------------------+ | 2008-08-23                 | +----------------------------+ 1 row selected ``` #### REAL to INTEGER The real (NUMERIC or DECIMAL) is rounded by the cast: ``` +---------+ | EXPR$0  | +---------+ | -2      | +---------+ 1 row selected ``` #### STRING to TIMESTAMP There are two ways to convert a string to a timestamp. The first uses CAST, as shown in the next topic. The other uses [Char To Timestamp(Sys)](sql-reference-char-to-timestamp.md). ## Using CAST to Convert a String to a Timestamp The example below illustrates this method for conversion: ``` 'EXPR$0' '2007-02-19 21:23:45' 1 row selected ``` If the input string lacks any one of the six fields (year, month, day, hours, minutes, seconds), or uses any delimiters different from those shown above, CAST will not return a value. (Fractional seconds are disallowed.) If the input string is thus not in the appropriate format to be CAST, then to convert the string to a timestamp, you must use the CHAR\$1TO\$1TIMESTAMP method. ## Using CHAR\$1TO\$1TIMESTAMP to convert a String to a Timestamp When the input string is not in the appropriate format to be CAST, you can use the CHAR\$1TO\$1TIMESTAMP method. It has the additional advantage that you can specify which parts of the timestamp string you wish to use in subsequent processing, and create a TIMESTAMP value containing only those. To do so, you specify a template that identifies which parts you want, such as 'yyyy-MM' to use only the year and month parts. The input-date-time string-to-be-converted can contain all or any parts of a full timestamp, that is, values for any or all of the standard elements ('yyyy-MM-dd hh:mm:ss'). If all these elements are present in your input string, and 'yyyy-MM-dd hh:mm:ss' is the template you supply, then the input-string elements are interpreted in that order as year, month, day, hour, minute, and seconds, such as in '2009-09-16 03:15:24'. The yyyy cannot be uppercase; the hh can be uppercase to mean using a 24-hour clock. For many examples of valid specifiers, see the table and examples later in this topic. For the full range of valid specifiers, see [Class SimpleDateFormat](http://docs.oracle.com/javase/7/docs/api/index.html?java/text/SimpleDateFormat.html) on the Oracle website. CHAR\$1TO\$1TIMESTAMP uses the template you specify as a parameter in the function call. The template causes the TIMESTAMP result to use only the parts of the input-date-time value that you specified in the template. Those fields in the resulting TIMESTAMP will then contain the corresponding data taken from your input-date-time string; fields not specified in your template will use default values (see below). The format of the template used by CHAR\$1TO\$1TIMESTAMP is defined by [Class SimpleDateFormat](http://docs.oracle.com/javase/7/docs/api/index.html?java/text/SimpleDateFormat.html), at which link all the specifiers are listed, some with examples. For more information, see [Date and Time Patterns](sql-reference-parse-timestamp-format.md). The function-call syntax is as follows: ```  CHAR_TO_TIMESTAMP('','') ``` Where is the template you specify for the parts of you want, and is the original string that is being converted to a TIMESTAMP result. Each string must be enclosed in single quotes, and each element of the must be in the range for its corresponding element in the template. Otherwise, no result is returned. ### Example 1 + The input-string-element whose position corresponds with MM must be an integer from 1 to 12, because anything else does not represent a valid month. + The input-string-element whose position corresponds with dd must be an integer from 1 to 31, because anything else does not represent a valid day. + However, if MM is 2, dd cannot be 30 or 31, because February never has such days. However, for months or days, the default starting value substituted for the omitted parts is 01. For example, using '2009-09-16 03:15:24' as your input string, you can obtain a TIMESTAMP containing only the date, with zeros for the other fields such as hours, minutes, or seconds, by specifying ```  CHAR_TO_TIMESTAMP('yyyy-MM-dd','2009-09-16 03:15:24'). ``` The result would be the TIMESTAMP 2009-09-16 00:00:00. ### Example 2 + If the call had kept hours and minutes in the template while omitting months, days, and seconds, as illustrated in the following call --- --- CHAR\$1TO\$1TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24') --- --- then the resulting TIMESTAMP would be 2009-01-01 03:15:00. | Template | Input String | Output TIMESTAMP | Notes | | --- | --- | --- | --- | | 'yyyy-MM-dd hh:mm:ss' | '2009-09-16 03:15:24' | '2009-09-16 03:15:24' | Input string MUST use the form 'yyyy-MM-dd hh:mm:ss' or a subset or reordering thereof; using an input string like 'Wednesday, 16 September 2009 03:15:24' will NOT work, meaning that no output will result. | | 'yyyy-mm' | '2012-02-08 07:23:19' | '2012-01-01 00:02:00' | The template above specifies only year first and minutes second, so the second element in the input string ("02") is used as minutes. Default values are used for Month and Day ("01") and for hours and seconds ("00"). | | 'yyyy-ss-mm' | '2012-02-08 07:23:19' | '2012-01-01 00:08:02' | The template above specifies only year, seconds, and minutes, in that order, so the second element in the input string ("02") is used as seconds and the third as minutes ("08"). Default values are used for Month and Day ("01") and for hours ("00"). | | 'MMM dd, yyyy' | 'March 7, 2010' | '2010-03-07 00:00:00' | MMM in the template above matches "March"; the template's 'comma space' matches the input string. --- --- If the template lacks the comma, so must the input string, or there is no output; --- --- If the input string lacks the comma, so must the template. | | 'MMM dd,' | 'March 7, 2010' | '1970-03-07 00:00:00' | Note that the template above doesn't use a year specifier, causing the output TIMESTAMP to use the earliest year in this epoch, 1970. | | 'MMM dd,y' | 'March 7, 2010' | '2010-03-07 00:00:00' | Using the template above, if the input string were 'March 7, 10', the output TIMESTAMP would be '0010-03-07 00:00:00'. | | 'M-d' | '2-8' | '1970-02-08 00:00:00' | Absent a yyyy specifier in the template, as above, the earliest year in this epoch (1970) is used. An input string of '2-8-2012' would give the same result; using '2012-2-8' would give no result because 2012 is not a valid month. | | 'MM-dd-yyyy' | '06-23-2012 10:11:12' | '2012-06-23 00:00:00' | Dashes as delimiters (as above) are fine, if template and input both use them in the same positions. Since the template omits hours, minutes, and seconds, zeroes are used in the output TIMESTAMP. | | 'dd-MM-yy hh:mm:ss' | '23-06-11 10:11:12' | '2011-06-23 10:11:12' | You can have the specifiers in any order as long as that order matches the meaning of the input string you supply, as above. The template and input string of the next example below have the same meaning (and the same output TIMESTAMP) as this example, but they specify months before days and seconds before hours. | | 'MM-dd-yy ss:hh:mm' | '06-23-11 12:10:11' | '2011-06-23 10:11:12' | In the template used above, the order of the month and day specifiers is reversed from the example just above, and the specifier for seconds is before hours instead of after minutes; but because the input string also puts months before days and seconds before hours, the meaning (and the output TIMESTAMP) is the same as the example ABOVE. | | 'yy-dd-MM ss:hh:mm' | '06-23-11 12:10:11' | '2006-11-23 10:11:12' | The template used above reverses (compared to the prior example above) the years and months specifiers, while the input string remains the same. In this case, the output TIMESTAMP uses the first element of the input string as the years, the second as the days, and the third as the months. | | 'dd-MM-yy hh:mm' | '23-06-11 10:11:12' | '2011-06-23 10:11:00' | With seconds omitted in the template, as above, the output TIMESTAMP uses 00 seconds. Any number of y specifiers produces the same result; but if the input string inadvertently uses a 1 instead of 11 for the year, as in '23-06-1 10:11:12', then the output TIMESTAMP becomes '0001-06-23 10:11:00'. | | 'MM/dd/yy hh:mm:ss' | `'12/19/11 10:11:12'` `'12/19/11 12:11:10'` | `'2011-12-19 10:11:12'` `'2011-12-19 00:11:10'` | Slashes as delimiters are fine, if template and input both use them in the same positions, as above; otherwise, no output. Using specifier hh, input times of 12:11:10 and 00:11:10 have the same meaning as a time in the morning. | | 'MM/dd/yy HH:mm:ss' | '12/19/11 12:59:59' '12/19/11 21:08:07' | '2011-12-19 12:59:59' '2011-12-19 21:08:07' | The input-string values '2011-12-19 00:11:12' or '2011-12-19 12:11:12' would fail with this template because '2011' is not a month, as required/expected by the template-string 'MM/dd/yy HH:mm:ss'. However, changing the template gives useful output:

values(cast(CHAR_TO_TIMESTAMP('y/MM/dd HH:mm:ss', '2011/12/19 00:11:12') as varchar(19)));
'EXPR$0'
'2011-12-19 00:11:12'
1 row selected
`'12/19/11 00:11:12'` would fail with the above template (`'y/MM/dd'`), since 19 is not a valid month; supplying `'12/11/19 00:11:12'` works. `'2011-12-19 12:11:12'` would fail as input because dashes don't match the slashes in the template ; `'2011/12/19 12:11:12'` works. Note that for times after 12 noon, that is, for afternoon and evening times, the hours specifier must be HH instead of hh, and the input string must specify the afternoon or evening hour in 24-hour clock time, hours running from 00 to 23. --- --- Using specifier HH, input times of 12:11:10 and 00:11:10 have different meanings, the first as a time in the afternoon and the second as a time in the morning. --- --- Using the specifier hh, the times from 12:00 through 11:59:59 are morning times: --- --- Given the specifiers hh:mm:ss, the output TIMESTAMP will include '00:09:08' in the morning for both input string '12:09:08' and input string '00:09:08'; --- --- whereas --- --- Given the specifiers HH:mm:ss, the output TIMESTAMP for input string '00:09:08' in the morning will include '00:09:08' --- --- and the output TIMESTAMP for input string '12:09:08' in the afternoon will include '12:09:08'. | ### Further examples The examples below illustrate using various templates with CHAR\$1TO\$1TIMESTAMP, including some common misunderstandings. ``` values (CHAR_TO_TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24')); 'EXPR$0' '2009-01-01 09:16:00' 1 row selected ``` Note that the fields in the input string above were used in the order given by the specifiers in the template, as defined by the dashes-as-delimiters in both template and input string: years first, then hours, then minutes. Since the specifiers for months and days are not present in the template, their values in the input string were ignored, with 01 substituted for both values in the output TIMESTAMP. The template specified hours and minutes as the second and third input values, so 09 became the hours and 16 became the minutes. No specifier was present for seconds, so 00 was used. The years specifier can be alone or, after a delimiter matching the input string shows the end of the years specifier, with one of the hours:minutes:seconds specifiers: ``` values (CHAR_TO_TIMESTAMP('yyyy','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 00:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected ``` The template above fails because it has a space-as-delimiter before the "hh" rather than the dash delimiter used in the input string's date specification; whereas the four templates below work because they use the same delimiter to separate the years specifier from the next specifier as is used in the input string's date specification (dash in the first case, space in the second, slash in the third, and dash in the fourth). ``` values (CHAR_TO_TIMESTAMP('yyyy-hh','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy hh','2009 09 16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy/hh','2009/09/16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy-mm','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 00:09:00' 1 row selected ``` However, if the template specifies months (MM), it cannot then specify hours, minutes, or seconds unless days are also specified: Template specifying years and months only, thus omitting days/hours/minutes/seconds from the resulting TIMESTAMP: ``` values (CHAR_TO_TIMESTAMP('yyyy-MM','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-01 00:00:00' 1 row selected ``` The next two templates fail, lacking a 'days' specifier: ``` values (CHAR_TO_TIMESTAMP('yyyy-MM hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected values (CHAR_TO_TIMESTAMP('yyyy-MM hh:','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected ``` The next three succeed, using a 'days' specifier: ``` values (CHAR_TO_TIMESTAMP('yyyy-MM-dd hh','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-16 03:00:00' 1 row selected ``` The template above, 'yyyy-MM-dd hh', specifies only hours (hh) without minutes or seconds. Since hh is the 4th token/element of the template, its value is to be taken from the 4th token/element of the input string '2009-09-16 03:15:24' ; and that 4th element is 03, then used as the value output for hours. Since neither mm or ss is specified, the default or initial values defined as the starting point for mm and ss are used, which are zeroes. ``` values (CHAR_TO_TIMESTAMP('yyyy-MM-dd ss','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-16 00:00:03' 1 row selected ``` The template above, 'yyyy-MM-dd ss', specifies that the 4th token/element of the input string is to be used as seconds (ss). The 4th element of the input string '2009-09-16 03:15:24' is 03, which becomes the value output for seconds as specified in the template; and since neither hh nor mm is specified in the template, their default or initial values are used, which are zeroes. ``` values (CHAR_TO_TIMESTAMP('yyyy-MM-dd mm','2009-09-16 03:15:24') ); 'EXPR$0' '2009-09-16 00:03:00' 1 row selected ``` The template above, 'yyyy-MM-dd mm', specifies that the 4th token/element of the input string is to be used as minutes (mm). The 4th element of the input string '2009-09-16 03:15:24' is 03, which becomes the value output for minutes as specified in the template; and since neither hh nor ss is specified in the template, their default or initial values are used, which are zeroes. Further failures, lacking a 'days' specifier: ``` values (CHAR_TO_TIMESTAMP('yyyy-MM- mm','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected values (CHAR_TO_TIMESTAMP('yyyy-MM   mm','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected values (CHAR_TO_TIMESTAMP('yyyy-MM   hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected ``` ## About Delimiters and Values Delimiters in the template must match those in the input string; values in the input string must be acceptable for the template specifiers to which they correspond. As a general convention, a colon is used to separate hours from minutes, and minutes from seconds. Similarly, the general convention is to use a dash or slash to separate years from months and months from days. Any parallel usage seems to work, and the examples that follow illustrate this. ``` values (CHAR_TO_TIMESTAMP('MM/dd/yy hh:mm:ss','2009/09/16 03:15:24') ); 'EXPR$0' No rows selected ``` The example above fails because 2009 is not an acceptable value for months, which is the first specifier (MM) in the template. ``` values (CHAR_TO_TIMESTAMP('MM/dd/yy hh:mm:ss','09/16/11 03:15:24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected ``` The example above succeeds because the delimiters are parallel (slashes to slashes, colons to colons) and each value is acceptable for the corresponding specifier. ``` values (CHAR_TO_TIMESTAMP('MM/dd/yy hh/mm/ss','09/16/11 03/15/24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected ``` The example above succeeds because the delimiters are parallel (all slashes) and each value is acceptable for the corresponding specifier. ``` values (CHAR_TO_TIMESTAMP('MM/dd/yy hh-mm-ss','09/16/11 03-15-24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy|MM|dd hh|mm|ss','2009|09|16 03|15|24') ); 'EXPR$0' '2009-09-16 03:15:24' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy@MM@dd hh@mm@ss','2009@09@16 03@15@24') ); 'EXPR$0' '2009-09-16 03:15:24' 1 row selected ``` The examples above succeed because the delimiters are parallel and the values are acceptable per specifier. In the following examples, note that omissions in the supplied string can cause the template value 'yyyy' to produce logical but unintended or unexpected results. The value given as the year in the resulting TIMESTAMP value derives directly from the first element in the supplied string. ``` VALUES(CHAR_TO_TIMESTAMP('yyyy','09-16 03:15')); 'EXPR$0' '0009-01-01 00:00:00' 1 row selected VALUES(CHAR_TO_TIMESTAMP('yyyy','16 03:15')); 'EXPR$0' '0016-01-01 00:00:00' 1 row selected ``` ## TIMESTAMP to STRING ``` values(cast( TIMESTAMP '2007-02-19 21:25:35' AS VARCHAR(25))); 'EXPR$0' '2007-02-19 21:25:35' 1 row selected ``` Note that CAST requires a TIMESTAMP-literal to have literally the full format of 'yyyy-mm-dd hh:mm:ss'. If any part of that full format is missing, the literal is rejected as illegal, as seen below: ``` values( TIMESTAMP '2007-02-19 21:25'); Error: Illegal TIMESTAMP literal '2007-02-19 21:25':                                  not in format 'yyyy-MM-dd HH:mm:ss' (state=,code=0) values( TIMESTAMP '2007-02-19 21:25:00'); 'EXPR$0' '2007-02-19 21:25:00' 1 row selected ``` Also, if an inadequate output specification is supplied, no rows are selected: ``` values(cast( TIMESTAMP '2007-02-19 21:25:35' AS VARCHAR(18))); 'EXPR$0' No rows selected (Because the timestamp literal requires 19 characters) ``` These restrictions apply similarly to CASTing to TIME or DATE types. ## STRING to TIME ``` values(cast(' 21:23:45.0' AS TIME)); 'EXPR$0' '21:23:45' 1 row selected ``` For more information, see Note A. ## STRING to DATE ``` values(cast('2007-02-19' AS DATE)); 'EXPR$0' '2007-02-19' 1 row selected ``` **Note A** Note that CAST for strings requires that the string operand for casting to TIME or DATE have the exact form required to represent a TIME or DATE, respectively. As shown below, the cast fails if: + the string operand includes data extraneous to the targeted type, or + the INTERVAL operand ( 'day hours:minutes:seconds.milliseconds' ) does not include necessary data, or + the specified output field is too small to hold the conversion results. ``` values(cast('2007-02-19 21:23:45.0' AS TIME)); 'EXPR$0' No rows selected ``` Fails because it includes date information not allowed as a TIME. ``` values(cast('2007-02-19 21:23:45.0' AS DATE)); 'EXPR$0' No rows selected ``` Fails because it includes time information not allowed as a DATE. ``` values(cast('2007-02-19 21' AS DATE)); 'EXPR$0' No rows selected ``` Fails because it includes time information not allowed as a DATE. ``` values(cast('2009-02-28' AS DATE)); 'EXPR$0' '2009-02-28' 1 row selected ``` Succeeds because it includes a correct representation of date string. ``` values(CAST (cast('2007-02-19 21:23:45.0' AS TIMESTAMP) AS DATE)); 'EXPR$0' '2007-02-19' 1 row selected ``` Succeeds because it correctly converts string to TIMESTAMP before casting to DATE. ``` values(cast('21:23' AS TIME)); 'EXPR$0' No rows selected ``` Fails because it lacks time information (seconds) required for a TIME. (Specifying fractional seconds is allowed but not required.) ``` values(cast('21:23:34:11' AS TIME)); 'EXPR$0' No rows selected ``` Fails because it includes incorrect representation of fractional seconds. ``` values(cast('21:23:34.11' AS TIME)); 'EXPR$0' '21:23:34' 1 row selected ``` Succeeds because it includes correct representation of fractional seconds. ``` values(cast('21:23:34' AS TIME)); 'EXPR$0' '21:23:34' 1 row selected ``` This example succeeds because it includes correct representation of seconds without fractions of a second. ## INTERVAL to exact numerics CAST for intervals requires that the INTERVAL operand have only one field in it, such as MINUTE, HOUR, SECOND. If the INTERVAL operand has more than one field, such as MINUTE TO SECOND, the cast fails, as shown below: ``` values ( cast (INTERVAL '120' MINUTE(3) as decimal(4,2))); +---------+ | EXPR$0  | +---------+ +---------+ No rows selected values ( cast (INTERVAL '120' MINUTE(3) as decimal(4))); +---------+ | EXPR$0  | +---------+ | 120     | +---------+ 1 row selected values ( cast (INTERVAL '120' MINUTE(3) as decimal(3))); +---------+ | EXPR$0  | +---------+ | 120     | +---------+ 1 row selected values ( cast (INTERVAL '120' MINUTE(3) as decimal(2))); +---------+ | EXPR$0  | +---------+ +---------+ No rows selected values cast(interval '1.1' second(1,1) as decimal(2,1)); +---------+ | EXPR$0  | +---------+ | 1.1     | +---------+ 1 row selected values cast(interval '1.1' second(1,1) as decimal(1,1)); +---------+ | EXPR$0  | +---------+ +---------+ No rows selected ``` For year, decimal fractions are disallowed as input and as output. ``` values cast(interval '1.1' year (1,1) as decimal(2,1)); Error: org.eigenbase.sql.parser.SqlParseException: Encountered "," at line 1, column 35. Was expecting:    ")" ... (state=,code=0) values cast(interval '1.1' year (1) as decimal(2,1)); Error: From line 1, column 13 to line 1, column 35:              Illegal interval literal format '1.1' for INTERVAL YEAR(1) (state=,code=0) values cast(interval '1.' year (1) as decimal(2,1)); Error: From line 1, column 13 to line 1, column 34:              Illegal interval literal format '1.' for INTERVAL YEAR(1) (state=,code=0) values cast(interval '1' year (1) as decimal(2,1)); +---------+ | EXPR$0  | +---------+ | 1.0     | +---------+ 1 row selected ``` For additional examples, see SQL Operators: Further examples. ## Limitations Amazon Kinesis Data Analytics does not support directly casting numeric values to interval values. This is a departure from the SQL:2008 standard. The recommended way to convert a numeric to an interval is to multiply the numeric value against a specific interval value. For example, to convert the integer time\$1in\$1millis to a day-time interval: ``` time_in_millis * INTERVAL '0 00:00:00.001' DAY TO SECOND ``` For example: ``` values cast( 5000 * (INTERVAL '0 00:00:00.001' DAY TO SECOND) as varchar(11)); 'EXPR$0' '5000' 1 row selected ``` # Date and Time Functions The following built-in functions relate to dates and time. **Topics** + [Time Zones](#sql-reference-date-time-functions-time-zones) + [Datetime Conversion Functions](sql-reference-datetime-conversion-functions.md) + [Date, Timestamp, and Interval Operators](sql-reference-date-timestamp-interval.md) + [Date and Time Patterns](sql-reference-parse-timestamp-format.md) + [CURRENT\$1DATE](sql-reference-current-date.md) + [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md) + [CURRENT\$1TIME](sql-reference-current-time.md) + [CURRENT\$1TIMESTAMP](sql-reference-current-timestamp.md) + [EXTRACT](sql-reference-extract.md) + [LOCALTIME](sql-reference-localtime.md) + [LOCALTIMESTAMP](sql-reference-local-timestamp.md) + [TSDIFF](sql-reference-tsdiff.md) Of these, the SQL extension CURRENT\$1ROW\$1TIMESTAMP is the most useful for a streaming context, because it gives you information about the times of streaming data as it emerges, not just when the query is run. This is a key difference between a streaming query and a traditional RDMS query: streaming queries remain "open," producing more data, so the timestamp for when the query was run does not offer good information. LOCALTIMESTAMP, LOCALTIME, CURRENT\$1DATE, and CURRENT\$1TIMESTAMP all produce results which are set to values at the time the query first executes. Only CURRENT\$1ROW\$1TIMESTAMP generates a row with a unique timestamp (date and time) for each row. A query run with LOCALTIMESTAMP (or CURRENT\$1TIMESTAMP or CURRENT\$1TIME) as one of the columns puts into all output rows the time the query is first run. If that column instead contains CURRENT\$1ROW\$1TIMESTAMP, each output row gets a newly-calculated value of TIME representing when that row was output. To return a part (such as the day of the month) from a Datetime value, use [EXTRACT](sql-reference-extract.md) ## Time Zones Amazon Kinesis Data Analytics runs in UTC. As a result, all time functions return time in UTC. # Datetime Conversion Functions You specify date and time formats using patterned letters. Date and time pattern strings use unquoted letters from 'A' to 'Z' and from 'a' to 'z', with each letter representing a formatting element. For more information, see [Class SimpleDateFormat](http://docs.oracle.com/javase/7/docs/api/index.html?java/text/SimpleDateFormat.html) on the Oracle website. **Note** If you include other characters, they will be incorporated into the output string during formatting or compared to the input string during parsing. The pattern letters in the following table are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved). | Letter | Date or Time Component | Presentation | Examples | | --- | --- | --- | --- | | y | Year | Year | yyyy; yy 2018;18 | | Y | Week year | Year | YYYY; YY 2009; 09 | | M | Month in year | Month | MMM;MM;MM July; Jul; 07 | | w | Week in year | Number | ww; 27 | | W | Week in month | Number | W 2 | | D | Day in year | Number | DDD 321 | | d | Day in month | Number | dd 10 | | F | Day of week in month | Number | F 2 | | E | Day name in week | Text | Tuesday; Tue | | u | Day number of week (1 = Monday, ..., 7 = Sunday) | Number | 1 | | a | Am/pm marker | Text | PM | | H | Hour in day (0-23) | Number | 0 | | k | Hour in day (1-24) | Number | 24 | | K | Hour in am/pm (0-11) | Number | 0 | | h | Hour in am/pm (1-12) | Number | 12 | | m | Minute in hour | Number | 30 | | s | Second in minute | Number | 55 | | S | Millisecond | Number | 978 | | z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 | | Z | Time zone | RFC 822 time zone | -0800 | | X | Time zone | ISO 8601 time zone | -08; -0800; -08:00 | You determine the exact presentation by repeating pattern letters, along the lines of YYYY. **Text** If the number of repeated pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available. For parsing, both forms are accepted, independent of the number of pattern letters. **Number** For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. For parsing, the number of pattern letters is ignored unless it's needed to separate two adjacent fields. **Year** If the formatter's Calendar is the Gregorian calendar, the following rules are applied. + For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a number. + For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits. So using the pattern "MM/dd/yyyy", "01/11/12" parses to Jan 11, 12 A.D. For parsing with the abbreviated year pattern ("y" or "yy"), SimpleDateFormat must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the time the SimpleDateFormat instance is created. For example, using a pattern of "MM/dd/yy" and a SimpleDateFormat instance created on Jan 1, 2018, the string "01/11/12" would be interpreted as Jan 11, 2012 while the string "05/04/64" would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits, as defined by Character.isDigit(char), will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that isn't all digits (for example, "-1"), is interpreted literally. So "01/02/3" or "01/02/003" are parsed, using the same pattern, as Jan 2, 3 AD. Likewise, "01/02/-3" is parsed as Jan 2, 4 BC. Otherwise, calendar system specific forms are applied. For both formatting and parsing, if the number of pattern letters is 4 or more, a calendar specific long form is used. Otherwise, a calendar specific short or abbreviated form is used. # Char To Timestamp(Sys) The Char to Timestamp function is one of the most frequently-used system functions, because it lets you create a timestamp out of any correctly formatted input string. Using this function, you can specify which parts of the timestamp string you wish to use in subsequent processing, and create a TIMESTAMP value containing only those. To do so, you specify a template that identifies the parts of the timestamp you want. For example, to use only year and month, you would specify 'yyyy-MM'. The input date-time string can contain any parts of a full timestamp ('yyyy-MM-dd hh:mm:ss'). If all these elements are present in your input string, and 'yyyy-MM-dd hh:mm:ss' is the template you supply, then the input-string elements are interpreted in that order as year, month, day, hour, minute, and seconds, such as in '2009-09-16 03:15:24'. The yyyy cannot be uppercase; the hh can be uppercase to mean using a 24-hour clock. For the full range of valid specifiers, see [Class SimpleDateFormat](http://docs.oracle.com/javase/7/docs/api/index.html?java/text/SimpleDateFormat.html) on the Oracle website. CHAR\$1TO\$1TIMESTAMP uses the template you specify as a parameter in the function call. The template causes the TIMESTAMP result to use only the parts of the input-date-time value that you specified in the template. Those fields in the resulting TIMESTAMP contain the corresponding data taken from your input-date-time string. Fields not specified in your template will use default values (see below). The format of the template used by CHAR\$1TO\$1TIMESTAMP is defined by the [Class SimpleDateFormat](http://docs.oracle.com/javase/7/docs/api/index.html?java/text/SimpleDateFormat.html) on the Oracle website. For more information, see [Date and Time Patterns](sql-reference-parse-timestamp-format.md). The function-call syntax is as follows: ``` CHAR_TO_TIMESTAMP('','') ``` Where is the template you specify for the parts of you want, and is the original string that is being converted to a TIMESTAMP result. Note that each string must be enclosed in single quotes and each element of the must be in the range for its corresponding element in the template, otherwise no result is returned. For example, the input-string-element whose position corresponds with MM must be an integer from 1 to 12, because anything else does not represent a valid month. Similarly, the input-string-element whose position corresponds with dd must be an integer from 1 to 31, because anything else does not represent a valid day. (However, if MM is 2, dd cannot be 30 or 31, because February never has such days.) For hours, minutes, or seconds, the default starting value is zero, so when those specifiers are omitted from the template, zeroes are substituted. For months or days, the default starting value substituted for the omitted parts is 01. For example, using '2009-09-16 03:15:24' as your input string, you can obtain a TIMESTAMP containing only the date, with zeros for the other fields such as hours, minutes, or seconds. ``` CHAR_TO_TIMESTAMP('yyyy-MM-dd','2009-09-16 03:15:24'). ``` The result would is TIMESTAMP 2009-09-16 00:00:00. If the call had kept hours and minutes in the template while omitting months, days, and seconds, as illustrated in the following call. ``` --- --- CHAR_TO_TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24') ``` Then, the resulting TIMESTAMP would be 2009-01-01 03:15:00. [Template Strings to Create Specific Output Timestamps](sql-reference-template-strings-create-output-timestamps.md) shows further illustrative examples of templates and input strings used to create the indicated output TIMESTAMPs. **Note** Input string MUST use the form 'yyyy-MM-dd hh:mm:ss' or a subset or reordering thereof. As a result, using an input string like 'Wednesday, 16 September 2009 03:15:24' will NOT work, meaning that no output will result. ## About Delimiters and Values Delimiters in the template must match those in the input string and values in the input string must be acceptable for the template specifiers to which they correspond. As a general convention, a colon is used to separate hours from minutes, and minutes from seconds. Similarly, the general convention is to use a dash or slash to separate years from months and months from days. For example, the following template has values that line up correctly with the input string. ``` values (CHAR_TO_TIMESTAMP('MM/dd/yy hh:mm:ss','09/16/11 03:15:24') ); 'EXPR$0' '2011-09-16 03:15:24' 1 row selected ``` If values in the input string are not acceptable for the template specifiers to which they correspond, the result fails, as in the following example. ``` values (CHAR_TO_TIMESTAMP('MM/dd/yy hh:mm:ss','2009/09/16 03:15:24') ); 'EXPR$0' No rows selected ``` This example returns no rows because 2009 is not an acceptable value for months, which is the first specifier (MM) in the template. Omissions in the supplied string can cause the template value 'yyyy' to produce logical but unintended or unexpected results. The following examples each return an erroneous year, but one that derives directly from the first element in the supplied string. ``` VALUES(CHAR_TO_TIMESTAMP('yyyy','09-16 03:15')); 'EXPR$0' '0009-01-01 00:00:00' 1 row selected VALUES(CHAR_TO_TIMESTAMP('yyyy','16 03:15')); 'EXPR$0' '0016-01-01 00:00:00' 1 row selected ``` ## Examples Using Templates to Create TIMESTAMPS The order of the template must match the input string. That means that you cannot specify "hh" after "yyyy" and expect the method to find the hour automatically. For example, the following template specifies years first, then hours, then minutes, and returns an erroneous result. ``` values (CHAR_TO_TIMESTAMP('yyyy-hh-mm','2009-09-16 03:15:24')); 'EXPR$0' '2009-01-01 09:16:00' 1 row selected ``` Since the specifiers for months and days are not present in the template, their values in the input string were ignored, with 01 substituted for both values in the output TIMESTAMP. The template specified hours and minutes as the second and third input values, so 09 became the hours and 16 became the minutes. No specifier was present for seconds, so 00 was used. The years specifier can be alone or after a delimiter matching the input string shows the end of the years specifier, with one of the hours:minutes:seconds specifiers. ``` values (CHAR_TO_TIMESTAMP('yyyy','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 00:00:00' 1 row selected ``` In contrast, the template below fails because it has a space-as-delimiter before the "hh" rather than the dash delimiter used in the input string's date specification. ``` values (CHAR_TO_TIMESTAMP('yyyy hh','2009-09-16 03:15:24') ); 'EXPR$0' No rows selected ``` The four templates below work because they use the same delimiter to separate the years specifier from the next specifier as is used in the input string's date specification (dash in the first case, space in the second, slash in the third, and dash in the fourth). ``` values (CHAR_TO_TIMESTAMP('yyyy-hh','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy hh','2009 09 16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy/hh','2009/09/16 03:15:24') ); 'EXPR$0' '2009-01-01 09:00:00' 1 row selected values (CHAR_TO_TIMESTAMP('yyyy-mm','2009-09-16 03:15:24') ); 'EXPR$0' '2009-01-01 00:09:00' 1 row selected ``` However, if the template specifies months (MM), it cannot then specify hours, minutes, or seconds unless days are also specified. # Template Strings to Create Specific Output Timestamps | Template | Input String | Output TIMESTAMP | Notes | | --- | --- | --- | --- | | 'yyyy-MM-dd hh:mm:ss' | '2009-09-16 03:15:24' | '2009-09-16 03:15:24' | | 'yyyy-mm' | '2011-02-08 07:23:19' | '2011-01-01 00:02:00' | The template above specifies only year first and minutes second, so the second element in the input string ("02") is used as minutes. Default values are used for Month and Day ("01") and for hours and seconds ("00"). | | 'MMM dd, yyyy' | 'March 7, 2010' | '2010-03-07 00:00:00' | MMM in the template above matches "March"; the template's 'comma space' matches the input string. If the template lacks the comma, so must the input string, or there is no output; If the input string lacks the comma, so must the template. | | 'MMM dd,' | 'March 7, 2010' | '1970-03-07 00:00:00' | Note that the template above doesn't use a year specifier, causing the output TIMESTAMP to use the earliest year in this epoch, 1970. | | 'MMM dd,y' | 'March 7, 2010' | '2010-03-07 00:00:00' | Using the template above, if the input string were 'March 7, 10', the output TIMESTAMP would be '0010-03-07 00:00:00'. | | 'M-d' | '2-8' | '1970-02-08 00:00:00' | Absent a yyyy specifier in the template, as above, the earliest year in this epoch (1970) is used. An input string of '2–8−2011' would give the same result; using '2011–2−8' would give no result because 2011 is not a valid month. | | 'MM-dd-yyyy' | '06-23-2011 10:11:12' | '2011-06-23 00:00:00' | Dashes as delimiters (as above) are fine, if template and input both use them in the same positions. Since the template omits hours, minutes, and seconds, zeroes are used in the output TIMESTAMP. | | `'dd-MM-yy hh:mm:ss'` | `'23-06-11 10:11:12'` | `'2011-06-23 10:11:12'` | You can have the specifiers in any order as long as that order matches the meaning of the input string you supply. The template and input string of the next example below have the same meaning (and the same output TIMESTAMP) as this example, but they specify months before days and seconds before hours. | | `'MM-dd-yy ss:hh:mm'` | `'06-23-11 12:10:11'` | `'2011-06-23 10:11:12'` | In the template used above, the order of the month and day specifiers is reversed from the example just above, and the specifier for seconds is before hours instead of after minutes; but because the input string also puts months before days and seconds before hours, the meaning (and the output TIMESTAMP) is the same as the example ABOVE. | | `'yy-dd-MM ss:hh:mm'` | `'06-23-11 12:10:11'` | `'2006-11-23 10:11:12'` | The template used above reverses (compared to the prior example above) the years and months specifiers, while the input string remains the same. In this case, the output TIMESTAMP uses the first element of the input string as the years, the second as the days, and the third as the months. | | `'dd-MM-yy hh:mm'` | `'23-06-11 10:11:12'` | `'2011-06-23 10:11:00'` | With seconds omitted in the template, as above, the output TIMESTAMP uses 00 seconds. Any number of y specifiers produces the same result; but if the input string inadvertently uses a 1 instead of 11 for the year, as in '23-06-1 10:11:12', then the output TIMESTAMP becomes '0001-06-23 10:11:00'. | | `'MM/dd/yy hh:mm:ss'` | `'12/19/11 10:11:12'` `'12/19/11 12:11:12'` | `'2011-12-19 10:11:12'` `'12/19/11 00:11:12'` | Slashes as delimiters are fine, if template and input both use them in the same positions, as above. Using specifier hh, input times of 12:11:10 and 00:11:10 have the same meaning as a time in the morning. | | `'MM/dd/yy HH:mm:ss'` | `'12/19/11 12:59:59'` `'12/19/11 21:08:07'` `'2011-12-19 00:11:12'` `'2011-12-19 12:11:12'` | `'2011-12-19 12:59:59'` `'2011-12-19 21:08:07'` | The input-string values `'2011-12-19 00:11:12'` or `'2011-12-19 12:11:12'` would fail with this template because `'2011'` is not a month, as required/expected by the template-string `'MM/dd/yy HH:mm:ss'`. However, changing the template gives useful output: 
values(cast(CHAR_TO_TIMESTAMP('y/MM/dd HH:mm:ss', '2011/12/19 00:11:12') as
varchar(19)));
'EXPR$0'
'2011-12-19 00:11:12'
1 row selected `'12/19/11 00:11:12'` would fail with the above template (`'y/MM/dd'`), since 19 is not a valid month; supplying '`12/11/19 00:11:12'` works. `'2011-12-19 12:11:12'` would fail as input because dashes don't match the slashes in the template, `'2011/12/19 12:11:12'` works. Note that for times after 12 noon (that is, for afternoon and evening times), the hours specifier must be HH instead of hh, and the input string must specify the afternoon or evening hour in 24-hour clock time, hours running from 00 to 23. Using specifier HH, input times of 12:11:10 and 00:11:10 have different meanings, the first as a time in the afternoon and the second as a time in the morning. Using the specifier hh, the times from 12:00 through 11:59:59 are morning times: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/sql-reference-template-strings-create-output-timestamps.html) | # CHAR\$1TO\$1DATE Converts a string to a date, according to the specified format string. ``` CHAR_TO_DATE(format,dateString); ``` # CHAR\$1TO\$1TIME Converts a string to a date, according to the specified format string ``` CHAR_TO_TIME(format,dateString); ``` # DATE\$1TO\$1CHAR The DATE\$1TO\$1CHAR converts a date to a string. ``` DATE_TO_CHAR(format,d); ``` Where d is a date that will be converted to a string. # TIME\$1TO\$1CHAR Uses a format string to format a time. Returns the formatted time or portion of a time as a string. ``` TIME_TO_CHAR(format,time); ``` # TIMESTAMP\$1TO\$1CHAR Uses a format string to format a timestamp as char. Returns the timestamp as a string. ``` TIMESTAMP_TO_CHAR(format,ts); ``` Where ts is timestamp. **Note** If the input is `null`, the output will be the string "`null`". # TO\$1TIMESTAMP Converts a Unix timestamp to a SQL timestamp in 'YYYY-MM-DD HH:MM:SS' format. ## Syntax ``` TO_TIMESTAMP(unixEpoch) ``` ## Parameters *unixEpoch* A Unix timestamp in the format milliseconds since '1970-01-01 00:00:00' UTC, expressed as a BIGINT. ## Example ### Example Dataset The examples following are based on the sample stock dataset that is part of [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. **Note** The sample dataset has been modified to include a Unix timestamp value (CHANGE\$1TIME). To run each example, you need an Amazon Kinesis Analytics application that has the input stream for the sample stock ticker. To learn how to create an Analytics application and configure the input stream for the sample stock ticker, see [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, change_time BIGINT, --The UNIX timestamp value price REAL) ``` ### Example 1: Convert a Unix Timestamp to a SQL Timestamp In this example, the `change_time` value in the source stream is converted to a SQL TIMESTAMP value in the in-application stream. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), sector VARCHAR(64), change REAL, change_time TIMESTAMP, price REAL); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM TICKER_SYMBOL, SECTOR, CHANGE, TO_TIMESTAMP(CHANGE_TIME), PRICE FROM "SOURCE_SQL_STREAM_001" ``` The preceding example outputs a stream similar to the following. ![\[Table showing stock data with columns for time, ticker symbol, sector, change, and price.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-to-timestamp.png) ## Notes TO\$1TIMESTAMP is not part of the SQL:2008 standard. It is an Amazon Kinesis Data Analytics streaming SQL extension. # UNIX\$1TIMESTAMP Converts a SQL timestamp to a Unix timestamp that is expressed in milliseconds since '1970-01-01 00:00:00' UTC and that is a BIGINT. ## Syntax ``` UNIX_TIMESTAMP(timeStampExpr) ``` ## Parameters *timeStampExpr* A SQL TIMESTAMP value. ## Example ### Example Dataset The examples following are based on the sample stock dataset that is part of [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. **Note** The sample dataset has been modified to include a Timestamp value (CHANGE\$1TIME). To run each example, you need an Amazon Kinesis Analytics application that has the input stream for the sample stock ticker. To learn how to create an Analytics application and configure the input stream for the sample stock ticker, see [Getting Started Exercise](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. The sample stock dataset has the schema following. ``` (ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, change_time TIMESTAMP, --The timestamp value to convert price REAL) ``` ### Example 1: Convert a Timestamp to a UNIX Timestamp In this example, the `change_time` value in the source stream is converted to a TIMESTAMP value in the in-application stream. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), SECTOR VARCHAR(16), CHANGE REAL, CHANGE_TIME BIGINT, PRICE REAL); CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM TICKER_SYMBOL, SECTOR, CHANGE, UNIX_TIMESTAMP(CHANGE_TIME), PRICE FROM "SOURCE_SQL_STREAM_001" ``` The preceding example outputs a stream similar to the following. ![\[Table showing stock data with columns for time, ticker symbol, sector, change, and price.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-unix-timestamp.png) ## Notes UNIX\$1TIMESTAMP is not part of the SQL:2008 standard. It is an Amazon Kinesis Data Analytics streaming SQL extension. # Date, Timestamp, and Interval Operators The arithmetic operators \$1, -, \$1, and / are binary operators. | Operator | Description | Notes | | --- | --- | --- | | \$1 | Addition | interval \$1 interval = interval interval \$1 datetime = datetime datetime \$1 interval = datetime | | - | Subtraction | interval - interval = interval datetime - interval = datetime ( - ) [Date, Timestamp, and Interval Operators](#sql-reference-date-timestamp-interval) = interval | | \$1 | Multiplication | interval \$1 numeric = interval numeric \$1 interval = interval | | / | Division | interval / numeric = interval | ## Examples | Example | Operation | Result | | --- | --- | --- | | 1 | INTERVAL '1' DAY \$1 INTERVAL '3' DAY | INTERVAL '4' DAY | | 2 | INTERVAL '1' DAY \$1 INTERVAL '3 4' DAY TO HOUR | INTERVAL '\$14 04' DAY TO HOUR | | 3 | INTERVAL '1' DAY - INTERVAL '3 4' DAY TO HOUR | INTERVAL '-2 04' DAY TO HOUR | | 4 | INTERVAL '1' YEAR \$1 INTERVAL '3-4' YEAR TO MONTH | INTERVAL '\$14-04' YEAR TO MONTH | | 5 | 2 \$1 INTERVAL '3 4' DAY TO HOUR | INTERVAL '6 8' DAY TO HOUR | | 6 | INTERVAL '3 4' DAY TO HOUR / 2 | INTERVAL ' 1 14' DAY TO HOUR | In the example 3, '3 4 DAY means 3 days and 4 hours, so the result in that row means 24 hours minus 76 hours, resulting in minus 52 hours, which is a negative 2 days and 4 hours. Example 4 uses TO MONTH rather than TO HOUR, so the INTERVAL specified as '3-4' means 3 years and 4 months, or 40 months. In example 6, the "/2" applies to the INTERVAL '3 4', which is 76 hours, half of which is 38, or 1 day and 14 hours. ### Further Examples of Interval Operations Streaming SQL also supports subtracting two datetimes, giving an interval. You specify what kind of interval you want for the result, as shown following: ``` ( - ) ``` The following examples show operations that can be useful in Amazon Kinesis Data Analytics applications. **Example 1 – Time Difference (as minutes to the nearest second or as seconds)** ``` values cast ((time  '12:03:34' - time '11:57:23') minute to second as varchar(8)); +---------+ EXPR$0   +---------+ +6:11   +---------+ 1 row selected ............... 6 minutes, 11 seconds or values cast ((time  '12:03:34' - time '11:57:23') second as varchar(8)); +---------+ EXPR$0   +---------+ +371     +---------+ 1 row selected ``` **Example 2 – Time Difference (as minutes only)** ``` values cast ((time  '12:03:34' - time '11:57:23') minute as varchar(8)); +---------+ EXPR$0   +---------+ +6       +---------+ 1 row selected ............... 6 minutes; seconds ignored. values cast ((time  '12:03:23' - time '11:57:23') minute as varchar(8)); +---------+ EXPR$0   +---------+ +6       +---------+ 1 row selected ............... 6 minutes ``` **Example 3 – Time-to-Timestamp Difference (as days to the nearest second) Invalid** ``` values cast ((time '12:03:34'-timestamp '2004-04-29 11:57:23') day to second as varchar(8)); Error: From line 1, column 14 to line 1, column 79: Parameters must be of the same type ``` **Example 4 – Timestamp difference (as days to the nearest second)** ``` values cast ((timestamp  '2004-05-01 12:03:34' - timestamp '2004-04-29 11:57:23') day to                second as varchar(8)); +-----------+  EXPR$0   +-----------+ +2 00:06   +-----------+ 1 row selected ............... 2 days, 6 minutes ............... Although "second" was specified above, the varchar(8) happens to allow only room enough to show only the minutes, not the seconds. The example below expands to varchar(11), showing the full result: values cast ((timestamp  '2004-05-01 12:03:34' - timestamp '2004-04-29 11:57:23') day to                second as varchar(11)); +--------------+    EXPR$0     +--------------+ +2 00:06:11   +--------------+ 1 row selected ............... 2 days, 6 minutes, 11 seconds ``` **Example 5 – Timestamp Difference (as days to the nearest second)** ``` values cast ((timestamp  '2004-05-01 1:03:34' - timestamp '2004-04-29 11:57:23') day to                second as varchar(11)); +--------------+    EXPR$0     +--------------+ +1 13:06:11   +--------------+ 1 row selected ............... 1 day, 13 hours, 6 minutes, 11 seconds values cast ((timestamp  '2004-05-01 13:03:34' - timestamp '2004-04-29 11:57:23') day to                second as varchar(11)); +--------------+    EXPR$0     +--------------+ +2 01:06:11   +--------------+ 1 row selected ............... 2 days, 1 hour, 6 minutes, 11 seconds ``` **Example 6 – Timestamp Difference (as days)** ``` values cast ((timestamp  '2004-05-01 12:03:34' - timestamp '2004-04-29 11:57:23') day                as varchar(8)); +---------+ EXPR$0   +---------+ +2       +---------+ 1 row selected ............... 2 days ``` **Example 7 – Time Difference (as days)** ``` values cast ((date '2004-12-02 ' - date '2003-12-01 ') day  as varchar(8)); Error: Illegal DATE literal '2004-12-02 ': not in format 'yyyy-MM-dd' .............. Both date literals end with a space;  disallowed. values cast ((date '2004-12-02' - date '2003-12-01 ') day  as varchar(8)); Error: Illegal DATE literal '2003-12-01 ': not in format 'yyyy-MM-dd' .............. Second date literal still ends with a space;  disallowed. values cast ((date '2004-12-02' - date '2003-12-01') day  as varchar(8)); +---------+ EXPR$0   +---------+ +367     +---------+ 1 row selected ............... 367 days ``` **Example 8 – Not Supported (Simple Difference of Dates)** If you don't specify "day" as the intended unit, as shown following, the subtraction is not supported. ```     values cast ((date '2004-12-02' - date '2003-12-01') as varchar(8));     Error: From line 1, column 15 to line 1, column 51:            Cannot apply '-' to arguments of type ' - '.     Supported form(s): ' - '                        ' - '                        ' - ' ``` ### Why Use "as varchar" in Conversion Examples? The reason for using the "values cast ( AS varchar(N))" syntax in the examples above is that while the SQLline client used above (with Amazon Kinesis Data Analytics running) does return an interval, JDBC does not support returning that result so as to display it. Therefore, that "values" syntax is used to see/show it. If you close the Amazon Kinesis Data Analytics (with a \$1kill command) or if you don't start it before running SQLline, then you can run the sqllineEngine (rather than the sqllineClient) from the bin subdirectory of your Amazon Kinesis Data Analytics home, which can show your results without the Amazon Kinesis Data Analytics application or JDBC: ### Rules for Specifying Intervals A Day-Time Interval Literal is a string that denotes a single interval value: for example '10' SECONDS. Note it has two parts: the value (which must always be in single-quotes) and the qualifier (here, SECONDS), which give the units for the value. The qualifier takes the following form: ``` DAY  HOUR  MINUTE  SECOND [TO HOUR  MINUTE  SECOND] ``` **Note** YEAR TO MONTH intervals require a dash separating the values, whereas DAY TO HOUR intervals use a space to separate the values, as seen in the 2nd, 3rd, 5th, and 6th examples in that topic. In addition, the leading term has to be of greater significance than the optional trailing term, so this means you can only specify: ```  DAY  HOUR  MINUTE  SECOND  DAY TO HOUR  DAY TO MINUTE  DAY TO SECOND  HOUR TO MINUTE  HOUR TO SECOND  MINUTE TO SECOND ``` The easiest way to understand these may be to translate X TO Y as "Xs to the nearest Y". Hence, DAY TO HOUR is "days to the nearest hour". When DAY, HOUR, or MINUTE is the leading term, you can specify a precision, e.g., DAY(3) TO HOUR, indicating the number of digits the associated field in the value can have. The maximum precision is 10, and the default is 2. You can't specify precision for HOUR, OR MINUTE in the trailing term - they are always of precision 2. So for example, HOUR(3) TO MINUTE is legal, HOUR TO MINUTE(3) is not. SECOND can also take a precision, but the way it is specified differs depending on whether it is the leading or trailing field. + If SECOND is the leading field, you can specify the digits before and after the decimal point. For example, SECOND(3,3) would allow you to specify up to 999.999 seconds. The default is (2,3), which is actually a deviation from the SQL:2008 spec (it should be (2,6), but we only have millisecond precision). + If SECOND is the trailing field, you can only specify precision for the fractional seconds, that is, the part shown after the seconds' decimal point below. For example, SECOND(3) would indicate milliseconds. The default is 3 digits after the decimal point, but as above this is a deviation from the standard of 6. As for the value, it takes the general form of: ```  [+-]'[+-]DD HH:MM:SS.SSS' ``` Where DD are digits indicating days, HH hours, MM minutes, and SS.SSS is seconds (adjust the number of digits appropriately if precision is explicitly specified). Not all values have to include all fields—you can trim from both front or back, but not from in the middle. So you could make it 'DD HH' or 'MM:SS.SSS', but not 'DD MM'. However you write it, though, the value must match the qualifier, as shown following: ``` INTERVAL '25 3' DAY to HOUR ------> legal INTERVAL '3:45:04.0' DAY TO HOUR --> illegal ``` As stated in the SQL spec, if the precision is not explicitly specified, it is implied to be 2. Thus: + INTERVAL '120' MINUTE is an illegal interval. The legal form for the desired interval is INTERVAL '120' MINUTE(2) and + INTERVAL '120' SECOND is not legal. The legal form for the desired interval is INTERVAL '120' SECOND(3). ```   values INTERVAL '120' MINUTE(2);   Error: From line 1, column 8 to line 1, column 31:                       Interval field value 120 exceeds precision of MINUTE(2) field   values INTERVAL '120' MINUTE(3);   Conversion not supported ``` Also, if HOUR, MINUTE, or SECOND are not the leading field, they must fall in the following ranges (taken from Table 6 in topic 4.6.3 of the SQL:2008 foundation spec), as shown following: ```  HOUR: 0-23  MINUTE: 0-59  SECOND: 0-59.999 ``` Year-month intervals are similar, except that the qualifiers are as shown following: ```  YEAR  MONTH  YEAR TO MONTH ``` Precision can be specified just as with DAY and HOUR, and the max of 10 and default of 2 is the same. The value format for year-month is: 'YY-MM'. If MONTH is the trailing field, it must fall in the range 0-11. ``` := TO   := [ ] :=  SECOND [ ] := [ ]   SECOND [           [ ] ] :=       SECOND := YEAR  MONTH  DAY  HOUR      MINUTE := := ``` # Date and Time Patterns Date and time formats are specified by date and time pattern strings. In these pattern strings, unquoted letters from A to Z and from a to z represent components of a data or time value. If a letter or text string is enclosed within a pair of single quotes, that letter or text is not interpreted but rather used as is, as are all other characters in the pattern string. During printing, that letter or text is copied as is to the output string; during parsing, they are matched against the input string. "''" represents a single quote. The following pattern letters are defined for the indicated Date or Time Component. All other characters from 'A' to 'Z' and from 'a' to 'z' are reserved. For an alphabetic ordering of the pattern letters, see [Date and Time Pattern Letters in Alphabetic Order](#PATTERNSINALFAORDR). | Date or Time Component | Pattern Letter | Presentation as text or number | Examples | | --- | --- | --- | --- | | Era designator | G | [Text](#sql-reference-parse-timestamp-format-text) | AD | | Year | y | Year | 1996; 96 | | Month in year | M | Month | July; Jul; 07 | | Week in year | w | Number | 27 | | Week in month | W | Number | 2 | | Day in year | D | Number | 189 | | Day in month | d | Number | 10 | | Day of week in month | F | Number | 2 | | Day in week | E | [Text](#sql-reference-parse-timestamp-format-text) | EE=Tu; EEE=Tue; EEEE=Tuesday | | Am/pm marker | a | [Text](#sql-reference-parse-timestamp-format-text) | PM | | Hour in day (0-23) | H | Number | 0 | | Hour in day (1-24) | k | Number | 24 | | Hour in am/pm (0-11) | K | Number | 0 | | Hour in am/pm (1-12) | h | Number | 12 | | Minute in hour | m | Number | 30 | | Second in minute | s | Number | 55 | | Millisecond | S | Number | 978 | | Time zone | z | General | Pacific Standard Time; PST; GMT-08:00 | | Time zone | Z | RFC | -0800 | Pattern letters are usually repeated, as their number determines the exact presentation: ## Text For formatting, if the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available. For parsing, both forms are accepted, independent of the number of pattern letters. ## Number For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. For parsing, the number of pattern letters is ignored unless it's needed to separate two adjacent fields. ## Year Time zones are interpreted as text if they have names. For time zones representing a GMT offset value, the following syntax is used: ``` GMTOffsetTimeZone: GMT Sign Hours : Minutes Sign: one of + - Hours: Digit Digit Digit Minutes: Digit Digit Digit: one of 0 1 2 3 4 5 6 7 8 9 ``` Hours must be between 0 and 23, and Minutes must be between 00 and 59. The format is locale independent and digits must be taken from the Basic Latin block of the Unicode standard. For parsing, RFC 822 time zones are also accepted. ## RFC 822 time zone For formatting, the RFC 822 4-digit time zone format is used: ``` RFC822TimeZone: Sign TwoDigitHours Minutes TwoDigitHours: Digit Digit ``` TwoDigitHours must be between 00 and 23. Other definitions are as for general time zones. For parsing, general time zones are also accepted. SimpleDateFormat also supports ''localized date and time pattern'' strings. In these strings, the pattern letters described above may be replaced with other, locale dependent, pattern letters. SimpleDateFormat does not deal with the localization of text other than the pattern letters; that's up to the client of the class. ## Examples The following examples show how date and time patterns are interpreted in the U.S. locale. The given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific time zone. | Date and Time Pattern | Result | | --- | --- | | "yyyy.MM.dd G 'at' HH:mm:ss z" | 2001.07.04 AD at 12:08:56 PDT | | "EEE, MMM d, ''yy" | Wed, Jul 4, '01 | | "h:mm a" | 12:08 PM | | "hh 'o''clock' a, zzzz" | 12 o'clock PM, Pacific Daylight Time | | "K:mm a, z" | 0:08 PM, PDT | | "yyyyy.MMMMM.dd GGG hh:mm aaa" | 02001.July.04 AD 12:08 PM | | "EEE, d MMM yyyy HH:mm:ss Z" | Wed, 4 Jul 2001 12:08:56 -0700 | | "yyMMddHHmmssZ" | 010704120856-0700 | | "yyyy-MM-dd'T'HH:mm:ss.SSSZ" | 2001-07-04T12:08:56.235-0700 | ## Date and Time Pattern Letters in Alphabetic Order The same pattern letters shown at first, above, in Date or Time Component order are shown below in alphabetic order for easy reference. | Pattern Letter | Date or Time Component | Presentation as text or number | Examples | | --- | --- | --- | --- | | a | Am/pm marker | Text | PM | | D | Day in year | Number | 189 | | d | Day in month | Number | 10 | | E | Day in week | Text | EE=Tu; EEE=Tue; EEEE=Tuesday | | F | Day of week in month | Number | 2 | | G | Era designator | Text | AD | | H | Hour in day (0-23) | Number | 0 | | h | Hour in am/pm (1-12) | Number | 12 | | k | Hour in day (1-24) | Number | 24 | | K | Hour in am/pm (0-11) | Number | 0 | | M | Month in year | Month | July; Jul; 07 | | m | Minute in hour | Number | 30 | | s | Second in minute | Number | 55 | | S | Millisecond | Number | 978 | | w | Week in year | Number | 27 | | W | Week in month | Number | 2 | | y | Year | Year | 1996; 96 | | z | Time zone | General | Pacific Standard Time; PST; GMT-08:00 | | Z | Time zone | RFC | -0800 | # CURRENT\$1DATE Returns the current Amazon Kinesis Data Analytics system date when the query executes as YYYY-MM-DD when the query executes. For more information, see [CURRENT\$1TIME](sql-reference-current-time.md), [CURRENT\$1TIMESTAMP](sql-reference-current-timestamp.md), [LOCALTIMESTAMP](sql-reference-local-timestamp.md), [LOCALTIME](sql-reference-localtime.md), and [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md). ## Example ``` +---------------+ | CURRENT_DATE  | +---------------+ | 2008-08-27    | +---------------+ ``` # CURRENT\$1ROW\$1TIMESTAMP CURRENT\$1ROW\$1TIMESTAMP is an Amazon Kinesis Data Analytics extension to the SQL:2008 specification. This function returns the current timestamp as defined by the environment on which the Amazon Kinesis Data Analytics application is running. CURRENT\$1ROW\$1TIMESTAMP is always returned as UTC, not the local timezone. CURRENT\$1ROW\$1TIMESTAMP is similar to [LOCALTIMESTAMP](sql-reference-local-timestamp.md), but returns a new timestamp for each row in a stream. A query run with LOCALTIMESTAMP (or CURRENT\$1TIMESTAMP or CURRENT\$1TIME) as one of the columns puts into all output rows the time the query is first run. If that column instead contains CURRENT\$1ROW\$1TIMESTAMP, each output row gets a newly-calculated value of TIME representing when that row was output. **Note** CURRENT\$1ROW\$1TIMESTAMP is not defined in the SQL:2008 specification; it is an Amazon Kinesis Data Analytics extension. For more information, see [CURRENT\$1TIME](sql-reference-current-time.md), [CURRENT\$1DATE](sql-reference-current-date.md), [CURRENT\$1TIMESTAMP](sql-reference-current-timestamp.md), [LOCALTIMESTAMP](sql-reference-local-timestamp.md), [LOCALTIME](sql-reference-localtime.md), and [CURRENT\$1ROW\$1TIMESTAMP](#sql-reference-current-row-timestamp). # CURRENT\$1TIME Returns the current Amazon Kinesis Data Analytics system time when the query executes. Time is in UTC, not the local time zone. For more information, see [CURRENT\$1TIMESTAMP](sql-reference-current-timestamp.md), [LOCALTIMESTAMP](sql-reference-local-timestamp.md), [LOCALTIME](sql-reference-localtime.md), [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md), and [CURRENT\$1DATE](sql-reference-current-date.md). ## Example ``` +---------------+ | CURRENT_TIME  | +---------------+ | 20:52:05      | ``` # CURRENT\$1TIMESTAMP Returns the current database system timestamp (as defined on the environment on which Amazon Kinesis Data Analytics is running) as a datetime value. For more information, see [CURRENT\$1TIME](sql-reference-current-time.md), [CURRENT\$1DATE](sql-reference-current-date.md), [LOCALTIME](sql-reference-localtime.md), [LOCALTIMESTAMP](sql-reference-local-timestamp.md),  and [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md). ## Example ``` +--------------------+ | CURRENT_TIMESTAMP  | +--------------------+ | 20:52:05           | +--------------------+ ``` # EXTRACT ``` EXTRACT(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND FROM |) ``` The EXTRACT function extracts one field from a DATE, TIME, TIMESTAMP or INTERVAL expression. Returns BIGINT for all fields other than SECOND. For SECOND it returns DECIMAL(5,3) and includes milliseconds. ## Syntax ### Examples | Function | Result | | --- | --- | | 
EXTRACT(DAY FROM INTERVAL '2 3:4:5.678' DAY TO SECOND)
| 2 | | 
EXTRACT(HOUR FROM INTERVAL '2 3:4:5.678' DAY TO SECOND)
| 3 | | 
EXTRACT(MINUTE FROM INTERVAL '2 3:4:5.678' DAY TO SECOND)
| 4 | | 
EXTRACT(SECOND FROM INTERVAL '2 3:4:5.678' DAY TO SECOND)
| 5.678 | | 
EXTRACT(MINUTE FROM CURRENT_ROW_TIMESTAMP)
where CURRENT_ROW_TIMESTAMP is 2016-09-23 04:29:26.234
| 29 | | 
EXTRACT (HOUR FROM CURRENT_ROW_TIMESTAMP)
where CURRENT\$1ROW\$1TIMESTAMP is 2016-09-23 04:29:26.234 | 4 | ### Use in Function EXTRACT can be used for conditioning data, as in the following function which returns a 30 minute floor when [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md) is input for p\$1time. ``` CREATE or replace FUNCTION FLOOR30MIN( p_time TIMESTAMP ) RETURNS  TIMESTAMP CONTAINS SQL RETURNS NULL ON NULL INPUT RETURN  floor(p_time to HOUR) + (( EXTRACT (  MINUTE FROM p_time  ) / 30)* INTERVAL '30' MINUTE ) ; ``` You would implement this function using code along the following lines: ``` SELECT stream FLOOR30MIN( CURRENT_ROW_TIMESTAMP ) as ROWTIME , * from "MyStream" ) over (range current row ) as r ``` **Note** The code above assumes that you have previously created a stream called "MyStream." # LOCALTIME Returns the current time when the query executes as defined by the environment on which Amazon Kinesis Data Analytics is running. LOCALTIME is always returned as UTC (GMT), not the local timezone. For more information, see [CURRENT\$1TIME](sql-reference-current-time.md), [CURRENT\$1DATE](sql-reference-current-date.md), [CURRENT\$1TIMESTAMP](sql-reference-current-timestamp.md), [LOCALTIMESTAMP](sql-reference-local-timestamp.md), and [CURRENT\$1ROW\$1TIMESTAMP](sql-reference-current-row-timestamp.md). ## Example ``` VALUES localtime; +------------+ | LOCALTIME  | +------------+ | 01:11:15   | +------------+ 1 row selected (1.558 seconds) ``` ## Limitations Amazon Kinesis Data Analytics does not support the optional