MariaDB ve MySQL’de DATE ve DATETIME Alanlarla Çalışma

Tarafındankomut
0 Yorum

Veritabanı yönetiminde en çok hata yapılan konuların başında tarih ve zaman alanları gelir. Yanlış format, saat dilimi karışıklığı, karşılaştırma hataları… Bunların hepsi production ortamında ciddi sorunlara yol açabilir. Bu yazıda MariaDB ve MySQL üzerinde DATE ve DATETIME alanlarla çalışmanın tüm inceliklerini, gerçek dünya senaryolarıyla birlikte ele alacağız.

DATE ve DATETIME Veri Tipleri Hakkında Temel Bilgiler

Önce neyin ne olduğunu netleştirelim. MariaDB/MySQL’de tarih ve zamanla ilgili birkaç farklı veri tipi var:

  • DATE: Sadece tarih bilgisi saklar. Format: YYYY-MM-DD. Örnek: 2024-03-15
  • DATETIME: Tarih ve saat bilgisini birlikte saklar. Format: YYYY-MM-DD HH:MM:SS. Örnek: 2024-03-15 14:30:00
  • TIMESTAMP: DATETIME’a benzer ama UTC olarak saklar ve saat dilimi dönüşümü yapar. Range: 1970-01-01 00:00:01 ile 2038-01-19 03:14:07 arası
  • TIME: Sadece saat bilgisi. Format: HH:MM:SS
  • YEAR: Sadece yıl. Format: YYYY

DATETIME ile TIMESTAMP arasındaki fark birçok sysadmin’i yanıltır. TIMESTAMP, verinin INSERT/UPDATE edildiği andaki UTC zamanı saklar ve sunucunun saat dilimi ayarına göre otomatik dönüşüm yapar. DATETIME ise girdiğiniz değeri olduğu gibi saklar, saat diliminden bağımsızdır. Uluslararası kullanıcıları olan bir uygulama yönetiyorsanız bu fark kritik önem taşır.

Tablo Oluşturma ve Alan Tanımlama

Gerçek bir senaryo üzerinden gidelim. Bir e-ticaret platformu için sipariş tablosu oluşturuyoruz:

CREATE TABLE siparisler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    musteri_id INT NOT NULL,
    siparis_tarihi DATE NOT NULL,
    siparis_zamani DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    guncelleme_zamani DATETIME ON UPDATE CURRENT_TIMESTAMP,
    teslim_tarihi DATE,
    iptal_tarihi DATETIME NULL,
    toplam_tutar DECIMAL(10,2),
    durum ENUM('beklemede', 'isleniyor', 'kargoda', 'teslim_edildi', 'iptal')
);

Burada dikkat edilmesi gereken noktalar var. DEFAULT CURRENT_TIMESTAMP ile kayıt oluşturulduğunda otomatik olarak o anki zamanı alıyoruz. ON UPDATE CURRENT_TIMESTAMP ise her güncellemede ilgili alanı otomatik güncelliyor. NULL olarak tanımlanan iptal_tarihi ise sipariş iptal edilene kadar boş kalabilir, bu da sorgu yazarken önemli.

Temel INSERT ve SELECT İşlemleri

Tarih değerleri girerken her zaman YYYY-MM-DD formatını kullanın. Başka format deneyen arkadaşların başının belaya girdiğini defalarca gördüm:

-- Doğru format ile veri ekleme
INSERT INTO siparisler (musteri_id, siparis_tarihi, siparis_zamani, teslim_tarihi, toplam_tutar, durum)
VALUES 
    (101, '2024-03-15', '2024-03-15 09:30:00', '2024-03-20', 249.90, 'beklemede'),
    (102, '2024-03-15', NOW(), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 89.50, 'beklemede'),
    (103, CURDATE(), CURRENT_TIMESTAMP, NULL, 415.00, 'isleniyor');

-- Temel SELECT sorguları
SELECT 
    id,
    musteri_id,
    siparis_tarihi,
    DATE_FORMAT(siparis_zamani, '%d.%m.%Y %H:%i') AS turkce_format,
    teslim_tarihi,
    toplam_tutar
FROM siparisler
WHERE durum != 'iptal';

NOW(), CURDATE(), CURRENT_TIMESTAMP fonksiyonlarının farkına dikkat edin. CURDATE() sadece tarihi döndürür, NOW() ve CURRENT_TIMESTAMP ise tarih ve saati birlikte döndürür.

DATE_FORMAT ile Tarih Biçimlendirme

Veritabanından çektiğiniz tarihleri doğrudan uygulama katmanında biçimlendirmek yerine SQL seviyesinde yapabilirsiniz. Özellikle raporlama sorgularında çok işe yarar:

SELECT 
    id,
    musteri_id,
    -- Türkiye formatı: gün.ay.yıl
    DATE_FORMAT(siparis_tarihi, '%d.%m.%Y') AS tarih_tr,
    -- Uzun format
    DATE_FORMAT(siparis_zamani, '%d %M %Y, %H:%i:%s') AS uzun_format,
    -- Sadece ay ve yıl
    DATE_FORMAT(siparis_tarihi, '%m/%Y') AS ay_yil,
    -- ISO 8601 format
    DATE_FORMAT(siparis_zamani, '%Y-%m-%dT%H:%i:%s') AS iso_format,
    -- Haftanın günü ile
    DATE_FORMAT(siparis_tarihi, '%W, %d %M %Y') AS haftanin_gunu
FROM siparisler;

Sık kullanılan DATE_FORMAT parametreleri:

  • %Y: 4 haneli yıl (2024)
  • %y: 2 haneli yıl (24)
  • %m: 2 haneli ay (03)
  • %M: Ayın tam adı (March)
  • %d: 2 haneli gün (15)
  • %D: Gün ve eki (15th)
  • %H: 24 saat formatında saat (14)
  • %h: 12 saat formatında saat (02)
  • %i: Dakika (30)
  • %s: Saniye (00)
  • %W: Haftanın tam günü (Friday)
  • %w: Haftanın günü sayısal (0=Pazar)

Tarih Karşılaştırma ve Filtreleme

Tarih alanlarında WHERE koşulları yazarken çok dikkatli olmak gerekiyor. Yanlış yazılmış bir sorgu, indeksi bypass edip tüm tabloyu tarayabilir:

-- Belirli bir tarihteki siparişler
SELECT * FROM siparisler 
WHERE siparis_tarihi = '2024-03-15';

-- Tarih aralığı sorgusu (BETWEEN kullanımı)
SELECT id, musteri_id, siparis_tarihi, toplam_tutar
FROM siparisler
WHERE siparis_tarihi BETWEEN '2024-03-01' AND '2024-03-31';

-- DATETIME alanında tarih bazlı filtreleme
-- DİKKAT: Bu şekilde yazarsanız indeks kullanılmaz!
-- SELECT * FROM siparisler WHERE DATE(siparis_zamani) = '2024-03-15';

-- Bunun yerine şöyle yazın, indeks dostu:
SELECT * FROM siparisler 
WHERE siparis_zamani >= '2024-03-15 00:00:00' 
  AND siparis_zamani < '2024-03-16 00:00:00';

-- Son 30 günün siparişleri
SELECT id, musteri_id, siparis_tarihi, toplam_tutar, durum
FROM siparisler
WHERE siparis_tarihi >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY siparis_tarihi DESC;

-- Bu haftanın siparişleri
SELECT * FROM siparisler
WHERE YEARWEEK(siparis_tarihi, 1) = YEARWEEK(CURDATE(), 1);

Önemli not: DATE(), MONTH(), YEAR() gibi fonksiyonları WHERE koşulunda direkt alan üzerinde kullanmaktan kaçının. Bu fonksiyonlar alanı wrap ettiğinde MySQL/MariaDB indeksi kullanamaz ve full table scan yapar. Büyük tablolarda performansı mahveder.

DATE_ADD, DATE_SUB ve DATEDIFF Fonksiyonları

Tarih hesaplamaları sysadmin işlerinde sık karşılaşılan bir ihtiyaç. Log temizleme scriptlerinde, otomatik arşivleme işlemlerinde, SLA takibinde sürekli kullanıyoruz:

-- DATE_ADD ve DATE_SUB örnekleri
SELECT 
    id,
    siparis_tarihi,
    -- 5 gün sonrası
    DATE_ADD(siparis_tarihi, INTERVAL 5 DAY) AS beklenen_teslim,
    -- 3 ay sonrası
    DATE_ADD(siparis_tarihi, INTERVAL 3 MONTH) AS garanti_bitis,
    -- 1 yıl öncesi
    DATE_SUB(siparis_tarihi, INTERVAL 1 YEAR) AS bir_yil_oncesi,
    -- Gecen ay ayni gun
    siparis_tarihi - INTERVAL 1 MONTH AS gecen_ay,
    -- DATEDIFF: iki tarih arası gün farkı
    DATEDIFF(COALESCE(teslim_tarihi, CURDATE()), siparis_tarihi) AS gecen_gun,
    -- Teslim süresi aşıldı mı?
    CASE 
        WHEN teslim_tarihi IS NOT NULL AND teslim_tarihi > DATE_ADD(siparis_tarihi, INTERVAL 7 DAY)
        THEN 'SLA İHLALİ'
        WHEN teslim_tarihi IS NULL AND CURDATE() > DATE_ADD(siparis_tarihi, INTERVAL 7 DAY)
        THEN 'GECİKMEDE'
        ELSE 'NORMAL'
    END AS sla_durumu
FROM siparisler;

-- TIMESTAMPDIFF: daha detaylı zaman farkı hesabı
SELECT 
    id,
    siparis_zamani,
    TIMESTAMPDIFF(MINUTE, siparis_zamani, NOW()) AS dakika_once,
    TIMESTAMPDIFF(HOUR, siparis_zamani, NOW()) AS saat_once,
    TIMESTAMPDIFF(DAY, siparis_zamani, NOW()) AS gun_once
FROM siparisler
ORDER BY siparis_zamani DESC
LIMIT 10;

INTERVAL ifadesinde kullanabileceğiniz birimler:

  • SECOND: Saniye
  • MINUTE: Dakika
  • HOUR: Saat
  • DAY: Gün
  • WEEK: Hafta
  • MONTH: Ay
  • QUARTER: Çeyrek yıl
  • YEAR: Yıl
  • MINUTE_SECOND: Dakika ve saniye birlikte
  • HOUR_MINUTE: Saat ve dakika birlikte
  • DAY_HOUR: Gün ve saat birlikte

Gerçek Dünya Senaryosu: Aylık Satış Raporu

Müşteriniz her ay başında geçen ayın satış raporunu istiyor. Bunu otomatikleştirmek için şu sorguyu kullanabilirsiniz:

-- Geçen ayın satış raporu - aylık bazda gruplama
SELECT 
    DATE_FORMAT(siparis_tarihi, '%Y-%m') AS ay,
    COUNT(*) AS toplam_siparis,
    COUNT(CASE WHEN durum = 'teslim_edildi' THEN 1 END) AS tamamlanan,
    COUNT(CASE WHEN durum = 'iptal' THEN 1 END) AS iptal_edilen,
    SUM(CASE WHEN durum != 'iptal' THEN toplam_tutar ELSE 0 END) AS toplam_ciro,
    AVG(CASE WHEN durum = 'teslim_edildi' THEN toplam_tutar END) AS ortalama_siparis_tutari,
    MIN(siparis_tarihi) AS ilk_siparis,
    MAX(siparis_tarihi) AS son_siparis
FROM siparisler
WHERE siparis_tarihi >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND siparis_tarihi < DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY DATE_FORMAT(siparis_tarihi, '%Y-%m')
ORDER BY ay DESC;

-- Son 12 ayın trendi
SELECT 
    DATE_FORMAT(siparis_tarihi, '%Y-%m') AS ay,
    COUNT(*) AS siparis_adedi,
    ROUND(SUM(toplam_tutar), 2) AS aylik_ciro,
    ROUND(AVG(toplam_tutar), 2) AS ortalama_tutar
FROM siparisler
WHERE siparis_tarihi >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
  AND durum != 'iptal'
GROUP BY DATE_FORMAT(siparis_tarihi, '%Y-%m')
ORDER BY ay ASC;

Bu sorguyu bir cron job ile çalıştırıp sonuçları CSV’ye yazabilirsiniz:

#!/bin/bash
# Aylık rapor scripti - crontab'a eklemek için
# 0 6 1 * * /opt/scripts/aylik_rapor.sh

RAPOR_TARIHI=$(date +%Y-%m)
CIKTI_DOSYA="/var/reports/satis_raporu_${RAPOR_TARIHI}.csv"

mysql -u rapor_user -p'guclu_sifre' e_ticaret_db -e "
SELECT 
    DATE_FORMAT(siparis_tarihi, '%Y-%m') AS ay,
    COUNT(*) AS toplam_siparis,
    SUM(CASE WHEN durum != 'iptal' THEN toplam_tutar ELSE 0 END) AS toplam_ciro,
    COUNT(CASE WHEN durum = 'iptal' THEN 1 END) AS iptal_sayisi
FROM siparisler
WHERE siparis_tarihi >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND siparis_tarihi < DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY DATE_FORMAT(siparis_tarihi, '%Y-%m');
" | sed 's/t/,/g' > "$CIKTI_DOSYA"

echo "Rapor oluşturuldu: $CIKTI_DOSYA"

NULL Değer Yönetimi ve Tarih Kontrolleri

NULL tarihleri yönetmek, özellikle karşılaştırma sorgularında dikkat ister. Teslim edilmemiş ya da iptal edilmemiş siparişlerde bazı tarih alanları NULL olacaktır:

-- NULL tarih kontrolü
SELECT 
    id,
    musteri_id,
    siparis_tarihi,
    teslim_tarihi,
    iptal_tarihi,
    -- COALESCE ile NULL yerine varsayılan değer
    COALESCE(teslim_tarihi, DATE_ADD(siparis_tarihi, INTERVAL 7 DAY)) AS tahmini_teslim,
    -- IFNULL kısa versiyonu
    IFNULL(teslim_tarihi, 'Henüz Teslim Edilmedi') AS teslim_durumu,
    -- IS NULL ile filtreleme
    CASE 
        WHEN iptal_tarihi IS NOT NULL THEN 'İptal Edildi'
        WHEN teslim_tarihi IS NOT NULL THEN 'Teslim Edildi'
        WHEN siparis_tarihi < DATE_SUB(CURDATE(), INTERVAL 10 DAY) THEN 'Gecikmeli'
        ELSE 'İşlemde'
    END AS genel_durum
FROM siparisler
WHERE iptal_tarihi IS NULL
ORDER BY siparis_tarihi ASC;

-- Belirli gün sayısından fazla teslim edilmemiş siparişler
SELECT 
    s.id,
    s.musteri_id,
    s.siparis_tarihi,
    DATEDIFF(CURDATE(), s.siparis_tarihi) AS gecen_gun_sayisi
FROM siparisler s
WHERE s.teslim_tarihi IS NULL
  AND s.iptal_tarihi IS NULL
  AND DATEDIFF(CURDATE(), s.siparis_tarihi) > 7
ORDER BY gecen_gun_sayisi DESC;

Saat Dilimi Yönetimi

Çok lokasyonlu sistemlerde saat dilimi yönetimi gerçek bir kabus olabilir. Türkiye’deyiz, sunucumuz UTC’de çalışıyor diyelim, kullanıcılardan bazıları farklı ülkelerden sipariş veriyor. Bu durumda TIMESTAMP kullanıyorsanız şunları yapabilirsiniz:

-- Sunucunun saat dilimi kontrolü
SELECT @@global.time_zone, @@session.time_zone;

-- Saat dilimi dönüşümü
SELECT 
    id,
    siparis_zamani AS utc_zaman,
    CONVERT_TZ(siparis_zamani, '+00:00', '+03:00') AS turkey_zaman,
    CONVERT_TZ(siparis_zamani, '+00:00', '-05:00') AS new_york_zaman,
    CONVERT_TZ(siparis_zamani, '+00:00', '+09:00') AS tokyo_zaman
FROM siparisler
WHERE siparis_zamani >= DATE_SUB(NOW(), INTERVAL 24 HOUR);

-- Session bazında saat dilimi ayarlama
SET time_zone = '+03:00';

-- Sonrasında çalışan sorgular Türkiye saatini kullanır
SELECT NOW() AS simdi_tr;

-- Global olarak ayarlamak için (my.cnf'e de ekleyin kalıcı olsun)
-- SET GLOBAL time_zone = '+03:00';

my.cnf veya my.ini dosyasına eklenecek kalıcı saat dilimi ayarı:

[mysqld]
default-time-zone = '+03:00'

Log Temizleme Senaryosu: Tarih Bazlı Arşivleme

Sysadmin olarak en sık karşılaştığım işlerden biri eski log ve işlem kayıtlarını arşivleyip ana tablodan silmek. İşte production’da kullandığım tipik bir yaklaşım:

-- Arşiv tablosu oluşturma (ana tablo ile aynı yapı)
CREATE TABLE siparisler_arsiv LIKE siparisler;
ALTER TABLE siparisler_arsiv ADD COLUMN arsiv_tarihi DATETIME DEFAULT CURRENT_TIMESTAMP;

-- 1 yıldan eski tamamlanmış siparişleri arşive taşıma
-- Önce arşive kopyala
INSERT INTO siparisler_arsiv 
SELECT *, NOW() 
FROM siparisler
WHERE durum IN ('teslim_edildi', 'iptal')
  AND siparis_tarihi < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- Kaç kayıt etkilendiğini kontrol et
SELECT ROW_COUNT() AS arsivlenen_kayit_sayisi;

-- Arşive geçtikten sonra ana tablodan sil
-- LIMIT kullanımı büyük tablolarda lock sürelerini kısaltır
DELETE FROM siparisler
WHERE durum IN ('teslim_edildi', 'iptal')
  AND siparis_tarihi < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
LIMIT 10000;

-- Silme işlemini döngüsel yapmak için bash scripti
-- Bu kısmı bir döngüyle çalıştırın:
-- WHILE ROW_COUNT() > 0 DO ... END WHILE;

-- Arşiv tablosundan istatistik
SELECT 
    YEAR(siparis_tarihi) AS yil,
    MONTH(siparis_tarihi) AS ay,
    COUNT(*) AS kayit_sayisi,
    SUM(toplam_tutar) AS toplam_tutar
FROM siparisler_arsiv
GROUP BY YEAR(siparis_tarihi), MONTH(siparis_tarihi)
ORDER BY yil DESC, ay DESC;

İndeks Stratejisi ve Performans

DATE/DATETIME alanlarında performans için doğru indeks kullanımı şart. Birkaç önemli nokta:

-- Tarih alanına indeks ekleme
ALTER TABLE siparisler ADD INDEX idx_siparis_tarihi (siparis_tarihi);
ALTER TABLE siparisler ADD INDEX idx_durum_tarih (durum, siparis_tarihi);

-- Birleşik indeks sorgu planını kontrol etme
EXPLAIN SELECT * FROM siparisler 
WHERE durum = 'beklemede' 
  AND siparis_tarihi BETWEEN '2024-03-01' AND '2024-03-31';

-- Fonksiyon sarmalamadan kaçının - indeks kullanılmaz
-- YANLIŞ:
-- SELECT * FROM siparisler WHERE YEAR(siparis_tarihi) = 2024;

-- DOĞRU:
SELECT * FROM siparisler 
WHERE siparis_tarihi >= '2024-01-01' 
  AND siparis_tarihi < '2025-01-01';

-- EXPLAIN ile sorgu analizi
EXPLAIN FORMAT=JSON
SELECT DATE_FORMAT(siparis_tarihi, '%Y-%m') AS ay, COUNT(*) 
FROM siparisler 
WHERE siparis_tarihi >= '2024-01-01'
GROUP BY DATE_FORMAT(siparis_tarihi, '%Y-%m');

Sık yapılan hatalar ve çözümleri:

  • Fonksiyon wrap etme: WHERE YEAR(tarih) = 2024 yerine her zaman aralık karşılaştırması kullanın
  • String ile karşılaştırma: Tarih alanını asla string olarak karşılaştırmayın, implicit conversion performansı düşürür
  • BETWEEN ve saat: BETWEEN '2024-03-15' AND '2024-03-15' ile DATETIME alanını filtrelemeyin, sadece 00:00:00 saatini yakalar, gün sonunu kaçırırsınız
  • NULL karşılaştırması: WHERE iptal_tarihi = NULL yerine her zaman WHERE iptal_tarihi IS NULL kullanın

STR_TO_DATE ile Format Dönüşümü

Dışarıdan gelen verileri, özellikle CSV import veya API entegrasyonlarında, doğru formata dönüştürmek gerekiyor:

-- Farklı formatlardaki string'leri DATE'e çevirme
SELECT 
    STR_TO_DATE('15.03.2024', '%d.%m.%Y') AS tr_formatından,
    STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS slash_formatından,
    STR_TO_DATE('March 15, 2024', '%M %d, %Y') AS ingilizce_formatından,
    STR_TO_DATE('2024-03-15 14:30:00', '%Y-%m-%d %H:%i:%s') AS datetime_dönüşüm;

-- CSV import sırasında format dönüşümü
-- Diyelim ki dışarıdan 'dd.mm.yyyy' formatında tarihler geldi
UPDATE siparisler_temp
SET siparis_tarihi = STR_TO_DATE(siparis_tarihi_ham, '%d.%m.%Y')
WHERE siparis_tarihi_ham IS NOT NULL
  AND siparis_tarihi_ham != '';

-- Geçersiz tarihleri tespit etme
SELECT siparis_tarihi_ham
FROM siparisler_temp
WHERE STR_TO_DATE(siparis_tarihi_ham, '%d.%m.%Y') IS NULL
  AND siparis_tarihi_ham IS NOT NULL;

Sonuç

DATE ve DATETIME alanlarla çalışmak başta basit görünse de production ortamında işler kolayca karmaşıklaşabiliyor. Bu yazıda ele aldığımız konuları özetlersek:

  • Doğru veri tipini seçin: Saat dilimi bağımsızlığı gerekiyorsa DATETIME, otomatik UTC dönüşümü gerekiyorsa TIMESTAMP kullanın
  • Indeksi koruyun: WHERE koşulunda tarih alanlarına fonksiyon uygulamaktan kaçının, bunun yerine aralık sorguları yazın
  • NULL yönetimine dikkat edin: IS NULL / IS NOT NULL kullanın, COALESCE ve IFNULL ile güvenli sorgular yazın
  • FORMAT tutarlılığı: Her zaman YYYY-MM-DD formatında değer girin, başka formatlarda implicit conversion’a güvenmeyin
  • Saat dilimini netleştirin: Sunucu, uygulama ve veritabanı saat dilimlerinin tutarlı olduğundan emin olun, CONVERT_TZ fonksiyonunu tanıyın
  • EXPLAIN kullanın: Tarih bazlı sorgularınızın gerçekten indeks kullanıp kullanmadığını düzenli kontrol edin

Büyük tablolarla çalışırken tarih bazlı silme ve arşivleme işlemlerini her zaman LIMIT ile küçük parçalar halinde yapın. Tek seferde milyonlarca satırı silmeye çalışmak tabloyu kilitler ve production’ı etkiler. Küçük adımlarla, döngüsel bir yaklaşımla ilerlemek çok daha güvenli.

Herhangi bir konuda sorunuz olursa yorum bırakın, elimden geldiğince yardımcı olmaya çalışırım.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir