[
https://issues.apache.org/jira/browse/PHOENIX-1704?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14351983#comment-14351983
]
Serhiy Bilousov edited comment on PHOENIX-1704 at 3/8/15 9:29 AM:
------------------------------------------------------------------
I am kind lost here. I feel like I missing something here because it can not be
all wrong :) My understanding is such that (TRUNC|ROUND|FLOOR|CEIL should do
manipulation on specified part DAY|HOUR|MINUTE|SECOND|MILLISECOND of the
TIMESTAMP but that not what I see here. In addition I would expect returned
datatype stay the same (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}
I also noticed that current_date() even showing returning DATE but still hold
time part what you can see after casting it to the TIMESTAMP and I would expect
it to have no time (0:0:0). Where current_date() AS timestamp getting time ? :)
>From what I guess TRUNC intentions is (and I have to take my previous comment
>back) I do not think TRUNC can be used as EXTRACT. EXTRACT return part of the
>date as number but TRUNC just truncate and return date/timestamp with
>truncated part.
Anyway I may be just really missing something here ...
was (Author: sergey.b):
I am kind lost here. I feel like I missing something here because it can not be
all wrong :) My understanding is such that (TRUNC|ROUND|FLOOR|CEIL should do
manipulation on specified part DAY|HOUR|MINUTE|SECOND|MILLISECOND of the
TIMESTAMP but that not what I see here. In addition I would expect returned
datatype stay the same (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}
I also noticed that current_date() even show returning DATE but still hold time
part what you can see after casting it to the TIMESTAMP and I would expect it
to have no time (0:0:0). Where
current_date() AS timestamp getting time ? :)
>From what I guess TRUNC intentions is I have to take my previous comment back.
>I do not think TRUNC can be use as EXTRACT. Extract return part of the date as
>number but TRUNC just truncate ecpected to return
date/timestamp with truncated part.
Anyway I may be just really missing something here ...
> Add year() built-in function
> ----------------------------
>
> Key: PHOENIX-1704
> URL: https://issues.apache.org/jira/browse/PHOENIX-1704
> Project: Phoenix
> Issue Type: Bug
> Reporter: Alicia Ying Shu
> Assignee: Alicia Ying Shu
> Attachments: Phoenix-1704-v1.patch, Phoenix-1704.patch
>
>
> SELECT YEAR('2014-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss') FROM YEARFUNC -->
> 2014
> SELECT YEAR('2014-12-13') FROM YEARFUNC --> 2014
> SELECT YEAR('Sat, 3 Feb 2014 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z',
> 'UTC') FROM YEARFUNC --> 2014
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
