1

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!

dmkov
  • 334
  • 2
  • 11
  • what type it is now? As far as I can tell <>= operators works fine with timestamps – Antoniossss Sep 21 '18 at 21:39
  • 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 Sep 21 '18 at 21:57
  • 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 Sep 21 '18 at 21:58
  • 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 Sep 21 '18 at 22:01
  • try CAST https://stackoverflow.com/a/4793868/1527544 and CAST(xxx as time) – Antoniossss Sep 21 '18 at 22:18
  • thank you @Antoniossss! It works with one strange issue. Even if I cast it to time, hibernate wants input parameter with Date type (`Parameter value [00:00] did not match expected type [java.util.Date (n/a)]`). In this case should be tested additionally with day overflows. – dmkov Sep 22 '18 at 08:04

1 Answers1

3

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
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • 1
    Good idea! However, I noticed that it automatically extracts hour in local timezone. So if I pass integer parameter, it should be also converted to this timezone before. Also, if I need to compare time, it should be minutes there as well. It became complex and I will probably just add another field for time value, but after some polishing it can be managed to working solution! – dmkov Sep 22 '18 at 08:00
  • Yes, well if the data has multiple timezones you will need to massage the data into a single timezone e.g. to_utc_timestamp() so the the hour filter makes sense. You don't mention minutes in the question, but perhaps making use of unix_timestamp() will help with that. – Paul Maxwell Sep 22 '18 at 08:06
  • 1
    After some thinking was able to combine hours and minutes in query in the following way: @Query("SELECT m FROM Table m WHERE m.status.id = :statusId " + " AND m.startDate >= :startDate AND m.startDate <= :endDate " + " AND (extract(hour FROM m.startDate) * 60 + extract(minute FROM m.startDate)) >= :startTime " + " AND (extract(hour FROM m.startDate) * 60 + extract(minute FROM m.startDate)) <= :endTime ") Thank you for the initial push in this direction! – dmkov Sep 22 '18 at 08:49