Repository: trafodion Updated Branches: refs/heads/master fe87aa15e -> 7677846ac
[TRAFODION-3169] 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/776f38cf Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/776f38cf Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/776f38cf Branch: refs/heads/master Commit: 776f38cf3ffed1f78446d093a0f31fdf3d79d535 Parents: fe87aa1 Author: liu.yu <qwerty...@hotmail.com> Authored: Mon Jul 30 10:54:20 2018 +0800 Committer: liu.yu <qwerty...@hotmail.com> Committed: Mon Jul 30 10:54:20 2018 +0800 ---------------------------------------------------------------------- .../sql_functions_and_expressions.adoc | 650 ++++++++++++++++++- 1 file changed, 641 insertions(+), 9 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/776f38cf/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 adba842..3e7c987 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,665 @@ 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_` + +Number of quarter(s) in the `_interval-expression_`. +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +* `_interval-expression_` + +| SECOND +a| * `_datetime-expression_` + +Second (0-59). +* `_interval-expression_` + +Number of second(s) in the `_interval-expression_`. + +a| * `_datetime-expression_` + +** TIME +** TIMESTAMP +* `_interval-expression_` + + +| WEEK +a| * `_datetime-expression_` + +a| Week of year. + + +* The value 1 is returned for a datetime that occurs in the first 7 days of the year if the year begins on Sunday. + +Otherwise, the value 1 is returned for a datetime that occurs in the partial week before the start of the first Sunday. + +* The value *53* is returned for a datetime that occurs in the last full or partial week of the year. + +* The value *54* is returned for a datetime that occurs in the last full or partial week of the leap year if the leap year begins on Saturday. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP + +| WOM +a| * `_datetime-expression_` + +Week of 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 EXTRACT (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 EXTRACT (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 EXTRACT (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 EXTRACT (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 EXTRACT (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 EXTRACT (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 EXTRACT (HOUR) + +* This example extracts the hour from `_TIME_`. + ``` -EXTRACT (YEAR FROM DATE '2007-09-28') +SQL>SELECT EXTRACT (HOUR FROM TIME '00:01:02') FROM DUAL; + +(EXPR) +------ + 0 + +--- 1 row(s) selected. ``` + +* This example extracts the hour from `_TIMESTAMP_`. + -The result is 2007. +``` +SQL>SELECT EXTRACT (HOUR FROM TIMESTAMP '1990-11-02 23:16:26') FROM DUAL; -* Extract the year from an INTERVAL value: +(EXPR) +------ + 23 + +--- 1 row(s) selected. +``` + +* This example extracts the number of hours from `_interval-expression_`. + ``` -EXTRACT (YEAR FROM INTERVAL '01-09' YEAR TO MONTH) +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 EXTRACT (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 EXTRACT (MONTH) + +* This example extracts the month from `_DATE_`. + -The result is 1. +``` +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 EXTRACT (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 number of quarters from `_interval-expression_`. ++ +``` +SQL>SELECT EXTRACT (QUARTER FROM INTERVAL '97' MONTH) FROM DUAL; + +(EXPR) +------ + 33 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_second]] +==== Examples of EXTRACT (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_`. ++ +``` +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 EXTRACT (WEEK) + +* This example extracts the week from `_DATE_`. ++ +``` +SQL>SELECT EXTRACT (WEEK FROM DATE '2019-01-01') FROM DUAL; + +(EXPR) +------ + 1 + +--- 1 row(s) selected. +``` + +* This example extracts the week from `_TIMESTAMP_`. ++ +``` +SQL>SELECT EXTRACT (WEEK FROM TIMESTAMP '2000-12-31 23:59:59') FROM DUAL; + +(EXPR) +------ + 54 + +--- 1 row(s) selected. +``` + +[[examples_of_extract_wom]] +==== Examples of EXTRACT (WOM) + +* This example extracts the WOM from `_DATE_`. ++ +``` +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 EXTRACT (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]]
