.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years
INSERT INTO datefilter (datex)
WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini (tanggal 1)
    SELECT
        DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS dt,
        1 AS lvl

    UNION ALL

    -- Mundur 36 bulan
    SELECT
        DATE_SUB(dt, INTERVAL 1 MONTH),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
SELECT dt + INTERVAL 1000 MICROSECOND         -- tanggal 1
FROM cte

UNION ALL

SELECT (dt + INTERVAL 14 DAY) + INTERVAL 1000 MICROSECOND   -- tanggal 15
FROM cte;
 
3. check sample data 
 
select count(*) from datefilter ;
select * from datefilter order by datex desc;
 

4. per bulan 
SELECT
    DATE_FORMAT(datex, '%Y-%m') AS month,
    COUNT(*) AS record_count
FROM
    datefilter
GROUP BY
    DATE_FORMAT(datex, '%Y-%m')
ORDER BY
    month;

  

5. perhari 

SELECT
    CAST(datex AS DATE) AS `day`,   -- Ambil tanggal saja
    COUNT(*) AS record_count
FROM
    datefilter
GROUP BY
    CAST(datex AS DATE)
ORDER BY
    `day`;
 

6. pertahun

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

7. pertahun pakai where 

select * from datefilter where YEAR(datex)=2025 order by datex desc;
SELECT COUNT(*) FROM datefilter WHERE YEAR(datex) = 2025;
 

SELECT * FROM datefilter
WHERE YEAR(datex) <= 2025  AND MONTH(datex) <= 6     -- di bawah Juni
ORDER BY datex DESC;
 

SELECT count(*) FROM datefilter
WHERE YEAR(datex) <= 2025  AND MONTH(datex) <= 6 ;    -- di bawah Juni
 

8. pertahun pakai where

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

 

 

9. per bulan pakai where 

SELECT
    DATE_FORMAT(datex, '%Y-%m') AS month,
    COUNT(*) AS record_count
FROM
    datefilter
WHERE
    YEAR(datex) = 2025
GROUP BY
    DATE_FORMAT(datex, '%Y-%m')
ORDER BY
    month;
    

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