phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Serhiy Bilousov (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-1704) Add year() built-in function
Date Sun, 08 Mar 2015 09:27:38 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-1704?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14351983#comment-14351983
] 

Serhiy Bilousov commented on PHOENIX-1704:
------------------------------------------

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)

Mime
View raw message