Question 243150 in PostgreSQL Question posted in
The official documentation can be found here.

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    Extract/Date_Part will give only the specified subfield from the timestamp (I assume ZonedDateTime maps to the Postgres data type timestamp with time zone). If you want the full time (hh:mi:ss) then rather that an extract for each you just cast the column to time. So

    select start::time, ...           -- Postgres exclusive 
OR 
select cast(start to time), ...   --- SQL standard

    If you do not want seconds then use to_char:

    select to_char(start, 'hh:mm'), ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search