Examples of org.jooq.types.Interval

org.jooq.types.Interval

A substitute for JDBC's missing java.sql.Interval data type.

JDBC lacks an important data type that is present in most SQL databases: INTERVAL. The SQL:2008 standard states that there are two types of intervals:

4.6.3 Intervals
There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH.

INTERVAL can be combined with date time data types according to the following operation table:

Operand 1	Operator	Operand 2	Result Type
Datetime	–	Datetime	Interval
Datetime	+ or –	Interval	Datetime
Interval	+	Datetime	Datetime
Interval	+ or –	Interval	Interval
Interval	* or /	Numeric	Interval
Numeric	*	Interval	Interval

Interval implementations can be expected to also also extend {@link Number}.

Note: only a few databases actually support this data type on its own. You can still use it for date time arithmetic in other databases, though, through {@link Field#add(Field)} and {@link Field#sub(Field)} Databases that havebeen observed to natively support INTERVAL data types are:

{@link SQLDialect#HSQLDB}
{@link SQLDialect#INGRES}
{@link SQLDialect#ORACLE}
{@link SQLDialect#POSTGRES}

These dialects have been observed to partially support INTERVAL data types in date time arithmetic functions, such as TIMESTAMPADD, and TIMESTAMPDIFF:

{@link SQLDialect#CUBRID}
{@link SQLDialect#MYSQL}

@author Lukas Eder

                    }
                }


                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));
                    }

View Full Code Here


            switch (dialect.family()) {
                case CUBRID:
                case MARIADB:
                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, Utils.field(interval, String.class));
                    }
                    else {
                        if (dialect == CUBRID) {
                            return field("{date_add}({0}, {interval} {1} {day_millisecond})", getDataType(), lhs, Utils.field(interval, String.class));
                        }
                        else {
                            return field("{date_add}({0}, {interval} {1} {day_microsecond})", getDataType(), lhs, Utils.field(interval, String.class));
                        }
                    }
                }


                case DERBY:
                case HSQLDB: {
                    Field<T> result;


                    if (rhs.get(0).getType() == YearToMonth.class) {
                        result = field("{fn {timestampadd}({sql_tsi_month}, {0}, {1}) }",
                            getDataType(), val(sign * rhsAsYTM().intValue()), lhs);
                    }
                    else {
                        result = field("{fn {timestampadd}({sql_tsi_second}, {0}, {1}) }",
                            getDataType(), val(sign * (long) rhsAsDTS().getTotalSeconds()), lhs);
                    }


                    // [#1883] TIMESTAMPADD returns TIMESTAMP columns. If this
                    // is a DATE column, cast it to DATE
                    return castNonTimestamps(configuration, result);
                }


                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 SQLITE: {
                    boolean ytm = rhs.get(0).getType() == YearToMonth.class;
                    Field<?> interval = val(ytm ? rhsAsYTM().intValue() : rhsAsDTS().getTotalSeconds());


                    if (sign < 0) {
                        interval = interval.neg();
                    }


                    interval = interval.concat(inline(ytm ? " months" : " seconds"));
                    return field("{datetime}({0}, {1})", getDataType(), lhs, interval);
                }


                /* [pro] xx
                xxxx xxxxxxx x

View Full Code Here

TOP

Related Classes of org.jooq.types.Interval

org.jooq.impl.Expression$DateExpression

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.