[
https://issues.apache.org/jira/browse/PHOENIX-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14368167#comment-14368167
]
James Taylor commented on PHOENIX-1748:
---------------------------------------
[~dhacker1341] - would you mind investigating? One thing to watch out for is
the way sqlline displays DATE and TIME columns. It may just be showing you the
day part when in actuality the finer granularity is still there in the value.
This may or may not be the case here, but it's the first thing I'd check.
> 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
>
> *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)
