Return-Path: X-Original-To: apmail-phoenix-dev-archive@minotaur.apache.org Delivered-To: apmail-phoenix-dev-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 7CFFA179A3 for ; Sun, 8 Mar 2015 09:28:06 +0000 (UTC) Received: (qmail 674 invoked by uid 500); 8 Mar 2015 09:28:03 -0000 Delivered-To: apmail-phoenix-dev-archive@phoenix.apache.org Received: (qmail 626 invoked by uid 500); 8 Mar 2015 09:28:03 -0000 Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list dev@phoenix.apache.org Received: (qmail 615 invoked by uid 99); 8 Mar 2015 09:28:03 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 08 Mar 2015 09:28:03 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,T_RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO mail.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with SMTP; Sun, 08 Mar 2015 09:27:41 +0000 Received: (qmail 560 invoked by uid 99); 8 Mar 2015 09:27:38 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 08 Mar 2015 09:27:38 +0000 Date: Sun, 8 Mar 2015 09:27:38 +0000 (UTC) From: "Serhiy Bilousov (JIRA)" To: dev@phoenix.incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (PHOENIX-1704) Add year() built-in function MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ 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)