Extract time from timestamp in Hibernate query

Question

I am trying to filter records in HQL with timestamp dates for some specific time range. For example, get all items created between 1pm and 3pm for last month.

My initial idea was to cast timestamp field to time by ::time from PostgreSQL. But Hibernate does not have this embedded function (I use HQL for building query).

It seems that there is date function but no time formatter. I do not have strict requirements for implementation and can adjust solution if it fits main idea. Now I send date boundaries as timestamps and time values as strings and do two parallel between comparisons.

@Query("SELECT t FROM Table t WHERE t.status.id = ?1"
            + " AND t.startDate >= ?2 AND t.startDate <= ?3"
            + " AND t.startDate::time >= ?4 AND t.startDate::time <= ?5")
Page<Item> findByStatusIdAndDateRange(Long activeId, final long startDate, final long endDate, final String startTime, final String endTime, Pageable pageRequest);

This is not a production code, so no performance issues with casting values in query.

Thank you for any advice!

what type it is now? As far as I can tell <>= operators works fine with timestamps — Antoniossss
startDate is TIMESTAMP(6) WITH TIME ZONE in Postgres and Instant in domain class. filter parameters are longs/timestamps for dates and strings with 'HH:MM' format for time — dmkov
why dont you just change filter criteria (and model) to actual timestamps (intstants in your case)? Splitting date and time is just kind of nonsense. — Antoniossss
I need filter dates and time interval separately. Define date period and then select specific hours there. For example, all records between 10:00 and 12:00 during some month. That is why I started with time casting — dmkov
try CAST stackoverflow.com/a/4793868/1527544 and CAST(xxx as time) — Antoniossss

Paul Maxwell Paul Maxwell · Accepted Answer · 2018-09-22T03:52:51

Documentation suggests you may "extract" the hour from the timestamp column:

extract(field FROM source)

Source must be a date, timestamp, interval or a string that can be converted into either a date or timestamp. Supported fields include: day, dayofweek, hour, minute, month, quarter, second, week and year. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-TypeConversionFunctions

and then you should be able to use that in the where clause

SELECT t.*
FROM Table t 
WHERE t.status.id = ?1
AND t.startDate >= ?2 AND t.startDate <= ?3
AND extract(hour FROM t.startDate)   >= ?4 
AND extract(hour FROM t.startDate)  <= ?5

Extract time from timestamp in Hibernate query

1 Answers