MySQL Cheatsheet

cheatsheet.sql

ALTER TABLE `absensi_rekap`
  CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  
UPDATE absensi_raw SET absensi_timestamp = CONVERT_TZ(absensi_datetime, 'Asia/Jakarta', 'UTC');
UPDATE absensi_raw SET absensi_timestamp = CONVERT_TZ(absensi_datetime, '+07:00', '+00:00');

DELIMITER //
CREATE TRIGGER absensi_raw_insert_timestamp
BEFORE INSERT
ON absensi_raw FOR EACH ROW
BEGIN
set new.absensi_timestamp = CONVERT_TZ(new.absensi_datetime, '+07:00', '+00:00');
END; // 
DELIMITER ;