I have a Date object myCurrentDate which is in this format ‘2024-09-10 00:00:00.0’.
Date myCurrentDate = calendarRepository.getMyCurrentDate();
I have a product_table with create_date column which is of type timestamp.
Each day, many products may be added to the product_table which will have different timestamps.
For example:
| product_name | create_date |
|---|---|
| Sugar | 2024-09-10 11:37:25 |
| Chips | 2024-09-08 12:20:52 |
| Coffee | 2024-09-10 15:12:33 |
| Oranges | 2024-09-10 20:52:15 |
I need to use the Date object myCurrentDate to find all the rows that were created that day. So in my example, it should return Sugar, Coffee and Oranges but not Chips.
List<Products> productsReults= productRepository.findByCrtDt(myCurrentDate);
Jpa repository looks like this
List<Products> findByCrtDt(Date myCurrentDate)
Currently, it is not returning anything because myCurrentDate does not match anything due to the timestamp differences 2024-09-10 00:00:00.0 is not equal to 2024-09-10 11:37:25 and hence does not find Sugar, for example.
What would I need to do so that I can return all the desired results. Is there any way I can ignore the hours, minutes, seconds portion? I cannot change the database column type to Date from Timestamp.
4
It’s tempting to aim for building an sql query that looks like this:
SELECT *
FROM Product
WHERE truncate_time(create_date) = {myCurrentDate}
This is a bad idea. It’s important the {current_date} argument is modified, rather than the create_date column. Otherwise, you might have to mutate the column values for every row in the table… even rows that do not match and will not be included with the results. Additionally, this prevents the use of indexes with the column, which cuts to the core of database performance.
So then you might be tempted to run the query over a range, like this:
SELECT *
FROM Product
WHERE create_date BETWEEN {myCurrentDate} AND {myCurrentDate at 11:59:59 PM}
This is better, but it’s not quite right. Many databases may have odd tick or millisecond resolutions, leaving you open to the rare issue of missing values recorded very near the end of the day. Unlikely, but still possible, and the fix is easy, so why not do it?
What we really want is for Spring to ultimately generate a query using a date range comparison done with two conditional expressions comprising a half-open range, where the upper bound is an exclusive check for the first instant of the day after the end of the range. Something like this:
SELECT *
FROM Product
WHERE create_date >= {myCurrentDate at 00:00:00 (time is already truncated)}
AND create_date < {myCurrentDate at 00:00:00 + 1 day}
The exclusive vs inclusive test here makes a BETWEEN query a poor option. This has the best chance to perform well, with no chance to miss records near the end of the day.
Unfortunately, I’m not a Spring expert, so I can’t tell you how to get it to generate the query this way, especially since we can’t see the repository methods. I can only tell you what you should be looking for.
How about using cast(create_date as date) to match its data type to your variable?
1