Support HQL datetime functions and arithmetic expressions

XMLWordPrintableJSON

    • Type: New Feature
    • Resolution: Unresolved
    • Priority: Unknown
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      This ticket scope includes both functions and arithmetic expressions, particularly:

      1. Functions (Hibernate User Guide relevant section: https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html#hql-functions-datetime)
      2. Arithmetic expressions (Hibernate User Guide relevant section: https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html#hql-Datetime-arithmetic)

      HQL datetime functions and arithmetic expressions can be represented in MQL as follows

      Functions:

      • $year,$month, $week, }}$hour, $minute, $second, {{$millisecond, }}$dayOfYear{{{},$dayOfMonth,$dayOfWeek

      Particularly the functions could be broken down as follows:

      Special function Purpose Signature MQL operators
      extract() Extract a datetime field extract(field from x) $year,$month, $week, }}$hour, $minute, $second, {{$millisecond, }}$dayOfYear{{{},$dayOfMonth,$dayOfWeek, etc.
      format() Format a datetime as a string format(datetime as pattern)  TBD
      trunc() or truncate() Datetime truncation truncate(datetime, field)  TBD

      Seems both format() and trunc() function could be implemented based on extract(), though not 100% sure.

       

      The following functions are abbreviations of the above extract function:

      Function Long form using extract() MQL operators
      year() extract(year from x) $year
      month() extract(month from x) $month
      day() extract(day from x) $dayOfMonth
      hour() extract(year from x) $hour
      minute() extract(year from x) $minute
      second() extract(year from x) $second

       

      Arithmetic expressions:

      • $dateAdd, $dateSubtract
      • $dateDiff

      Particularly the arithmetic expressions could be broken down as follows (the last two duration operators involve simple math and doesn't require Mongo operator):

       

      Operator Expression type Example Resulting type MQL operator
      - Difference between two dates your.birthday - local date year/quarter/month/week/day duration $dateDiff
      - Difference between two datetimes local datetime - record.lastUpdated week/day/hour/minute/second/nanosecond duration $dateDiff
      + Sum of a date and a year/quarter/month/week/day duration local date + 1 week date $dateAdd
      + Sum of a datetime and a week/day/hour/minute/second/nanosecond duration record.lastUpdated + 1 second datetime $dateAdd
      * Product of an integer and a duration billing.cycles * 30 day duration      n/a
      by unit Convert a duration to an integer (1 year) by day integer      n/a

      Addressing the source code notes tagged with TODO-HIBERNATE-88 is in scope of this ticket.

            Assignee:
            Unassigned
            Reporter:
            Jeffrey Yemin
            None
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: