[jira] [Commented] (TRAFODION-3118) Improve on parts of EXTRACT constructs.

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-3118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530596#comment-16530596 ] 

ASF GitHub Bot commented on TRAFODION-3118:
-------------------------------------------

Github user DaveBirdsall commented on a diff in the pull request:

    https://github.com/apache/trafodion/pull/1622#discussion_r199649960
  
    --- Diff: core/sql/regress/seabase/TEST030 ---
    @@ -98,6 +98,11 @@ select HOUR(interval '5:2:15:36.33' day to second(2)) from (values(1)) as t(a);
     select MINUTE(interval '5:13:25:2.12' day to second(2)) from (values(1)) as t(a);
     select extract (year from INTERVAL '97-02' YEAR TO MONTH) from (values (1)) as t(a);
     select interval '8' year / 4 from dual;
    +select extract(week from date '2005-01-01') from (values(1)) as t(a);
    +select extract(epoch from timestamp '2000-12-30 20:38:40.12') from (values(1)) as t(a);
    +select extract(dow from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a);
    +select extract(doy from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a);
    +select extract(wom from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a);
    --- End diff --
    
    Consider adding tests for the other options, e.g. CENTURY, DECADE


> Improve on parts of EXTRACT constructs.
> ---------------------------------------
>
>                 Key: TRAFODION-3118
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-3118
>             Project: Apache Trafodion
>          Issue Type: Improvement
>            Reporter: chenyunren
>            Assignee: chenyunren
>            Priority: Major
>
> add  DAYOFYEAR (with alias DOY), DAYOFWEEK (with alias DOW), WEEK（weekofyear）, WEEKOFMONTH, QUARTER,  EPOCH,CENTURY, DECADE to function EXTRACT 
>  
> |century|The century
>  support: datetime|EXTRACT(CENTURY FROM TIMESTAMP '2000-11-20 12:30:00'); 
>  Result: 20
>  EXTRACT(CENTURY FROM TIMESTAMP '2001-01-01 12:30:00');
>  Result: 21|
> |decade|The year divided by 10
>  support: datetime interval|EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
>  Result: 200
>  EXTRACT(DECADE FROM INTERVAL '9' YEAR - INTERVAL '99' YEAR);
>  Result: -9|
> |week
>  (weekofyear)|week number of year (1-53) (The first week starts on the first day of the year.)
>  support: datetime|EXTRACT(WEEK FROM DATE '2005-01-01'); 
>  Result:1|
> |epoch|the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval
>  support: datetime interval|EXTRACT(EPOCH FROM TIMESTAMP '2000-12-30 20:38:40.12');
>  Result: 978208720.12
>  EXTRACT(EPOCH FROM INTERVAL '2' YEAR - INTERVAL '3' MONTH)
>  Result: 54885600|
> |quarter|The quarter of the year (1 - 4) 
>  support: datetime interval|EXTRACT(QUARTER FROM DATE '2007-02-16'); 
>  Result: 1
>  EXTRACT(QUARTER FROM INTERVAL '09' MONTH);
>  Result: 3|
> |dow
>  (dayofweek)|The day of the week as Sunday (1) to Saturday (7)
>  support: datetime|EXTRACT(DOW FROM TIMESTAMP '2018-06-21 20:38:40'); 
>  Result: 5|
> |doy
>  (dayofyear)|The day of the year (1 - 365/366)
>  support: datetime|EXTRACT(DOY FROM TIMESTAMP '2018-06-21 20:38:40');
>   Result: 172|
> |wom
>  (weekofmonth)|Returns a number for the count of week in the current month(1-5). (The first week starts on the first day of the month.)
>  support: datetime|EXTRACT(WOM FROM TIMESTAMP '2018-06-21 20:38:40'); 
>  Result: 3|



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)