0

I have the following fields:

field_a TIMESTAMP(0)
field_b TIME

Is this possible to add field_b to field_a using SQL without any special custom functions?

Karord
  • 105
  • 6
  • You would need some function here AFAIK. If `field_b` had a certain time _unit_, such as hours, then you could use addition with the `interval` syntax. – Tim Biegeleisen Dec 06 '21 at 07:20
  • 4
    So what is your expected result of `'2021-12-06 08:21:45' + '17:42:10'`? –  Dec 06 '21 at 07:21
  • `'2021-12-06 08:21:45' + '17:42:10' = '2021-12-07 02:03:55'` – Karord Dec 06 '21 at 07:24
  • 1
    Then you need data type `interval`, not `time` (which is a point in time rather than a duration). – Laurenz Albe Dec 06 '21 at 07:26
  • In fact you are adding an `interval` to the timestamp, not `time`. The expression to do so is `select '2021-12-06 08:21:45'::timestamp + '17:42:10'::interval;`, i.e. `field_a + field_b::interval` will do. – Stefanov.sm Dec 06 '21 at 07:26
  • 1
    So your `time` is actually a _duration_. In that case you should be storing it as an `interval`. A `time` value is a "point in time" (e.g. this afternoon at 17"). It's **not** a duration. –  Dec 06 '21 at 07:29
  • Well this is not working: `field_a + field_b::interval`. I am doing this in Java with nativeQuery: `SELECT COUNT(V) FROM VISIT V WHERE V.VISIT_DATE <= :newVisitDate AND V.VISIT_DATE + V.VISIT_DURATION::INTERVAL >= :newVisitDateEnd;` `ERROR: syntax error at or near ":" Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]` – Karord Dec 06 '21 at 07:36
  • 1
    The reason is that nativeQuery misinterprets your expression. There is no syntax error but `::` needs to be escaped. See [this](https://stackoverflow.com/questions/4169582/escaping-the-colon-character-in-jpa-queries) post to fix it, the second answer. – Stefanov.sm Dec 06 '21 at 07:40
  • 1
    Thank you very much! It is working fine! – Karord Dec 06 '21 at 07:48
  • Use `cast(field_b as interval)` in your obfuscation layer. –  Dec 06 '21 at 08:48

0 Answers0