[
https://issues.apache.org/jira/browse/PHOENIX-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14392700#comment-14392700
]
Serhiy Bilousov commented on PHOENIX-1748:
------------------------------------------
Thank you [~dhacker1341] for not giving up on that one and [~jamestaylor] for
still trying to :)
I am totally understand what you saying [~jamestaylor] and did find option in
SQuirreL (Global settings\Data Type Controls \ Interpret DATE columns as
TIMESTAMP). I also understand you reasoning behind cutting 4 bytes off.
Saying that I should respectfully disagree with a whole idea of a function to
change return data type (unless that is purpose of the function). It should be
up to the user to make that decision (using CAST when needed)
It just very confusing and may cause whole bunch of issued down the line
(changing return datatype).
If to follow the same logic than
{noformat}
SELECT TRUNC(25.5) c FROM system.catalog LIMIT 1;
{noformat}
Should return INTEGER but it returning DOUBLE (what is correct).
Not to mention that documentation for TRUNC|ROUND|FLOOR|CEIL specifically
points out that _*This method returns the same type as its first argument.*_
If PHOENIX community have a consensus on that it is how it should be (cutting
TIMESTAMP to DATE) that at least it should be consistent and clearly stated in
documentation (I hope thou that we do right thing here).
Hope that helps.
> 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)
