如何用Spring Data Sort按两timestamp列的天数差排序？

需要从PostgreSQL数据库中，基于两列timestamp without time zone类型的日期差（closedate与opendate的天数差）对记录排序。当前使用Spring Data的org.springframework.data.domain.Sort实现单字段排序正常，但无法直接实现基于日期差的排序。

排序前数据：

opendate               closedate            number of days
2023-04-05 04:00:00   2023-04-06 04:00:00       1
2023-04-05 04:00:00   2023-04-05 04:00:00       0
2023-04-05 04:00:00   2023-04-07 04:00:00       2
2023-04-05 04:00:00   2023-04-05 04:00:00       0
2023-04-05 04:00:00   2023-04-08 04:00:00       3

期望排序后结果：

opendate               closedate            number of days
2023-04-05 04:00:00   2023-04-05 04:00:00       0
2023-04-05 04:00:00   2023-04-05 04:00:00       0
2023-04-05 04:00:00   2023-04-06 04:00:00       1
2023-04-05 04:00:00   2023-04-07 04:00:00       2
2023-04-05 04:00:00   2023-04-08 04:00:00       3

对应的原生PostgreSQL查询可实现需求：

select * from table order by (date(closedDate)-date(openDate)) desc ;

当前使用的Spring Data代码：

import org.springframework.data.domain.*;

Sort sort=Sort.by(openDate).descending();
Pageable paging=PageRequest.of(1,10,sort);

希望实现类似Sort sort=Sort.by(closedDate-openDate).descending();的效果，询问是否可通过Sort函数实现。

可以通过Sort实现，但不能直接用字段相减的写法，需用自定义排序表达式，有两种常用方式：

1. 直接传入原生SQL排序表达式 #

利用Sort.Order.by(String)方法，将日期差的SQL表达式作为参数传入，Spring Data会自动将其拼接到最终查询语句中：

import org.springframework.data.domain.*;

// 匹配PostgreSQL语法，转换为date类型后计算差值，此处用ascending对应期望的升序结果
Sort sort = Sort.by(Sort.Order.by("date(closedate) - date(opendate)")).ascending();
Pageable paging = PageRequest.of(0, 10, sort);

2. 自定义Repository查询方法 #

在Repository接口中通过@Query指定排序逻辑，兼容性更强：

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

public interface YourEntityRepository extends PagingAndSortingRepository<YourEntity, Long> {

    @Query("SELECT e FROM YourEntity e ORDER BY FUNCTION('date', e.closedate) - FUNCTION('date', e.opendate) ASC")
    Page<YourEntity> findAllSortedByDateDiff(Pageable pageable);
}

使用FUNCTION调用数据库的date()函数，后续切换数据库时只需修改函数名即可。

注意事项 #

• 确保表达式中的字段名与数据库列名一致（PostgreSQL默认区分大小写，通常为小写）；
• Spring Data 2.0及以上版本支持Sort.Order.by(String)传入自定义表达式。

内容的提问来源于stack exchange，提问作者Thejas