[
https://issues.apache.org/jira/browse/PHOENIX-1704?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14351855#comment-14351855
]
Serhiy Bilousov edited comment on PHOENIX-1704 at 3/8/15 1:24 AM:
------------------------------------------------------------------
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 |
+----------------------+----------------------+----------------------+----------------------+----------------------+
select trunc(f_ulong,'DAY'), trunc(f_ulong,'HOUR'), trunc(f_ulong,'MINUTE'),
trunc(f_ulong,'SECOND'), trunc(f_ulong,'MILLISECOND') from dev.t2;
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------+
| F_ULONG | F_ULONG
| F_ULONG | F_ULONG
| F_ULONG |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------+
| 1394049227000 | 1394049227000
| 1394049227000 | 1394049227000
| 1394049227000 |
| 1425744792000 | 1425744792000
| 1425744792000 | 1425744792000
| 1425744792000 |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------+
2 rows selected (0.058 seconds)
{noformat}
was (Author: sergey.b):
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)
