trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From li...@apache.org
Subject [1/3] trafodion git commit: [TRAFODION-3169] Add *Syntax Descriptions and Examples* for *EXTRACT Function* in *Trafodion SQL Reference Manual*
Date Mon, 30 Jul 2018 03:57:57 GMT
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 <qwertyioz@hotmail.com>
Authored: Mon Jul 30 10:54:20 2018 +0800
Committer: liu.yu <qwertyioz@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]]


Mime
View raw message