phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-1744) CAST from UNSIGNED_LONG (_INT) to * TIMESTAMP is not supported.
Date Sun, 22 Mar 2015 21:03:11 GMT

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

ASF GitHub Bot commented on PHOENIX-1744:
-----------------------------------------

Github user JamesRTaylor commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/52#discussion_r26907122
  
    --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java ---
    @@ -176,4 +177,60 @@ public void testToDate_CustomTimeZoneViaQueryServicesAndCustomFormat()
throws SQ
                     callToDateFunction(
                             customTimeZoneConn, "TO_DATE('1970-01-01', 'yyyy-MM-dd')").getTime());
         }
    +    
    +    @Test
    +    public void testTimestampCast() throws SQLException {
    +        Properties props = new Properties();
    +        props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, "GMT+1");
    +        Connection customTimeZoneConn = DriverManager.getConnection(getUrl(), props);
    +
    +        assertEquals(
    +            1426188807198L,
    +                callToDateFunction(
    +                        customTimeZoneConn, "CAST(1426188807198 AS TIMESTAMP)").getTime());
    +        
    +
    +        try {
    +            callToDateFunction(
    +                    customTimeZoneConn, "CAST(22005 AS TIMESTAMP)");
    +            fail();
    +        } catch (TypeMismatchException e) {
    +
    +        }
    +    }
    +    
    +    @Test
    +    public void testUnsignedLongToTimestampCast() throws SQLException {
    +        Properties props = new Properties();
    +        props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, "GMT+1");
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        conn.setAutoCommit(false);
    +        try {
    +            conn.prepareStatement(
    +                "create table TT("
    +                        + "a unsigned_int not null, "
    +                        + "b unsigned_int not null, "
    +                        + "ts unsigned_long not null "
    +                        + "constraint PK primary key (a, b, ts))").execute();
    +            conn.commit();
    --- End diff --
    
    FYI, no need for commit after DDL statements.


> CAST from UNSIGNED_LONG (_INT) to * TIMESTAMP is not supported.
> ---------------------------------------------------------------
>
>                 Key: PHOENIX-1744
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1744
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: Serhiy Bilousov
>            Assignee: Dave Hacker
>            Priority: Minor
>              Labels: 4.3.1
>
> Epoch time can be represented as INTEGER (up to the seconds) or LONG (up to the millisecond).
Currently CAST from UNSIGNED_LONG to TIMESTAMP not supported by Phoenix. 
> It make sense to have support for conversion from epoch (4 bytes or 8 bytes) to any datetime
like format curently supported by Phoenix (TIME, DATE, TIMESTAMP, UNSIGNED_TIME, UNSIGNED_DATE,
UNSIGNED_TIMESTAMP).
> HBase shell:
> {noformat}
> create 't','f1'
> put 't',"\x00\x00\x00\x01\x00\x00\x00\x01\x00\x00\x01L\x0Fz,\x1E",'f1:c1','test'
> {noformat}
> sqlline:
> {noformat}
> CREATE VIEW vT
> (   a UNSIGNED_INT NOT NULL
>    ,b UNSIGNED_INT NOT NULL
>    ,ts UNSIGNED_LONG NOT NULL
> CONSTRAINT pk PRIMARY KEY (a, b, ts))
> AS SELECT * FROM "t"
> DEFAULT_COLUMN_FAMILY ='f1';
>  select a, b, ts, CAST(1426188807198 AS TIMESTAMP) from vt;
> +----+----+----------------+------------------------------+
> | A  | B  |       TS       | TO_TIMESTAMP(1426188807198)  |
> +----+----+----------------+------------------------------+
> | 1  | 1  | 1426188807198  | 2015-03-12 19:33:27.198      |
> +----+----+----------------+------------------------------+
> {noformat}
> but
> {noformat}
> select a, b, ts, CAST(ts AS TIMESTAMP) from vt;
> Error: ERROR 203 (22005): Type mismatch. UNSIGNED_LONG and TIMESTAMP for TS (state=22005,code=203)
> {noformat}
> As per Gabriel Reid
> {quote}
> As a workaround, you can cast the UNSIGNED_LONG to a BIGINT first, and then cast it to
a TIMESTAMP, i.e.
>     select a, b, ts, CAST(CAST(ts AS BIGINT) AS TIMESTAMP) from vt;
> {quote}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message