月度登录数据筛选需求：排除每日10:00-15:00时段

Hey there! Let's tweak your PL/SQL code to get the monthly data you need while excluding that daily 10:00 to 15:00 window. Here's how to do it properly:

First, a quick note on your original query: comparing dates as strings (like to_char(insert_date,'DD-MM-YY:HH:MM:SS') > '12-Mar-10') can lead to unexpected results because string comparisons don’t work the same way as date comparisons. We’ll fix that first, then add the exclusion logic.

Modified Query #

SET SERVEROUTPUT ON;
DECLARE
 CURSOR login_hist IS
 SELECT login_id, insert_date 
 FROM Login_history 
 WHERE 
   -- Target the entire month of March 2010 (adjust the date as needed)
   TRUNC(insert_date, 'MM') = TO_DATE('12-Mar-10', 'DD-Mon-YY')
   -- Exclude records between 10:00 AM and 3:00 PM (using 24-hour format for clarity)
   AND NOT (
     TO_CHAR(insert_date, 'HH24') >= '10' 
     AND TO_CHAR(insert_date, 'HH24') < '15'
   )
 ORDER BY insert_date DESC;
BEGIN
 FOR cur IN login_hist LOOP
 DBMS_OUTPUT.PUT_LINE( cur.login_id || ': ' || TO_CHAR(cur.insert_date,'DD-MM-YY:HH24:MI:SS') );
 END LOOP;
END;
/

What Changed & Why #

• Date Filtering: Using TRUNC(insert_date, 'MM') truncates the date to the first day of its month. Comparing this to TO_DATE('12-Mar-10', 'DD-Mon-YY') grabs all records from March 2010 automatically—no need to manually set start/end dates for the month.
• Exclusion Logic: The NOT (TO_CHAR(insert_date, 'HH24') >= '10' AND TO_CHAR(insert_date, 'HH24') < '15') condition skips any records where the hour (in 24-hour format) is between 10 and 14 (i.e., 10 AM to 2:59:59 PM). If you need to exclude up to 3:00 PM exactly, change < '15' to <= '15'.

Alternative Time Comparison Method #

If you prefer working with time intervals instead of string hours, you can replace the exclusion condition with this:

AND (
  insert_date - TRUNC(insert_date) < INTERVAL '10' HOUR 
  OR insert_date - TRUNC(insert_date) >= INTERVAL '15' HOUR
)

This checks if the time portion of insert_date is either before 10 AM or after (and including) 3 PM, which achieves the same result.

内容的提问来源于stack exchange，提问作者bilalhaider