I'm quite new to SQL and I got stuck on one thing.
Let's say that I have a database, where new records are being created every few mins and there is a 'dstamp' row which contains time when specific rows interfaced into database. I'm trying to write a query that (once ran) will show all records that interfaced into my database until 3pm (15:00), basically it would be a report, which whenever someone would run it, would always show rows that were created until 15:00 of the day the report was ran (including all past days, but from past days to include whole day, not only until 3pm).
Quick example:
Database
| xyz_id | dstamp |
|---|---|
| 111 | 02/01/2023 12:55:00 |
| 222 | 26/03/2023 14:51:23 |
| 333 | 26/03/2023 15:05:00 |
Result of the query (ran on 26/03/2023 15:10:00):
| xyz_id | dstamp |
|---|---|
| 111 | 02/01/2023 12:55:00 |
| 222 | 26/03/2023 14:51:23 |
So if someone would run the query at 1pm it would show all results until 1pm (cuz 3pm is the deadline), but if someone would run the query at 4pm it would show all results until 3pm [including all previous (full)days and ignore anything after 15:00:00 of current day]. I want the query to look on all days in the past (to show me all record until the deadline).
As I mentioned, I'm quite new to it so didn't try anything special, as I got stuck on how to make the query look for all records from previous day and todays date until 3pm.
I hope it all makes sense.
Could someone enlighten me if there is any way of doing it?
Filter against SYSDATE truncated back to midnight with 15 hours added:
SELECT *
FROM table-name
WHERE dstamp <= TRUNC(SYSDATE) + INTERVAL '15' HOUR;