Repository: trafodion Updated Branches: refs/heads/master 95eaa31dd -> 3cebebf8e
[TRAFODION-3156] Add *Syntax Descriptions and Examples* for *EXTRACT Function* in *Trafodion SQL Reference Manual* Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/c3081633 Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/c3081633 Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/c3081633 Branch: refs/heads/master Commit: c30816330060abb11b0b6b92ee63b1fba0d30fca Parents: ecc1a56 Author: liu.yu <[email protected]> Authored: Thu Jul 19 20:46:11 2018 +0800 Committer: liu.yu <[email protected]> Committed: Thu Jul 19 20:46:11 2018 +0800 ---------------------------------------------------------------------- .../sql_functions_and_expressions.adoc | 634 ++++++++++++++++++- 1 file changed, 625 insertions(+), 9 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/c3081633/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---------------------------------------------------------------------- diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc index 625952f..dab5601 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc @@ -4439,33 +4439,649 @@ EXTRACT (datetime-field FROM extract-source) * `_datetime-field_` is: + -YEAR \| MONTH \| DAY \| HOUR \| MINUTE \| SECOND +.Details of `_datetime-field_` +[cols="20%,40%,40%"] +|=== +^| `_datetime-filed_` +^| *Description* +^| *Supported* `_extract-source_` +a| CENTURY +| Century. +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| DAY +a| * `_datetime-expression_` + +Day. +* `_interval-expression_` + +Number of day(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| DECADE +a| * `_datetime-expression_` + +Year field divided by 10. +* `_interval-expression_` + +Number of decade(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +* `_interval-expression_` +| DOW +| Day of week (1-7), where 1 is Sunday, 6 is Saturday. + +This is not configurable. +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| DOY +| Day of year (1-366). +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| EPOCH +a| * `_datetime-expression_` + +Second(s) since 1970-01-01 00:00:00. + +The value is negative if `_datetime-expression_` precedes 1970-01-01 00:00:00. +* `_interval-expression_` + +Number of second(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +* `_interval-expression_` +| HOUR +a| * `_datetime-expression_` + +Hour (0-23). +* `_interval-expression_` + +Number of hour(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` + +** TIME +** TIMESTAMP +* `_interval-expression_` +| MINUTE +a| * `_datetime-expression_` + +Minute (0-59). +* `_interval-expression_` + +Number of minute(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` +** TIME +** TIMESTAMP +* `_interval-expression_` +| MONTH +a| * `_datetime-expression_` + +Month (1-12). +* `_interval-expression_` + +Number of month(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +* `_interval-expression_` +| QUARTER +a| * `_datetime-expression_` + +Quarter of year (1-4). +* `_interval-expression_` + +Quarter of year (1-4). +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +* `_interval-expression_` +| SECOND +a| * `_datetime-expression_` + +Second(s) (0-59). +* `_interval-expression_` + +Number of second(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` + +** TIME +** TIMESTAMP +* `_interval-expression_` + +| WEEK +a| * `_datetime-expression_` + +Week of year (1-54). +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| WOM +a| * `_datetime-expression_` + +Week of month (1-5). + +** The week 1 starts on the 1^st^ day and ends on the 7^th^ day of a month. +** The week 2 starts on the 8^th^ day and ends on the 14^th^ day of a month. +** The week 3 starts on the 15^th^ day and ends on the 21^th^ day of a month. +** The week 4 starts on the 22^th^ day and ends on the 28^th^ day of a month. +** The week 5 starts on the 29^th^ day and ends on the 31^th^ day of a month. +a| * `_datetime-expression_` + +** DATE +** TIMESTAMP +| YEAR +a| * `_datetime-expression_` + +Year (0001-9999). +* `_interval-expression_` + +Number of year(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +* `_interval-expression_` +|=== * `_extract-source_` is: + -datetime-expression \| interval-expression + datetime-expression +| interval-expression -See <<datetime_value_expressions,Datetime Value Expressions>> and ++ +For more information, see <<datetime_value_expressions,Datetime Value Expressions>> and <<interval_value_expressions,Interval Value Expressions>>. [[examples_of_extract]] === Examples of EXTRACT -* Extract the year from a DATE value: +[[examples_of_extract_century]] +==== Examples of CENTURY + +* This example extracts the century from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (CENTURY FROM DATE '0001-02-03') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the century from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (CENTURY FROM TIMESTAMP '9899-12-31 23:59:59') FROM DUAL; + +(EXPR) +------ + 99 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_day]] +==== Examples of DAY + +* This example extracts the day from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (DAY FROM DATE '2019-02-01') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the day from `_TIMESTAMP_`. ++ +``` +SELECT EXTRACT (DAY FROM TIMESTAMP '2019-12-31 11:43:00') FROM DUAL; + +(EXPR) +------ + 31 + +--- 1 row(s) selected. +``` + +* This example extracts the number of days from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (DAY FROM INTERVAL '1' DAY - INTERVAL '99' DAY) FROM DUAL; + +(EXPR) +------ + -98 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_decade]] +==== Examples of DECADE + +* This example extracts the decade from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (DECADE FROM DATE '0010-02-03') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the decade from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (DECADE FROM TIMESTAMP '9999-12-31 23:59:59') FROM DUAL; + +(EXPR) +------ + 999 + +--- 1 row(s) selected. +``` + +* This example extracts the number of decades from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (DECADE FROM INTERVAL '1'YEAR - INTERVAL '99'YEAR) FROM DUAL; + +(EXPR) +------ + -9 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_dow]] +==== Examples of DOW + +* This example extracts the DOW from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (DOW FROM DATE '2017-12-31') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the DOW from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (DOW FROM TIMESTAMP '2018-01-01 23:59:59') FROM DUAL; + +(EXPR) +------ + 2 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_doy]] +==== Examples of DOY + +* This example extracts the DOY from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (DOY FROM DATE '2019-01-01') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the DOY from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (DOY FROM TIMESTAMP '2020-12-31 11:36:28') FROM DUAL; + +(EXPR) +------ + 366 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_epoch]] +==== Examples of EPOCH + +* This example extracts the seconds from `_DATE_` since 1970-01-01 00:00:00. ++ +``` +SQL>SELECT EXTRACT (EPOCH FROM DATE '1970-01-02') FROM DUAL; + +(EXPR) +------------ + 86400 + +--- 1 row(s) selected. +``` + +* This example extracts the seconds from `_TIMESTAMP_` since 1970-01-01 00:00:00. ++ +``` +SQL>SELECT EXTRACT (EPOCH FROM TIMESTAMP '1969-12-31 23:59:59') FROM DUAL; + +(EXPR) +------------ + -1 + +--- 1 row(s) selected. +``` + +* This is the first example to extract the seconds from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (EPOCH FROM INTERVAL '3' HOUR) FROM DUAL; + +(EXPR) +------------ + 10800 + +--- 1 row(s) selected. +``` + +* This is the second example to extract the seconds from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (EPOCH FROM INTERVAL '1' MONTH - INTERVAL '2' YEAR) FROM DUAL; + +(EXPR) +------------ + -60069600 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_hour]] +==== Examples of HOUR + +* This example extracts the hour from `_TIME_`. ++ +``` +SQL>SELECT EXTRACT (HOUR FROM TIME '00:01:02') FROM DUAL; + +(EXPR) +------ + 0 + +--- 1 row(s) selected. +``` + +* This example extracts the hour from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (HOUR FROM TIMESTAMP '1990-11-02 23:16:26') FROM DUAL; + +(EXPR) +------ + 23 + +--- 1 row(s) selected. +``` + +* This example extracts the number of hours from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (HOUR FROM INTERVAL '1' HOUR - INTERVAL '99' HOUR) FROM DUAL; + +(EXPR) +------ + -98 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_minute]] +==== Examples of MINUTE + +* This example extracts the minute from `_TIME_`. ++ +``` +SQL>SELECT EXTRACT (MINUTE FROM TIME '01:00:02') FROM DUAL; + +(EXPR) +------ + 0 + +--- 1 row(s) selected. +``` + +* This example extracts the minute from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (MINUTE FROM TIMESTAMP '1990-11-02 23:59:16') FROM DUAL; + +(EXPR) +------ + 59 + +--- 1 row(s) selected. +``` + +* This example extracts the number of minutes from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT(MINUTE FROM INTERVAL '1' MINUTE - INTERVAL '99' MINUTE) FROM DUAL; + +(EXPR) +------ + -98 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_month]] +==== Examples of MONTH + +* This example extracts the month from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (MONTH FROM DATE '2019-01-02') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the month from `_TIME_`. ++ +``` +SQL>SELECT EXTRACT (MONTH FROM TIMESTAMP '2019-12-31 23:59:59') FROM DUAL; + +(EXPR) +------ + 12 + +--- 1 row(s) selected. +``` + +* This example extracts the number of months from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT(MONTH FROM INTERVAL '1' MONTH - INTERVAL '99' MONTH) FROM DUAL; + +(EXPR) +------ + -98 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_quarter]] +==== Examples of QUARTER + +* This example extracts the quarter from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (QUARTER FROM DATE '2019-01-01') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the quarter from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (QUARTER FROM TIMESTAMP '1990-11-02 08:16:26') FROM DUAL; + +(EXPR) +------ + 4 + +--- 1 row(s) selected. +``` + +* This example extracts the quarter from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT(QUARTER FROM INTERVAL '8' MONTH) FROM DUAL; + +(EXPR) +------ + 3 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_second]] +==== Examples of SECOND + +* This example extracts the second from `_TIME_`. ++ +``` +SQL>SELECT EXTRACT (SECOND FROM TIME '01:02:00') FROM DUAL; + +(EXPR) +------ + 0 + +--- 1 row(s) selected. +``` + +* This example extracts the second from `_TIMESTAMP_`. + ``` -EXTRACT (YEAR FROM DATE '2007-09-28') +SQL>SELECT EXTRACT (SECOND FROM TIMESTAMP '1990-11-02 23:16:59') FROM DUAL; + +(EXPR) +------ + 59 + +--- 1 row(s) selected. +``` + +* This example extracts the number of seconds from `_interval-expression_`. ++ ``` +SQL>SELECT EXTRACT(SECOND FROM INTERVAL '1' SECOND - INTERVAL '99' SECOND) FROM DUAL; + +(EXPR) +----------- + -98.000000 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_week]] +==== Examples of WEEK + +* This example extracts the week from `_DATE_`. + -The result is 2007. +``` +SQL>SELECT EXTRACT (WEEK FROM DATE '2019-01-01') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` -* Extract the year from an INTERVAL value: +* This example extracts the week from `_TIMESTAMP_`. + ``` -EXTRACT (YEAR FROM INTERVAL '01-09' YEAR TO MONTH) +SQL>SELECT EXTRACT (WEEK FROM TIMESTAMP '2020-12-31 23:59:59') FROM DUAL; + +(EXPR) +------ + 54 + +--- 1 row(s) selected. ``` + +[[examples_of_extract_wom]] +==== Examples of WOM + +* This example extracts the WOM from `_DATE_`. + -The result is 1. +``` +SQL>SELECT EXTRACT (WOM FROM DATE '2019-01-01') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the WOM from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (WOM FROM TIMESTAMP '2020-02-29 01:02:30') FROM DUAL; + +(EXPR) +------ + 5 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_year]] +==== Examples of YEAR + +* This example extracts the year from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (YEAR FROM DATE '0001-02-03') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the year from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (YEAR FROM TIMESTAMP '9999-12-31 23:59:59') FROM DUAL; + +(EXPR) +------ + 9999 + +--- 1 row(s) selected. +``` + +* This example extracts the year from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (YEAR FROM INTERVAL '01-09' YEAR TO MONTH) FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the number of years from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT(YEAR FROM INTERVAL '1' YEAR - INTERVAL '99' YEAR) FROM DUAL; + +(EXPR) +------ + -98 + +--- 1 row(s) selected. +``` <<< [[filetolob_function]]
