# 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