[
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)
