.::: Query Check day, hour & early time, check time, different time in MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle DB to Get the Start of the Current Hour and the Previous Hour :::.

  

A. MSSQL

1. one day before

select @@servername as ServerName, GETDATE() as Currentdate, CAST(CONVERT(date, GETDATE() - 1) AS datetime) as datebefore , CAST(CONVERT(date, GETDATE()) AS datetime) as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= CAST(CONVERT(date, GETDATE() - 1) AS datetime) 
  AND tanggal <  CAST(CONVERT(date, GETDATE()) AS datetime);
  

2. one hour before

select @@servername as ServerName, GETDATE() as Currentdate, DATEADD(HOUR, -1, GETDATE()) as datebefore , GETDATE() as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= DATEADD(HOUR, -1, GETDATE()) 
  AND tanggal <  GETDATE();
  

3. one hour before & early

select @@servername as ServerName, GETDATE() as Currentdate, DATEADD(HOUR, -1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)) as datebefore , DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0) as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= DATEADD(HOUR, -1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
  AND tanggal <  DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0);
  

4. 6 hour before & early

select @@servername as ServerName, GETDATE() as Currentdate, DATEADD(HOUR, -6, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)) as datebefore , DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0) as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= DATEADD(HOUR, -6, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
  AND tanggal <  DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0);
 

B. MariaDB / MySQL

1. one day before

SELECT @@hostname AS ServerName,NOW() AS CurrentDate,CAST(DATE(NOW() - INTERVAL 1 DAY) AS DATETIME) AS DateBefore,CAST(DATE(NOW()) AS DATETIME) AS DateCurrent;
       
INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= CAST(DATE(NOW() - INTERVAL 1 DAY) AS DATETIME)
  AND tanggal <  CAST(DATE(NOW()) AS DATETIME);
 

2. one hour before 

SELECT @@hostname AS ServerName,NOW() AS CurrentDate,DATE_SUB(NOW(), INTERVAL 1 HOUR) AS DateBefore,NOW() AS DateCurrent;

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
  AND tanggal <  NOW();
  

3. one hour before & early

SELECT @@hostname AS ServerName,NOW() AS CurrentDate,DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR) AS DateBefore,DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DateCurrent;
       
INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR)
  AND tanggal <  DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00');

 

4. 6 hour before & early

SELECT @@hostname AS ServerName,NOW() AS CurrentDate,DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), INTERVAL 6 HOUR) AS DateBefore,DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS DateCurrent;

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), INTERVAL 6 HOUR)
  AND tanggal <  DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00');
 

C. PostgreSQL / EDB

1. one day before

SELECT 
    inet_server_addr() AS ServerName,  -- setara @@hostname
    NOW() AS CurrentDate,
    CAST((NOW() - INTERVAL '1 day')::date AS timestamp) AS DateBefore,
    CAST(NOW()::date AS timestamp) AS DateCurrent;

       
INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= (NOW() - INTERVAL '1 day')::date
  AND tanggal <  NOW()::date;
 

2. one hour before 

SELECT 
    inet_server_addr() AS ServerName,  -- kira-kira setara @@hostname
    NOW() AS CurrentDate,
    NOW() - INTERVAL '1 hour' AS DateBefore,
    NOW() AS DateCurrent;

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= NOW() - INTERVAL '1 hour'
  AND tanggal < NOW();
 

3. one hour before & early 

SELECT 
    inet_server_addr() AS ServerName,  -- kira-kira setara @@hostname
    NOW() AS CurrentDate,
    TO_CHAR((NOW() - INTERVAL '1 hour')::timestamp(0), 'YYYY-MM-DD HH24:00:00') AS DateBefore,
    TO_CHAR(NOW()::timestamp(0), 'YYYY-MM-DD HH24:00:00') AS DateCurrent;

       
INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= (date_trunc('hour', now()) - INTERVAL '1 hour')
  AND tanggal <  date_trunc('hour', now());
 

4. 6 hour before & early

SELECT 
    inet_server_addr() AS ServerName,  -- kira-kira setara @@hostname
    NOW() AS CurrentDate,
    TO_CHAR((NOW() - INTERVAL '1 hour')::timestamp(0), 'YYYY-MM-DD HH24:00:00') AS DateBefore,
    TO_CHAR(NOW()::timestamp(0), 'YYYY-MM-DD HH24:00:00') AS DateCurrent;

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= date_trunc('hour', now()) - interval '6 hour'
  AND tanggal <  date_trunc('hour', now());
 

D. Oracle

1. one day before

SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') AS ServerName,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CurrentDate,
       TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD HH24:MI:SS') AS DateBefore,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS DateCurrent
FROM dual;

SELECT col1,
       col2,
       col3,
       TO_CHAR(tanggal, 'YYYY-MM-DD HH24:MI:SS') AS tanggal
FROM source_table
WHERE tanggal >= TO_DATE(TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
  AND tanggal <  TO_DATE(TO_CHAR(SYSDATE,     'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS');
 

 
2. one hour before

SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') AS ServerName,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CurrentDate,
       TO_CHAR(SYSDATE - (1/24), 'YYYY-MM-DD HH24:MI:SS') AS DateBefore,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS DateCurrent
FROM dual;

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1,
       col2,
       col3,
       TO_CHAR(tanggal, 'YYYY-MM-DD HH24:MI:SS')
FROM source_table
WHERE tanggal >= SYSDATE - (1/24)
  AND tanggal <  SYSDATE;
 

3. one hour before & early

SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') AS ServerName,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CurrentDate,
       TO_CHAR(TRUNC(SYSDATE, 'HH') - (1/24), 'YYYY-MM-DD HH24:MI:SS') AS DateBefore,
       TO_CHAR(TRUNC(SYSDATE, 'HH'), 'YYYY-MM-DD HH24:MI:SS') AS DateCurrent
FROM dual;

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1,
       col2,
       col3,
       TO_CHAR(tanggal, 'YYYY-MM-DD HH24:MI:SS') AS tanggal
FROM source_table
WHERE tanggal >= TRUNC(SYSDATE, 'HH') - (1/24)
  AND tanggal <  TRUNC(SYSDATE, 'HH');
 

4. 6 hour before & early

SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') AS ServerName,
       SYSDATE AS CurrentDate,
       TRUNC(SYSDATE, 'HH') - (6/24) AS DateBefore,
       TRUNC(SYSDATE, 'HH') AS DateCurrent
FROM dual;

-- Menyimpan data
INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= TRUNC(SYSDATE, 'HH') - (6/24)
  AND tanggal <  TRUNC(SYSDATE, 'HH');

-- Mengecek hasil dengan jam, menit, detik
SELECT col1,
       col2,
       col3,
       TO_CHAR(tanggal, 'YYYY-MM-DD HH24:MI:SS') AS tanggal
FROM target_table
ORDER BY tanggal;