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 01:22:38 GMT

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

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

That what my thinking was to regarding EXTRACT. Do you mean that TRUNC basically should be
extended to fully cover http://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
?

I cant get TRUNC to work for some reason. Is it expected behaviour ?

{noformat}
DROP TABLE IF EXISTS dev.t2;
CREATE TABLE dev.t2 (f_ulong UNSIGNED_LONG NOT NULL,f_ts TIMESTAMP NOT NULL, CONSTRAINT pk
PRIMARY KEY (f_ulong,f_ts));

/*
1425744792000 - GMT: Sat, 07 Mar 2015 16:13:12 GMT
1394049227000 - GMT: Wed, 05 Mar 2014 19:53:47 GMT
*/

upsert into dev.t2 values (CAST (1425744792000 AS UNSIGNED_LONG),CAST (1425744792000 AS TIMESTAMP));
upsert into dev.t2 values (CAST (1394049227000 AS UNSIGNED_LONG),CAST (1394049227000 AS TIMESTAMP));
{noformat}


{noformat}
select trunc(1425744792000,'DAY'), trunc(1425744792000,'HOUR') FROM dev.t2 LIMIT 1; 
+---------------------+---------------------+
|    1425744792000    |    1425744792000    |
+---------------------+---------------------+
| 1425744792000       | 1425744792000       |
+---------------------+---------------------+
1 row selected (0.035 seconds)

select trunc(f_ts,'DAY'), trunc(f_ts,'HOUR'), trunc(f_ts,'MINUTE'), trunc(f_ts,'SECOND'),
trunc(f_ts,'MILLISECOND') from dev.t2;
+----------------------+----------------------+----------------------+----------------------+----------------------+
| FLOOR(TO_DATE(F_TS)) | FLOOR(TO_DATE(F_TS)) | FLOOR(TO_DATE(F_TS)) | FLOOR(TO_DATE(F_TS))
| FLOOR(TO_DATE(F_TS)) |
+----------------------+----------------------+----------------------+----------------------+----------------------+
| 2014-03-05           | 2014-03-05           | 2014-03-05           | 2014-03-05        
  | 2014-03-05           |
| 2015-03-07           | 2015-03-07           | 2015-03-07           | 2015-03-07        
  | 2015-03-07           |
+----------------------+----------------------+----------------------+----------------------+----------------------+

{noformat}


> 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