[
https://issues.apache.org/jira/browse/TRAFODION-3118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16533304#comment-16533304
]
ASF GitHub Bot commented on TRAFODION-3118:
-------------------------------------------
Github user kakaxi3019 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1622#discussion_r200242748
--- Diff: core/sql/optimizer/SynthType.cpp ---
@@ -4470,15 +4489,31 @@ const NAType *Extract::synthesizeType()
else if (getExtractField() == REC_DATE_YEARWEEK_EXTRACT ||
getExtractField() == REC_DATE_YEARWEEK_D_EXTRACT)
prec = 6; // YEARMWEEK is yyyyww
+ else if (getExtractField() == REC_DATE_DECADE ||
+ getExtractField() == REC_DATE_DOY)
+ prec = 3;
+ else if (getExtractField() == REC_DATE_QUARTER ||
+ getExtractField() == REC_DATE_DOW)
+ prec = 1;
+ else if (getExtractField() == REC_DATE_EPOCH)
+ prec = 10;
else
prec = 2; // else max of 12, 31,
24, 59
if (getExtractField() == REC_DATE_SECOND) {
prec += dti.getFractionPrecision();
scale += dti.getFractionPrecision();
}
+ if (getExtractField() == REC_DATE_EPOCH)
+ {
+ prec += dti.getFractionPrecision();
+ scale += dti.getFractionPrecision();
+ }
+ NABoolean bNegValue = FALSE;
+ if ( getExtractField() >= REC_DATE_CENTURY && extractStartField <=
REC_DATE_WOM )
+ bNegValue = TRUE;
--- End diff --
Thanks Dave, there are some problems here.
options DECADE,QUARTER,EPOCH can be negative values
> 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)
