.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in Oracle Database :::.

1. create table 

CREATE TABLE datefilter (
    datex DATE NOT NULL
);

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
),
all_dates AS (
    SELECT dt + INTERVAL '1' SECOND AS datex     -- tanggal 1
    FROM cte
    UNION ALL
    SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND  -- tanggal 15
    FROM cte
)
SELECT datex FROM all_dates;

3. check sample data 
 
select count(*) from datefilter;
select * from datefilter order by datex desc;
 

 

4. per bulan 
SELECT
    to_char(datex, 'YYYY-MM') AS month,
    COUNT(*) AS record_count
FROM
    datefilter
GROUP BY
    to_char(datex, 'YYYY-MM')
ORDER BY
    month;
 

5. perhari 

SELECT
    CAST(datex AS date) AS day,   -- Ambil tanggal (tanpa waktu)
    COUNT(*) AS record_count
FROM
    datefilter
GROUP BY
    CAST(datex AS date)
ORDER BY
    day;
 

6. pertahun

SELECT
    EXTRACT(YEAR FROM datex) AS year,
    COUNT(*) AS record_count
FROM
    datefilter
GROUP BY
    EXTRACT(YEAR FROM datex)
ORDER BY
    year;
 

7. pertahun pakai where 

SELECT *
FROM datefilter
WHERE EXTRACT(YEAR FROM datex) = 2025
ORDER BY datex DESC;
 

SELECT COUNT(*)
FROM datefilter
WHERE EXTRACT(YEAR FROM datex) = 2025;
 

SELECT *
FROM datefilter
WHERE EXTRACT(YEAR FROM datex) <= 2025
  AND EXTRACT(MONTH FROM datex) <= 6
ORDER BY datex DESC;

  

8. pertahun pakai where

SELECT
    EXTRACT(YEAR FROM datex) AS year,
    COUNT(*) AS record_count
FROM
    datefilter
WHERE
    EXTRACT(YEAR FROM datex) = 2025
GROUP BY
    EXTRACT(YEAR FROM datex)
ORDER BY
    year;
   

 
9. per bulan pakai where 

SELECT
    TO_CHAR(datex, 'YYYY-MM') AS month,
    COUNT(*) AS record_count
FROM
    datefilter
WHERE
    EXTRACT(YEAR FROM datex) = 2025
GROUP BY
    TO_CHAR(datex, 'YYYY-MM')
ORDER BY
    month;
 

10. perhari pakai where     
SELECT
    CAST(datex AS date) AS day,
    COUNT(*) AS record_count
FROM
    datefilter
WHERE
    EXTRACT(YEAR FROM datex) = 2025
GROUP BY
    CAST(datex AS date)
ORDER BY
    day;