Package ch.tocco.nice2.persist.core.impl.hibernate.query.function

Class AbstractDatetimeAddFunction

java.lang.Object
ch.tocco.nice2.persist.core.api.hibernate.query.AbstractJdbcFunction
ch.tocco.nice2.persist.core.impl.hibernate.query.function.AbstractDatetimeAddFunction
All Implemented Interfaces:
JdbcFunction
Direct Known Subclasses:
DateAddFunction, DatetimeAddFunction
public abstract class AbstractDatetimeAddFunction extends AbstractJdbcFunction
The DATEADD function allows to calculate dates dynamically. The function receives 2 arguments:
  1. the reference date/datetime
  2. time interval to add
Example: "find DataTypes where eventDatetime < DATETIMEADD(\"now\", \"14 days\")" will create a query that lists all rows where the eventDatetime is in two weeks or earlier.

The function is postgresql specific and translates to postgres using postgres' datetime arithmetic syntax. The above example will result in the following statement: 


     SELECT data_types WHERE event_datetime < (CAST('2011-07-11 14:45:27' as TIMESTAMP) + CAST('14 days' as INTERVAL));
                                                    `- = first param: 'now'                    `- = second param
To subtract an interval from a reference date, just use negative intervals, like so 
     find DataTypes where eventDatetime < DATETIMEADD(\"now\", \"-14 days\")
Instead of using literals, parameters and identifiers are also possible, of course.

Since this is postgres specific, the notation for the reference date and interval parameters must comply to postgres notation for datetime and interval.
For the reference date this can be done using the Timestamp or Date class of the postgres jdbc driver. When using parameters for the reference date, pass in either a DateTime or LocalDate object.
An interval can be one of

  • <number> days -- example: "14 days", "-10 days"
  • HH:mm:ss -- example: "00:15:00", "10:30:00"
  • combination in the form: <number> days HH:mm:ss -- example: "14 days 10:30:00", "-10 days -10:30:00"
When using parameters, interval must be given as a String.

See postgres documentation and/or this wiki entry for more info. Examples of queries can be found in its test class.

  • Field Details

    • validArgumentTypes

      protected final Type<?>[] validArgumentTypes

  • Constructor Details

    • AbstractDatetimeAddFunction

      public AbstractDatetimeAddFunction(TypeManager typeManager, Type<?> returnType)

  • Method Details

    • createFunction

      public org.hibernate.dialect.function.SQLFunction createFunction(org.hibernate.type.TypeResolver typeResolver, SqlWriter writer)
      Returns:
      an SQLFunction that contains the raw sql statement that is executed on the db.
      See Also:
      • SQLFunctionTemplate

    • validateArguments

      public void validateArguments(jakarta.persistence.criteria.Expression<?>[] arguments)

    • argumentCount

      public int argumentCount()
      Returns:
      the number of expected arguments.

    • getTimeTypeAsString

      protected abstract String getTimeTypeAsString()