.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in PostgreSQL EDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini
    SELECT date_trunc('month', now())::date AS dt,
           1 AS lvl

    UNION ALL
    
    -- Mundur 36 bulan
    SELECT (dt - INTERVAL '1 month')::date,
           lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT dt + INTERVAL '1 millisecond'             -- tanggal 1
FROM cte
UNION ALL
SELECT dt + INTERVAL '14 days' + INTERVAL '1 millisecond'   -- tanggal 15
FROM cte;

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;