[
https://issues.apache.org/jira/browse/CALCITE-1662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15886254#comment-15886254
]
Remus Rusanu edited comment on CALCITE-1662 at 2/27/17 6:24 PM:
----------------------------------------------------------------
My initial tests for CALCITE-1659 contained something along this line:
{noformat}
select TIMESTAMPDIFF(MICROSECOND,
TIMESTAMP '2016-02-26 19:00:00.789',
TIMESTAMP '2016-02-26 19:00:00.123');
{noformat}
but this returns 0 (even after the CALCITE-1659 changes). At first I suspected
this lines in {{TimestampDiffConvertlet.convertCall}}:
{noformat}
switch (unit) {
case MICROSECOND:
case MILLISECOND:
case WEEK:
multiplier = BigDecimal.valueOf(DateTimeUtils.MILLIS_PER_SECOND);
divider = unit.multiplier;
unit = TimeUnit.SECOND;
break;
{noformat}
and this is why I created this. I tried to modify it as this:
{noformat}
case MICROSECOND:
case MILLISECOND:
multiplier = TimeUnit.MICROSECOND.multiplier;
divider = unit.multiplier;
unit = TimeUnit.MICROSECOND;
break;
{noformat}
to no avail, it still yields 0. Looking now at the code, it may be because the
implementation relies on {{MINUS_DATE}}. I tested mysql and TIMESTAMP {{-}}
operator yields 0, but TIMESTAMPDIFF returns the correct value:
{noformat}
mysql> select TIMESTAMPDIFF(MICROSECOND,
-> TIMESTAMP '2016-02-26 19:00:00.789',
-> TIMESTAMP '2016-02-26 19:00:00.123');
+----------------------------------------------------------------------------------------------------------+
| TIMESTAMPDIFF(MICROSECOND,
TIMESTAMP '2016-02-26 19:00:00.789',
TIMESTAMP '2016-02-26 19:00:00.123') |
+----------------------------------------------------------------------------------------------------------+
|
-666000 |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMP '2016-02-26 19:00:00.789' - TIMESTAMP '2016-02-26
19:00:00.123';
+---------------------------------------------------------------------------+
| TIMESTAMP '2016-02-26 19:00:00.789' - TIMESTAMP '2016-02-26 19:00:00.123' |
+---------------------------------------------------------------------------+
| 0 |
+---------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
{noformat}
was (Author: rusanu):
My initial tests for CALCITE-1659 contained something along this line:
{noformat}
select TIMESTAMPDIFF(MICROSECOND,
TIMESTAMP '2016-02-26 19:00:00.789',
TIMESTAMP '2016-02-26 19:00:00.123');
{noformat}
but this returns 0 (even after the CALCITE-1659 changes). At first I suspected
this lines in {{TimestampDiffConvertlet.convertCall}}:
{noformat}
switch (unit) {
case MICROSECOND:
case MILLISECOND:
case WEEK:
multiplier = BigDecimal.valueOf(DateTimeUtils.MILLIS_PER_SECOND);
divider = unit.multiplier;
unit = TimeUnit.SECOND;
break;
{noformat}
and this is why I created this. I tried to modify it as this:
{noformat}
case MICROSECOND:
case MILLISECOND:
multiplier = TimeUnit.MICROSECOND.multiplier;
divider = unit.multiplier;
unit = TimeUnit.MICROSECOND;
break;
{noformat}
to no avail, it still yields 0. Looking now at the code, it may be because the
implementation relies on {{MINUS_DATE}}. I tested mysql and TIMESTMAP {{-}}
operator yields 0, but TIMESTAMPDIFF returns the correct value:
{noformat}
mysql> select TIMESTAMPDIFF(MICROSECOND,
-> TIMESTAMP '2016-02-26 19:00:00.789',
-> TIMESTAMP '2016-02-26 19:00:00.123');
+----------------------------------------------------------------------------------------------------------+
| TIMESTAMPDIFF(MICROSECOND,
TIMESTAMP '2016-02-26 19:00:00.789',
TIMESTAMP '2016-02-26 19:00:00.123') |
+----------------------------------------------------------------------------------------------------------+
|
-666000 |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMP '2016-02-26 19:00:00.789' - TIMESTAMP '2016-02-26
19:00:00.123';
+---------------------------------------------------------------------------+
| TIMESTAMP '2016-02-26 19:00:00.789' - TIMESTAMP '2016-02-26 19:00:00.123' |
+---------------------------------------------------------------------------+
| 0 |
+---------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
{noformat}
> TimestampDiffConvertlet uses second resolution even for sub-second units
> ------------------------------------------------------------------------
>
> Key: CALCITE-1662
> URL: https://issues.apache.org/jira/browse/CALCITE-1662
> Project: Calcite
> Issue Type: Bug
> Reporter: Remus Rusanu
> Assignee: Julian Hyde
>
> {{TimestampDiffConvertlet.convertCall}} uses {{TimeUnit.SECOND}} for
> sub-second units.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
