ВЫБЕРИТЕ среднюю разницу в метках времени в JPA с Postgres и HSQL

Question

У меня есть стол с двумя timestamp колонны, startTime а также stopTimeи я хотел бы рассчитать среднюю разницу этих временных меток в моей таблице. У меня есть решение, которое работает в Postgres и в HSQLDB (которое я использую для локального модульного тестирования), но не в обоих, и у меня возникают проблемы, пытаясь найти общее решение.

Postgres:

 select EXTRACT(EPOCH FROM(avg(m.stopTime - m.startTime))) from Measures m 

HSQL:

 select avg(FUNC('UNIX_TIMESTAMP', m.stopTime) - FUNC('UNIX_TIMESTAMP', m.startTime) from Measures m

Есть ли способ использовать один и тот же запрос для обеих баз данных? Кажется, что все функции, которые я нашел, поддерживаются только в одной или другой базе данных.

Я думаю, что моя главная проблема в том, что нет единой функции для преобразования метки времени в секунды для выполнения вычислений. EPOCH совместим только с Postgres и UNIX_TIMESTAMP совместим только с HSQL.

Answer 1

Суть вашей проблемы - преобразование дат и временных отметок в количество секунд. Вместо того, чтобы использовать эпоху, я буду использовать юлианскую дату для даты. Я преобразую юлианскую дату в секунды, а затем добавлю количество секунд с каждой минуты для каждой сравниваемой метки времени. Следующий запрос не вычисляет разницу, он просто конвертирует дату в число, похожее на обеих платформах. Вам придется делать это один раз для каждой сравниваемой даты. примечание: замените "текущую" метку времени на m.startTime и m.stopTime соответственно.

select 
(to_number(to_char(current_timestamp,'J'),'99999999999999999999')*86400/*convert from julian days to julian seconds*/) 
       + (to_number(to_char(current_timestamp,'HH'),'99') * 3600) /*Hours to seconds */
       +  (to_number(to_char(current_timestamp,'MM'),'99') * 60) /*Minutes to seconds */
       +  (to_number(to_char(current_timestamp,'SS'),'99') /*add in the seconds*/

Ужасно как грех, я знаю - возможно, вы можете переписать его проще как функцию, но, поскольку я не знаю полного набора функций hsqls, я оставлю это в этой форме, а не с помощью CTE или функции.