@Test
public void toDate() throws Exception {
List<Expression> args =
Lists.newArrayList(getInvertedLiteral("2001-11-30 00:00:00:0", PDataType.VARCHAR));
evaluateAndAssertResult(
new ToDateFunction(args, null, DateUtil.getDateParser("yyyy-MM-dd HH:mm:ss:S")),
date(11, 30, 2001));
}
@Test
public void toChar() throws Exception {
List<Expression> args =
Lists.newArrayList(getInvertedLiteral(date(12, 11, 2001), PDataType.DATE));
evaluateAndAssertResult(
new ToCharFunction(
args, FunctionArgumentType.TEMPORAL, "", DateUtil.getDateFormatter("MM/dd/yy hh:mm a")),
"12/11/01 12:00 AM");
}
@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
BigDecimal finalResult = BigDecimal.ZERO;
for (int i = 0; i < children.size(); i++) {
if (!children.get(i).evaluate(tuple, ptr)) {
return false;
}
if (ptr.getLength() == 0) {
return true;
}
BigDecimal value;
PDataType type = children.get(i).getDataType();
SortOrder sortOrder = children.get(i).getSortOrder();
if (type == PTimestamp.INSTANCE || type == PUnsignedTimestamp.INSTANCE) {
value = (BigDecimal) (PDecimal.INSTANCE.toObject(ptr, type, sortOrder));
} else if (type.isCoercibleTo(PDecimal.INSTANCE)) {
value =
(((BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder))
.multiply(QueryConstants.BD_MILLIS_IN_DAY))
.setScale(6, RoundingMode.HALF_UP);
} else if (type.isCoercibleTo(PDouble.INSTANCE)) {
value =
((BigDecimal.valueOf(type.getCodec().decodeDouble(ptr, sortOrder)))
.multiply(QueryConstants.BD_MILLIS_IN_DAY))
.setScale(6, RoundingMode.HALF_UP);
} else {
value = BigDecimal.valueOf(type.getCodec().decodeLong(ptr, sortOrder));
}
finalResult = finalResult.add(value);
}
Timestamp ts = DateUtil.getTimestamp(finalResult);
byte[] resultPtr = new byte[getDataType().getByteSize()];
PTimestamp.INSTANCE.toBytes(ts, resultPtr, 0);
ptr.set(resultPtr);
return true;
}
public class TruncateFunctionTest extends BaseClientManagedTimeTest {
private static Format format = DateUtil.getDateParser(DateUtil.DEFAULT_MS_DATE_FORMAT);
private static final String DS1 = "1970-01-10 00:58:01.587";
private static final String DS2 = "1970-01-20 01:02:45.906";
private static final String DS3 = "1970-01-30 01:30:24.353";
private static Date toDate(String s) throws ParseException {
return (Date) (format.parseObject(s));
}
private static Timestamp toTimestamp(String s) throws ParseException {
return new Timestamp(((Date) (format.parseObject(s))).getTime());
}
@Test
public void testTruncate() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
ensureTableCreated(getUrl(), ATABLE_NAME, ts - 5);
Properties props = new Properties();
props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts - 3));
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
PreparedStatement stmt =
conn.prepareStatement(
"upsert into "
+ "ATABLE("
+ " ORGANIZATION_ID, "
+ " ENTITY_ID, "
+ " A_DATE, "
+ " A_TIMESTAMP)"
+ "VALUES (?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setString(2, ROW1);
stmt.setDate(3, toDate(DS1));
stmt.setTimestamp(4, toTimestamp(DS1));
stmt.execute();
stmt.setString(1, tenantId);
stmt.setString(2, ROW2);
stmt.setDate(3, toDate(DS2));
stmt.setTimestamp(4, toTimestamp(DS2));
stmt.execute();
stmt.setString(1, tenantId);
stmt.setString(2, ROW3);
stmt.setDate(3, toDate(DS3));
stmt.setTimestamp(4, toTimestamp(DS3));
stmt.execute();
conn.commit();
conn.close();
props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
conn = DriverManager.getConnection(getUrl(), props);
String query =
"SELECT entity_id, trunc(a_date, 'day', 7), trunc(a_timestamp, 'second', 10) FROM ATABLE WHERE organization_id = ?";
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(ROW1, rs.getString(1));
assertEquals(new Date((long) 7 * QueryConstants.MILLIS_IN_DAY), rs.getDate(2));
assertEquals(toTimestamp("1970-01-10 00:58:00.000"), rs.getTimestamp(3));
assertTrue(rs.next());
assertEquals(ROW2, rs.getString(1));
assertEquals(new Date((long) 14 * QueryConstants.MILLIS_IN_DAY), rs.getDate(2));
assertEquals(toTimestamp("1970-01-20 01:02:40.000"), rs.getTimestamp(3));
assertTrue(rs.next());
assertEquals(ROW3, rs.getString(1));
assertEquals(new Date((long) 28 * QueryConstants.MILLIS_IN_DAY), rs.getDate(2));
assertEquals(toTimestamp("1970-01-30 01:30:20.000"), rs.getTimestamp(3));
assertFalse(rs.next());
} finally {
conn.close();
}
}
}