javaspringpostgresqlspring-data-jpacriteria-api

How to extract time using CriteriaBuilder and PostgreSQL

I have a problem to map PostgreSQL expression below, that extracts hours/minutes/dow, to a CriteriaBuilder expression:

select extract(hour from start_time) from meetings where id = 5;

meetings have start_time field in a database that maps into ZonedDateTime start Java entity field later. I have tried CriteriaBuilder.function(), but I can not realy understand how to map hour from start_time expression as one function parameter.

Thanks for a help!

Solution

  • I have used DATE_PART function to fetch hours count instead:

    builder.function("DATE_PART", Integer.class, builder.literal("hour"), root.get("start_time"))

    SQL-request looks similar:

    select date_part('hour', start_time) from meetings where id = 5;