trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From liuyu000 <...@git.apache.org>
Subject [GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...
Date Thu, 23 Nov 2017 03:26:33 GMT
Github user liuyu000 commented on a diff in the pull request:

    https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152719226
  
    --- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
---
    @@ -2325,82 +2328,206 @@ following abbreviations:
     
     * `startdate`
     +
    -may be of type DATE or TIMESTAMP.
    +may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
     See <<datetime_value_expressions,Datetime Value Expressions>>.
     
     * `enddate`
     +
    -may be of type DATE or TIMESTAMP.
    +may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
     See <<datetime_value_expressions,Datetime Value Expressions>>.
     
    -The method of counting crossed boundaries such as days, minutes, and
    -seconds makes the result given by DATEDIFF consistent across all data
    -types. The result is a signed integer value equal to the number of
    -datepart boundaries crossed between the first and second date.
    -
    -For example, the number of weeks between Sunday, January 4, and Sunday,
    -January 1 , is 1. The number of months between March 31 and April 1
    -would be 1 because the month boundary is crossed from March to April.
    -The DATEDIFF function generates an error if the result is out of range
    -for integer values. For seconds, the maximum number is equivalent to
    -approximately 68 years. The DATEDIFF function generates an error if a
    -difference in weeks is requested and one of the two dates precedes
    -January 7 of the year 0001.
    +[[considerations_for_datediff]]
    +=== Considerations for DATEDIFF 
    +
    +[[boundary]]
    +==== Boundary
    +
    +The method of counting crossed boundaries such as days, minutes, and seconds makes the
result given by DATEDIFF consistent across all data types. 
    +
    +The result is a signed integer value equal to the number of _datepart_ boundaries crossed
between _startdate_ and _enddate_, because the DATEDIFF function does not calculate the full
_datepart_, it counts the difference between _startdate_  and _enddate_.
    +
    +For example:  
    +
    +* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is only a single
second. 
    +
    ++
    +However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 month, 1 quarter
or 1 month depending on the specified _datepart_.
    +
    ++
    +^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be counted. For more
information, see <<the_first_day_of_week,The first day of week>>.
    +
    +* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two years.
    +
    ++
    +However, the DATEDIFF difference is 1 year if the specified _datepart_ is YEAR.
    +
    +[[the_first_day_of_week]]
    +==== The first day of week
    +
    +* This value cannot be specified, the default value is Sunday.
    +
    +* The DATEDIFF function returns the number of Sundays between _startdate_ and _enddate_.
This function counts _enddate_ if it falls on Sunday, but doesn't count _startdate_ even if
it does fall on a Sunday.
    +
    ++
    +For example,
    +
    +** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the specified _datepart_
is WEEK.
    +
    +** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the specified _datepart_
is WEEK.
    +
    +[[the_first_week_of_year]]
    +==== The first week of year
    +
    +This value cannot be specified, the default value is the week in which Jan 1 occurs.
 
    +
    +[[negative_or_zero]]
    +==== Negative or zero
    +
    +If _enddate_ precedes _startdate_, the return value is negative or zero.
    +
    +[[error]]
    +==== Error
    +
    +* The DATEDIFF function generates an error if the result is out of range for integer
values. 
    +
    +** For seconds, the maximum number is equivalent to approximately 68 years. 
    +
    +** For weeks, if a difference in weeks is requested, one of the two dates cannot precede
0001-01-07.
     
     <<<
     [[examples_of_datediff]]
     === Examples of DATEDIFF
     
    -* This function returns the value of 0 because no one-second boundaries
    -are crossed.
    +[[date_difference_in_second]]
    +==== Date Difference in SECOND
    +
    +* This function returns the value of 0 because no one-second boundary
    +is crossed.
     +
     ```
    -DATEDIFF( SECOND
    -        , TIMESTAMP '2006-09-12 11:59:58.999998'
    -        , TIMESTAMP '2006-09-12 11:59:58.999999'
    -        )
    +SELECT DATEDIFF( SECOND
    +                 , TIMESTAMP '2006-09-12 11:59:58.999998'
    +                 , TIMESTAMP '2006-09-12 11:59:58.999999'
    +               )
    +FROM DUAL;
     ```
     
     * This function returns the value 1 because a one-second boundary is
     crossed even though the two timestamps differ by only one microsecond.
     +
     ```
    -DATEDIFF( SECOND
    -        , TIMESTAMP '2006-09-12 11:59:58.999999'
    -        , TIMESTAMP '2006-09-12 11:59:59.000000'
    -        )
    +SELECT DATEDIFF( SECOND
    +                 , TIMESTAMP '2006-09-12 11:59:58.999999'
    +                 , TIMESTAMP '2006-09-12 11:59:59.000000'
    +               )
    +FROM DUAL;
     ```
     
    -* This function returns the value of 0.
    +[[date_difference_in_minute]]
    +==== Date Difference in MINUTE
    +
    +* This function returns the value of 2 because a two-minute boundary is crossed.
     +
     ```
    -DATEDIFF( YEAR
    -        , TIMESTAMP '2006-12-31 23:59:59.999998'
    -        , TIMESTAMP '2006-12-31 23:59:59.999999'
    -        )
    +SELECT DATEDIFF( MINUTE
    +                 , TIMESTAMP '2011-12-30 08:54:55'
    +                 , TIMESTAMP '2011-12-30 08:56:01'
    +               ) 
    +FROM DUAL;
    +```
    +
    +[[date_difference_in_day]]
    +==== Date Difference in DAY
    +
    +* This function returns the value of -29.
    ++
    +```
    +SELECT DATEDIFF( DAY
    +                 , DATE '2008-03-01'
    +                 , DATE '2008-02-01'
    +               ) 
    +FROM DUAL;
    +```
    +
    +* This statement calculates how long do buyers have to wait. 
    ++
    +```
    +SELECT id, DATEDIFF( DAY, order_date, delivery_date ), price 
    +FROM orders 
    +ORDER BY price DESC;
     ```
     
    -* This function returns the value of 1 because a year boundary is
    -crossed.
     +
     ```
    -DATEDIFF( YEAR
    -        , TIMESTAMP '2006-12-31 23:59:59.999999'
    -        , TIMESTAMP '2007-01-01 00:00:00.000000'
    -        )
    +ID           (EXPR)       PRICE 
    +-----------  -----------  ------
    +
    +     700300          145     926
    +     312203          147     621
    +     800660           23     568
    +     100350          160     543
    +     500450          148     324
    +     700510          141     229
    +     100210            3     228
    +     600480          151     197
    +     300380          154     183
    +     200490          227     123
    +     200320          153      91
    +     400410          158      65
    +     600250          143      32
    +
    +--- 13 row(s) selected.
     ```
     
    -* This function returns the value of 2 because two WEEK boundaries are
    +[[date_difference_in_week]]
    +==== Date Difference in WEEK
    +
    +* This function returns the value of 1 because only a one-week boundary is
     crossed.
     +
     ```
    -DATEDIFF(WEEK, DATE '2006-01-01', DATE '2006-01-09')
    +SELECT DATEDIFF( WEEK
    +                 , DATE '2006-01-01'
    +                 , DATE '2006-01-09'
    +               )
    +FROM DUAL;
     ```
     
    -* This function returns the value of -29.
    +[[date_difference_in_quarter]]
    +==== Date Difference in QUARTER
    +
    +* This function returns the value of 3 because a three-quarter boundary is crossed.
    --- End diff --
    
    Thanks Dave, your comments have been incorporated. :)


---

Mime
View raw message