phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.apache.phoenix.end2end;

import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.schema.TypeMismatchException;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Properties;

import static org.junit.Assert.*;


public class ToDateFunctionIT extends ParallelStatsDisabledIT {

    private static final long ONE_HOUR_IN_MILLIS = 1000L * 60L * 60L;

    private Connection conn;

    @Before
    public void setUp() throws SQLException {
        conn = DriverManager.getConnection(getUrl());
    }

    @After
    public void tearDown() throws SQLException {
        conn.close();
    }

    private static java.util.Date callToDateFunction(Connection conn, String invocation) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(String.format("SELECT %s FROM \"SYSTEM\".CATALOG LIMIT 1", invocation));
        assertTrue(rs.next());
        java.util.Date returnValue = (java.util.Date)rs.getObject(1);
        rs.close();
        stmt.close();
        return returnValue;
    }

    private Date callToDateFunction(String invocation) throws SQLException {
        return (Date)callToDateFunction(conn, invocation);
    }

    private Time callToTimeFunction(String invocation) throws SQLException {
        return (Time)callToDateFunction(conn, invocation);
    }

    private Timestamp callToTimestampFunction(String invocation) throws SQLException {
        return (Timestamp)callToDateFunction(conn, invocation);
    }

    @Test
    public void testToDate_Default() throws SQLException {
        // Default time zone is GMT, so this is timestamp 0
        assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01 00:00:00')").getTime());
        assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01 00:00:00.000')").getTime());
        assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01')").getTime());
        assertEquals(0L, callToDateFunction("TO_DATE('1970/01/01','yyyy/MM/dd')").getTime());

        // Test other ISO 8601 Date Compliant Formats to verify they can be parsed
        try {
            callToDateFunction("TO_DATE('2015-01-27T16:17:57+00:00')");
            callToDateFunction("TO_DATE('2015-01-27T16:17:57Z')");
            callToDateFunction("TO_DATE('2015-W05')");
            callToDateFunction("TO_DATE('2015-W05-2')");
        } catch (Exception ex) {
            fail("TO_DATE Parse ISO8601 Time Failed due to:" + ex);
        }
    }

    @Test
    public void testToTime_Default() throws SQLException {
        // Default time zone is GMT, so this is timestamp 0
        assertEquals(0L, callToTimeFunction("TO_TIME('1970-01-01 00:00:00')").getTime());
        assertEquals(0L, callToTimeFunction("TO_TIME('1970-01-01 00:00:00.000')").getTime());
        assertEquals(0L, callToTimeFunction("TO_TIME('1970-01-01')").getTime());
        assertEquals(0L, callToTimeFunction("TO_TIME('1970/01/01','yyyy/MM/dd')").getTime());

        // Test other ISO 8601 Date Compliant Formats to verify they can be parsed
        try {
            callToTimeFunction("TO_TIME('2015-01-27T16:17:57+00:00')");
            callToTimeFunction("TO_TIME('2015-01-27T16:17:57Z')");
            callToTimeFunction("TO_TIME('2015-W05')");
            callToTimeFunction("TO_TIME('2015-W05-2')");
        } catch (Exception ex) {
            fail("TO_TIME Parse ISO8601 Time Failed due to:" + ex);
        }
    }

    @Test
    public void testToTimestamp_Default() throws SQLException {
        // Default time zone is GMT, so this is timestamp 0
        assertEquals(0L, callToTimestampFunction("TO_TIMESTAMP('1970-01-01 00:00:00')").getTime());
        assertEquals(0L, callToTimestampFunction("TO_TIMESTAMP('1970-01-01 00:00:00.000')").getTime());
        assertEquals(0L, callToTimestampFunction("TO_TIMESTAMP('1970-01-01')").getTime());
        assertEquals(0L, callToTimestampFunction("TO_TIMESTAMP('1970/01/01','yyyy/MM/dd')").getTime());

        // Test other ISO 8601 Date Compliant Formats to verify they can be parsed
        try {
            callToTimestampFunction("TO_TIMESTAMP('2015-01-27T16:17:57+00:00')");
            callToTimestampFunction("TO_TIMESTAMP('2015-01-27T16:17:57Z')");
            callToTimestampFunction("TO_TIMESTAMP('2015-W05')");
            callToTimestampFunction("TO_TIMESTAMP('2015-W05-2')");
        } catch (Exception ex) {
            fail("TO_TIMESTAMP Parse ISO8601 Time Failed due to:" + ex);
        }
    }

    @Test
    public void testToDate_CustomDateFormat() throws SQLException {
        // A date without time component is at midnight
        assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01', 'yyyy-MM-dd')").getTime());
    }

    @Test
    public void testToDate_CustomTimeZone() throws SQLException {
        // We're using GMT+1, so that's an hour before the Java epoch
        assertEquals(
                -ONE_HOUR_IN_MILLIS,
                callToDateFunction("TO_DATE('1970-01-01', 'yyyy-MM-dd', 'GMT+1')").getTime());
    }

    @Test
    public void testToDate_LocalTimeZone() throws SQLException {
        assertEquals(
                Date.valueOf("1970-01-01"),
                callToDateFunction("TO_DATE('1970-01-01', 'yyyy-MM-dd', 'local')"));
    }

    @Test
    public void testToDate_CustomTimeZoneViaQueryServices() throws SQLException {
        Properties props = new Properties();
        props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, "GMT+1");
        Connection customTimeZoneConn = DriverManager.getConnection(getUrl(), props);

        assertEquals(
                -ONE_HOUR_IN_MILLIS,
                callToDateFunction(customTimeZoneConn, "TO_DATE('1970-01-01 00:00:00.000')").getTime());
    }

    @Test
    public void testToDate_CustomTimeZoneViaQueryServicesAndCustomFormat() throws SQLException {
        Properties props = new Properties();
        props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, "GMT+1");
        Connection customTimeZoneConn = DriverManager.getConnection(getUrl(), props);

        assertEquals(
                -ONE_HOUR_IN_MILLIS,
                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();

            conn.prepareStatement("upsert into TT values (0, 22120, 1426188807198)").execute();
            conn.commit();
            
            ResultSet rs = conn.prepareStatement("select a, b, ts, CAST(ts AS TIMESTAMP) from TT").executeQuery();
            assertTrue(rs.next());
            assertEquals(new Date(1426188807198L), rs.getObject(4));
            rs.close();

            try {
                rs = conn.prepareStatement("select a, b, ts, CAST(b AS TIMESTAMP) from TT").executeQuery();
                fail();
            } catch (TypeMismatchException e) {

            }

        } finally {
            conn.close();
        }
    }

    @Test
    public void testVarcharToDateComparision() throws SQLException {
        final String dateString1 = "1900-01-02";
        final String dateString2 = "2100-01-01";

        conn.prepareStatement(
                "CREATE TABLE SB(" +
                        "DATE_STRING VARCHAR(50) NOT NULL " +
                        "CONSTRAINT PK PRIMARY KEY (DATE_STRING))").execute();
        conn.commit();

        String upsertSql = String.format("upsert into SB values (?)");
        PreparedStatement stmt = conn.prepareStatement(upsertSql);
        stmt.setString(1, dateString1);
        stmt.execute();
        stmt.setString(1, dateString2);
        stmt.execute();
        conn.commit();

        String selectSql = "SELECT DATE_STRING FROM SB WHERE TO_DATE(DATE_STRING) > TO_DATE('2001-01-01')";
        ResultSet rs = conn.prepareStatement(selectSql).executeQuery();
        assertTrue(rs.next());
        String obtainedString = rs.getString("DATE_STRING");
        assertEquals("Did not get value that was inserted!!", dateString2, obtainedString);
        assertFalse("No more rows expected!!", rs.next());
    }
    
    @Test
    public void testToDateWithCloneMethod() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
    	String ddl = "create table " + tableName + " (k varchar primary key, v varchar[])";
        conn.createStatement().execute(ddl);
        String dateStr = "2100-01-01";
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('x',ARRAY['"+dateStr+"'])");
        conn.commit();
        ResultSet rs = conn.createStatement().executeQuery("select to_date(v[1], 'yyyy-MM-dd', 'local') from " + tableName);
        
        assertTrue(rs.next());
        assertEquals("Unexpected value for date ", Date.valueOf(dateStr), rs.getDate(1));
        assertFalse(rs.next());
    }
}