[
https://issues.apache.org/jira/browse/PHOENIX-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Serhiy Bilousov updated PHOENIX-1748:
-------------------------------------
Summary: Appling TRUNC|ROUND|FLOOR|CEIL with
DAY|HOUR|MINUTE|SECOND|MILLISECOND on TIMESTAMP should not truncate value to
the DATE only. (was: Appling TRUNC|ROUND|FLOOR|CEIL with
DAY|HOUR|MINUTE|SECOND|MILLISECOND on TIMESTAMP should not truncate value to to
the DATE only.)
> 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
>
> *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)
