[
https://issues.apache.org/jira/browse/PHOENIX-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14391259#comment-14391259
]
Dave Hacker commented on PHOENIX-1748:
--------------------------------------
[~jamestaylor] This may actually be an issue. In RoundTimestampExpression,
CeilTimestampExpression and FloorDateExpression we coerce TIMESTAMP to DATE.
Climing that nanos have no affect. This appears to change the return type of
the expression. The reason we see it working in the test is because we use
ResultSet.getTimestamp() but the ResultSetMetaData.getColumnTypeName() is
returning DATE instead of TIMESTAMP as Serhiy points out. The question is is
this supposed to happen or is it a bug. If we change it to TIMESTAMP will we
break others?
> Appling TRUNC|ROUND|FLOOR|CEIL with DAY|HOUR|MINUTE|SECOND|MILLISECOND on
> TIMESTAMP should not truncate value to the DATE only.
> -------------------------------------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-1748
> URL: https://issues.apache.org/jira/browse/PHOENIX-1748
> Project: Phoenix
> Issue Type: Bug
> Reporter: Serhiy Bilousov
> Assignee: Dave Hacker
> Attachments: PHOENIX-1748.patch
>
>
> *Given* that input value is "YYYY-MM-DD HH:MM:SS.nnn" type of TIMESTAMP
> (UNSIGNED_TIMESTAMP, DATE, TIME etc)
> *When* applying TRUNC|ROUND|FLOOR|CEIL with DAY|HOUR|MINUTE|SECOND|MILLISECOND
> *Than* result should be "YYYY-MM-DD HH:MM:SS.nnn"
> But "YYYY-MM-DD" is returned instead.
> Basically when I do TRUNC on timestamp I would expect it to be timestamp with
> relevant parts truncated so for example I can GROUP BY on TRUNC
> (timestamp,'HOUR') and have my hourly aggregation.
> Here is test queries with cast(current_date() AS timestamp).
> {noformat}
> SELECT
> dt
> ,TRUNC(dt,'DAY') AS trunc_day_from_dt
> ,TRUNC(dt,'HOUR') AS trunc_hour_from_dt
> ,TRUNC(dt,'MINUTE') AS trunc_min_from_dt
> ,TRUNC(dt,'SECOND') AS trunc_sec_from_dt
> ,TRUNC(dt,'MILLISECOND') AS trunc_mil_from_dt
> FROM
> (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt,
> TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
> | TO_TIMESTAMP('2015-03-08 09:09:11.665') | TRUNC_DAY_FROM_DT |
> TRUNC_HOUR_FROM_DT | TRUNC_MIN_FROM_DT | TRUNC_SEC_FROM_DT |
> TRUNC_MIL_FROM_DT |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
> | 2015-03-08 09:09:11.665 | 2015-03-08 | 2015-03-08
> | 2015-03-08 | 2015-03-08 | 2015-03-08 |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
> 1 row selected (0.066 seconds)
> SELECT
> dt
> ,ROUND(dt,'DAY') AS round_day_from_d
> ,ROUND(dt,'HOUR') AS round_hour_from_d
> ,ROUND(dt,'MINUTE') AS round_min_from_d
> ,ROUND(dt,'SECOND') AS round_sec_from_d
> ,ROUND(dt,'MILLISECOND') AS round_mil_from_d
> FROM
> (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt,
> TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
> | TO_TIMESTAMP('2015-03-08 09:09:11.782') | ROUND_DAY_FROM_D |
> ROUND_HOUR_FROM_D | ROUND_MIN_FROM_D | ROUND_SEC_FROM_D |
> ROUND_MIL_FROM_D |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
> | 2015-03-08 09:09:11.782 | 2015-03-08 | 2015-03-08
> | 2015-03-08 | 2015-03-08 | 2015-03-08
> 09:09:11.782 |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
> 1 row selected (0.06 seconds)
> SELECT
> dt
> ,FLOOR(dt,'DAY') AS floor_day_from_dt
> ,FLOOR(dt,'HOUR') AS floor_hour_from_dt
> ,FLOOR(dt,'MINUTE') AS floor_min_from_dt
> ,FLOOR(dt,'SECOND') AS floor_sec_from_dt
> ,FLOOR(dt,'MILLISECOND') AS floor_mil_from_dt
> FROM
> (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt,
> TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
> | TO_TIMESTAMP('2015-03-08 09:09:11.895') | FLOOR_DAY_FROM_DT |
> FLOOR_HOUR_FROM_DT | FLOOR_MIN_FROM_DT | FLOOR_SEC_FROM_DT |
> FLOOR_MIL_FROM_DT |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
> | 2015-03-08 09:09:11.895 | 2015-03-08 | 2015-03-08
> | 2015-03-08 | 2015-03-08 | 2015-03-08 |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
> 1 row selected (0.059 seconds)
> SELECT
> dt
> ,CEIL(dt,'DAY') AS ceil_day_from_dt
> ,CEIL(dt,'HOUR') AS ceil_hour_from_dt
> ,CEIL(dt,'MINUTE') AS ceil_min_from_dt
> ,CEIL(dt,'SECOND') AS ceil_sec_from_dt
> ,CEIL(dt,'MILLISECOND') AS ceil_mil_from_dt
> FROM
> (SELECT current_date() AS d, cast(current_date() AS timestamp) AS dt,
> TO_NUMBER(current_date()) e FROM system.catalog LIMIT 1) t;
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
> | TO_TIMESTAMP('2015-03-08 09:09:12.009') | CEIL_DAY_FROM_DT |
> CEIL_HOUR_FROM_DT | CEIL_MIN_FROM_DT | CEIL_SEC_FROM_DT |
> CEIL_MIL_FROM_DT |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
> | 2015-03-08 09:09:12.009 | 2015-03-09 | 2015-03-08
> | 2015-03-08 | 2015-03-08 | 2015-03-08
> 09:09:12.009 |
> +------------------------------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------+
> 1 row selected (0.061 seconds)
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
