You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Serhiy Bilousov (JIRA)" <ji...@apache.org> on 2015/04/09 00:46:13 UTC
[jira] [Comment Edited] (PHOENIX-1748) Applying
TRUNC|ROUND|FLOOR|CEIL on TIMESTAMP should maintain return type of
TIMESTAMP
[ https://issues.apache.org/jira/browse/PHOENIX-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14486249#comment-14486249 ]
Serhiy Bilousov edited comment on PHOENIX-1748 at 4/8/15 10:46 PM:
-------------------------------------------------------------------
what version are you running it on and what client do you use?
latest agreement on this one was that metadata returned by thouse functions incorrect (should be same as input parameter and not changed)
[read above | https://issues.apache.org/jira/browse/PHOENIX-1748?focusedCommentId=14392700&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14392700]
Thank you
was (Author: sergey.b):
what version are you running it on and what client do you use?
latest agreement on this one was that metadata returned by thouse functions incorrect (should be same as input parameter and not changed)
[read above https://issues.apache.org/jira/browse/PHOENIX-1748?focusedCommentId=14392700&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14392700]
> Applying TRUNC|ROUND|FLOOR|CEIL on TIMESTAMP should maintain return type of TIMESTAMP
> -------------------------------------------------------------------------------------
>
> Key: PHOENIX-1748
> URL: https://issues.apache.org/jira/browse/PHOENIX-1748
> Project: Phoenix
> Issue Type: Bug
> Reporter: Serhiy Bilousov
> Labels: Newbie
> 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)