title: Manipulate timestamp (GMT to given UTC), using an SQL function in JPA
body: <p>The objective is to group data stored with a GMT 0 timestamp, by month/day/hour - according to the client's UTC time (meaning, client's timezone offset should be added to the timestamp).</p>
  
  <p>In SQL, the query should look something like (when grouping by days):</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT   * 
  FROM     myentity 
  GROUP BY dayofweek ( date_add (mytimestampcolumn, INTERVAL timezonedifference hours ))
  </code></pre>
  
  <p>In JPA, the following code works fine as "dayofweek" is a known (registered) function to jpa:</p>
  
  <pre><code>CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  Expression&lt;Integer&gt; timeUnitFunction = criteriaBuilder.function("dayofweek",  Integer.class, from.&lt;Date&gt;get("timestampColumn"));
  query.groupBy(timeUnitFunction);
  </code></pre>
  
  <p>It however, does not consider the timezone offset, so I'd like to use another db function and change the second line into something like:</p>
  
  <pre><code>criteriaBuilder.function("dayofweek", Integer.class, criteriaBuilder.function("date_add", Date.class, "hour", timezoneDifferece,  from.&lt;Date&gt;get("timestampColumn")))
  </code></pre>
  
  <p>Only <code>ADD_DATE</code> and <code>ADD_TIME</code> are not, from what I could see, registered JPA functions.</p>
  
  <p>Using a custom dialect for calculating a simple timezone offset seems like an overkill.</p>
  
  <p>Any suggestions on an alternative JPA registered function that can manipulate timestamps or any other way to get wanted results (Correct grouping by month/day/hour on a varying timezone offset) would be much appreciated. </p>
  
  <p><strong>EDIT:</strong>
  As commented by Neil, date_add, along with any other SQL function, is invokable by <code>CriteriaBuilder</code>'s function method.</p>
  
  <p>I couldn't however, provide <a href="https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add" rel="nofollow">date_add</a>'s required argument of "INTERVAL expr unit" format (using <code>CriteriaBuilder.literal</code> to create a String Expression didn't work).</p>
  
  <p><strong>Problem was solved</strong> using the SQL "<a href="https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz" rel="nofollow">convert_tz</a>" function with String literals as the timezone values.</p>