/** Return the expression to be rendered when the RHS is an interval type */ private final Field<T> getIntervalExpression(Configuration configuration) { SQLDialect dialect = configuration.getDialect(); int sign = (operator == ADD) ? 1 : -1; switch (dialect) { case ASE: case SYBASE: case SQLSERVER: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{dateadd}(mm, {0}, {1})", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { // SQL Server needs this cast. Field<Timestamp> lhsAsTS = lhs.cast(Timestamp.class); DayToSecond interval = rhsAsDTS(); // Be careful with 32-bit INT arithmetic. Sybase ASE // may fatally overflow when using micro-second precision if (interval.getNano() != 0) { return field( "{dateadd}(ss, {0}, {dateadd}(us, {1}, {2}))", getDataType(), val(sign * (long) interval.getTotalSeconds()), val(sign * interval.getMicro()), lhsAsTS); } else { return field( "{dateadd}(ss, {0}, {1})", getDataType(), val(sign * (long) interval.getTotalSeconds()), lhsAsTS); } } } case CUBRID: case MYSQL: { Interval interval = rhsAsInterval(); if (operator == SUBTRACT) { interval = interval.neg(); } if (rhs.get(0).getType() == YearToMonth.class) { return field( "{date_add}({0}, {interval} {1} {year_month})", getDataType(), lhs, val(interval, String.class)); } else { if (dialect == MYSQL) { return field( "{date_add}({0}, {interval} {1} {day_microsecond})", getDataType(), lhs, val(interval, String.class)); } else { return field( "{date_add}({0}, {interval} {1} {day_millisecond})", getDataType(), lhs, val(interval, String.class)); } } } case DB2: { if (rhs.get(0).getType() == YearToMonth.class) { if (operator == ADD) { return lhs.add(field("{0} month", val(rhsAsYTM().intValue()))); } else { return lhs.sub(field("{0} month", val(rhsAsYTM().intValue()))); } } else { // DB2 needs this cast if lhs is of type DATE. DataType<T> type = lhs.getDataType(); if (operator == ADD) { return lhs.cast(Timestamp.class) .add(field("{0} microseconds", val(rhsAsDTS().getTotalMicro()))) .cast(type); } else { return lhs.cast(Timestamp.class) .sub(field("{0} microseconds", val(rhsAsDTS().getTotalMicro()))) .cast(type); } } } case DERBY: case HSQLDB: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{fn {timestampadd}({sql_tsi_month}, {0}, {1}) }", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { return field( "{fn {timestampadd}({sql_tsi_second}, {0}, {1}) }", getDataType(), val(sign * (long) rhsAsDTS().getTotalSeconds()), lhs); } } case FIREBIRD: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{dateadd}({month}, {0}, {1})", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { return field( "{dateadd}({millisecond}, {0}, {1})", getDataType(), val(sign * (long) rhsAsDTS().getTotalMilli()), lhs); } } case H2: { if (rhs.get(0).getType() == YearToMonth.class) { return field( "{dateadd}('month', {0}, {1})", getDataType(), val(sign * rhsAsYTM().intValue()), lhs); } else { return field( "{dateadd}('ms', {0}, {1})", getDataType(), val(sign * (long) rhsAsDTS().getTotalMilli()), lhs); } } case INGRES: { throw new SQLDialectNotSupportedException( "Date time arithmetic not supported in Ingres. Contributions welcome!"); } case SQLITE: { String prefix = (sign > 0) ? "+" : "-"; if (rhs.get(0).getType() == YearToMonth.class) { return field( "{datetime}({0}, '" + prefix + rhsAsYTM().intValue() + " months')", getDataType(), lhs); } else { return field( "{datetime}({0}, '" + prefix + rhsAsDTS().getTotalSeconds() + " seconds')", getDataType(), lhs); } } case ORACLE: case POSTGRES: default: return new DefaultExpression(); } }