MySQL ve MariaDB’de STR_TO_DATE ile Metin Tarih Dönüşümü

Tarafındankomut
0 Yorum

Veritabanlarında tarih verileriyle çalışmak, sysadmin ve geliştirici hayatının kaçınılmaz bir parçası. Özellikle farklı kaynaklardan gelen verileri import ettiğinde, log dosyalarını işlediğinde ya da eski sistemlerden migration yaptığında tarihlerin metin olarak geldiğini görürsün. İşte tam bu noktada MariaDB ve MySQL’in STR_TO_DATE fonksiyonu devreye giriyor. Bu yazıda bu fonksiyonu her yönüyle ele alacak, gerçek dünya senaryolarıyla nasıl kullanacağını göstereceğim.

STR_TO_DATE Nedir ve Neden Kullanırız

STR_TO_DATE, adından da anlaşılacağı üzere bir metin stringini tarih veya tarih-saat tipine dönüştüren bir MySQL/MariaDB fonksiyonudur. Temel sözdizimi şu şekildedir:

STR_TO_DATE(string, format)

Burada string dönüştürmek istediğin metin değeri, format ise bu metnin hangi tarih formatında olduğunu tanımlayan format desenidir.

Peki neden buna ihtiyaç duyarız? Şöyle düşün: Bir CSV dosyasından 50.000 satır müşteri verisi import ediyorsun. Kaynak sistemde tarihler “15/03/2023” formatında tutulmuş. Ama senin veritabanında bu alan DATE tipinde. Direkt insert etmeye kalkarsan ya hata alırsın ya da NULL değerler yazılır. STR_TO_DATE burada hayat kurtarıcı oluyor.

Başka bir senaryo: Harici bir API’den gelen JSON verisinde tarihler “March 15, 2023” gibi İngilizce uzun formatta geliyor. Ya da legacy bir sistemden gelen dosyada “20230315” şeklinde yani tire veya slash olmadan bitişik yazılmış. Bunların hepsini düzgün tarih değerlerine çevirmek için STR_TO_DATE kullanmak en temiz yol.

Format Belirteçleri

Format parametresinde kullanabileceğin belirteçleri bilmek kritik. Bunları ezberlemeye gerek yok ama elimizin altında bulunması lazım:

  • %d: Gün, iki haneli (01-31)
  • %m: Ay, iki haneli (01-12)
  • %Y: Yıl, dört haneli (2023)
  • %y: Yıl, iki haneli (23)
  • %H: Saat, 24 saat formatında (00-23)
  • %h: Saat, 12 saat formatında (01-12)
  • %i: Dakika (00-59)
  • %s: Saniye (00-59)
  • %p: AM veya PM
  • %M: Ay adı, İngilizce tam (January, February…)
  • %b: Ay adı, İngilizce kısa (Jan, Feb…)
  • %W: Gün adı, tam (Monday, Tuesday…)
  • %a: Gün adı, kısa (Mon, Tue…)
  • %j: Yılın kaçıncı günü (001-366)
  • %T: Saat, HH:MM:SS formatında kısayol
  • %f: Mikrosaniye (000000-999999)

Şimdi bu bilgileri pratiğe dökelim.

Temel Kullanım Örnekleri

En basit kullanım senaryosundan başlayalım. Slash ile ayrılmış gün/ay/yıl formatı:

SELECT STR_TO_DATE('15/03/2023', '%d/%m/%Y');
-- Sonuç: 2023-03-15

Tire ile ayrılmış format:

SELECT STR_TO_DATE('2023-03-15', '%Y-%m-%d');
-- Sonuç: 2023-03-15

SELECT STR_TO_DATE('15-03-2023', '%d-%m-%Y');
-- Sonuç: 2023-03-15

Amerikan formatı, yani ay/gün/yıl sıralaması:

SELECT STR_TO_DATE('03/15/2023', '%m/%d/%Y');
-- Sonuç: 2023-03-15

Tarih ve saat birlikte:

SELECT STR_TO_DATE('15/03/2023 14:30:45', '%d/%m/%Y %H:%i:%s');
-- Sonuç: 2023-03-15 14:30:45

SELECT STR_TO_DATE('2023-03-15 02:30:00 PM', '%Y-%m-%d %h:%i:%s %p');
-- Sonuç: 2023-03-15 14:30:00

Gerçek Dünya Senaryosu 1: CSV Import Sonrası Veri Temizleme

Diyelim ki bir e-ticaret şirketinin veritabanını yönetiyorsun. Eski sistemden sipariş verilerini taşıdın ve tarihler VARCHAR alanında “DD.MM.YYYY” formatında duruyor. Şimdi bunları düzgün DATE alanına taşıman gerekiyor.

Önce mevcut durumu görelim:

-- Geçici tablo yapısı
CREATE TABLE orders_import (
    order_id INT,
    customer_name VARCHAR(100),
    order_date_text VARCHAR(20),  -- "15.03.2023" formatında
    amount DECIMAL(10,2)
);

-- Gerçek tablo
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    amount DECIMAL(10,2)
);

Şimdi dönüşüm sorgusunu yazalım:

INSERT INTO orders (order_id, customer_name, order_date, amount)
SELECT 
    order_id,
    customer_name,
    STR_TO_DATE(order_date_text, '%d.%m.%Y') AS order_date,
    amount
FROM orders_import
WHERE STR_TO_DATE(order_date_text, '%d.%m.%Y') IS NOT NULL;

Dikkat et: WHERE koşuluna IS NOT NULL ekledim. Bu sayede format uyumsuz olan, bozuk tarih verisi içeren satırlar ignore ediliyor ve hatalı dönüşüm yapılmıyor. Bu pratikte çok önemli bir güvenlik önlemi.

Kaç satırın dönüşemediğini görmek için:

SELECT COUNT(*) as donusemeyen_kayit
FROM orders_import
WHERE STR_TO_DATE(order_date_text, '%d.%m.%Y') IS NULL;

Gerçek Dünya Senaryosu 2: Log Dosyası Analizi

Apache veya Nginx log dosyalarını veritabanına aktardıktan sonra tarih bazlı sorgular yapmak istediğinde benzer bir sorunla karşılaşırsın. Apache log formatındaki tarihler “15/Mar/2023:14:30:45 +0300” gibi görünür.

-- Log tablosu
CREATE TABLE access_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_date_raw VARCHAR(50),
    ip_address VARCHAR(45),
    request_path VARCHAR(255),
    status_code INT
);

-- Log tarihini parse etme
SELECT 
    id,
    ip_address,
    STR_TO_DATE(
        SUBSTRING(log_date_raw, 1, 11),
        '%d/%b/%Y'
    ) AS log_date,
    status_code
FROM access_logs
WHERE STR_TO_DATE(
    SUBSTRING(log_date_raw, 1, 11),
    '%d/%b/%Y'
) BETWEEN '2023-03-01' AND '2023-03-31';

Burada %b formatı kısa ay adlarını (“Jan”, “Feb”, “Mar” gibi) parse ediyor. SUBSTRING ile de log_date_raw alanından sadece tarih kısmını kesip alıyoruz.

Gerçek Dünya Senaryosu 3: Farklı Formatları Normalize Etme

Bazen aynı tabloda birden fazla farklı format bulunabilir. Bu durum özellikle birden fazla kaynaktan veri birleştirmelerinde ortaya çıkar. CASE yapısıyla bunu ele alabiliriz:

SELECT 
    id,
    raw_date,
    CASE
        -- YYYY-MM-DD formatı
        WHEN raw_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
            THEN STR_TO_DATE(raw_date, '%Y-%m-%d')
        -- DD/MM/YYYY formatı
        WHEN raw_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'
            THEN STR_TO_DATE(raw_date, '%d/%m/%Y')
        -- DD.MM.YYYY formatı
        WHEN raw_date REGEXP '^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$'
            THEN STR_TO_DATE(raw_date, '%d.%m.%Y')
        -- YYYYMMDD bitişik format
        WHEN raw_date REGEXP '^[0-9]{8}$'
            THEN STR_TO_DATE(raw_date, '%Y%m%d')
        ELSE NULL
    END AS normalized_date
FROM mixed_date_table;

Bu sorgu, aynı alanda birden fazla format bulunsa bile hepsini otomatik olarak tanımlayıp dönüştürüyor. REGEXP ile format tespiti yapıp ardından uygun STR_TO_DATE çağrısını seçiyoruz.

UPDATE Sorgularında Kullanım

Mevcut bir tablodaki metin tarih kolonunu gerçek DATE kolonuna dönüştürmek için UPDATE kullanman gerekebilir:

-- Önce yeni kolon ekle
ALTER TABLE customers ADD COLUMN birth_date DATE;

-- Metin kolonunu dönüştürüp yeni kolona yaz
UPDATE customers
SET birth_date = STR_TO_DATE(birth_date_text, '%d/%m/%Y')
WHERE birth_date_text IS NOT NULL
  AND birth_date_text != ''
  AND STR_TO_DATE(birth_date_text, '%d/%m/%Y') IS NOT NULL;

-- Kaç kayıt güncellendi kontrol et
SELECT 
    COUNT(*) as toplam,
    SUM(CASE WHEN birth_date IS NOT NULL THEN 1 ELSE 0 END) as dönüstürülen,
    SUM(CASE WHEN birth_date IS NULL AND birth_date_text IS NOT NULL THEN 1 ELSE 0 END) as donusturülemeyen
FROM customers;

Büyük tablolarda bu UPDATE işlemini parçalara bölmek daha akıllıca olur. Tek seferde milyonlarca satırı güncellemek tabloya kilit koyar ve production sistemi etkiler:

-- Parçalı update, her seferinde 10000 satır
UPDATE customers
SET birth_date = STR_TO_DATE(birth_date_text, '%d/%m/%Y')
WHERE birth_date_text IS NOT NULL
  AND birth_date IS NULL
  AND STR_TO_DATE(birth_date_text, '%d/%m/%Y') IS NOT NULL
LIMIT 10000;

-- Bu sorguyu etkilenen satır 0 olana kadar tekrarla
-- Bash script ile otomatize edebilirsin:
-- while mysql -e "UPDATE ... LIMIT 10000" && [ $? -eq 0 ]; do sleep 0.1; done

Tarihe Göre Gruplama ve Raporlama

STR_TO_DATE ile sadece dönüşüm yapmakla kalmaz, sonuçları direkt sorgularda da kullanabilirsin. Örneğin aylık satış raporu:

SELECT 
    YEAR(STR_TO_DATE(sale_date_text, '%d/%m/%Y')) AS yil,
    MONTH(STR_TO_DATE(sale_date_text, '%d/%m/%Y')) AS ay,
    COUNT(*) AS siparis_adedi,
    SUM(amount) AS toplam_tutar
FROM sales_raw
WHERE STR_TO_DATE(sale_date_text, '%d/%m/%Y') IS NOT NULL
  AND STR_TO_DATE(sale_date_text, '%d/%m/%Y') >= '2023-01-01'
GROUP BY 
    YEAR(STR_TO_DATE(sale_date_text, '%d/%m/%Y')),
    MONTH(STR_TO_DATE(sale_date_text, '%d/%m/%Y'))
ORDER BY yil, ay;

Bu sorguyu sık kullanacaksan, performans açısından önce dönüşümü yapıp sonuçları saklamak çok daha verimli olur. Her sorguda STR_TO_DATE çalıştırmak gereksiz CPU tüketir.

Hata Yönetimi ve Sorun Giderme

STR_TO_DATE format uyumsuzluğunda NULL döner, exception fırlatmaz. Bu hem avantaj hem dezavantaj. Sessizce NULL döndüğü için bozuk verileri gözden kaçırabilirsin.

Bozuk verileri tespit etmek için şu sorguyu kullan:

SELECT 
    id,
    raw_date_field,
    STR_TO_DATE(raw_date_field, '%d/%m/%Y') AS converted,
    CASE 
        WHEN raw_date_field IS NULL THEN 'BOŞ DEĞER'
        WHEN raw_date_field = '' THEN 'BOŞ STRING'
        WHEN STR_TO_DATE(raw_date_field, '%d/%m/%Y') IS NULL THEN 'FORMAT HATASI'
        ELSE 'BAŞARILI'
    END AS durum
FROM problem_table
WHERE STR_TO_DATE(raw_date_field, '%d/%m/%Y') IS NULL
ORDER BY durum;

Sık karşılaşılan sorunlar ve çözümleri:

  • Yanlış sıra: “03/15/2023” için %d/%m/%Y kullandıysan 15. ay olmaz, NULL döner. Amerikan formatıysa %m/%d/%Y kullan.
  • İki haneli yıl: “15/03/23” için %d/%m/%y (küçük y) kullan. MySQL/MariaDB 70’den küçük değerleri 2000’li, büyük değerleri 1900’lü yıl olarak yorumlar.
  • Fazladan boşluk: Bazı import araçları başa veya sona boşluk bırakır. TRIM(raw_date_field) ile bu sorunu çöz.
  • Türkçe ay adları: STR_TO_DATE varsayılan olarak İngilizce ay adlarını tanır. Türkçe ay adları için önce REPLACE ile İngilizceye çevirmen gerekir.

Türkçe ay adı durumu biraz can sıkıcı ama çözülmesi zor değil:

SELECT STR_TO_DATE(
    REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(
                REPLACE(REPLACE(REPLACE(
                    raw_date, 'Ocak', 'January'),
                'Şubat', 'February'),
            'Mart', 'March'),
        'Nisan', 'April'),
    'Mayıs', 'May'),
    'Haziran', 'June'),
    'Temmuz', 'July'),
    'Ağustos', 'August'),
    'Eylül', 'September'),
    'Ekim', 'October'),
    'Kasım', 'November'),
    'Aralık', 'December'),
    '%d %M %Y'
) AS converted_date
FROM turkish_dates;

Evet, iç içe REPLACE biraz çirkin görünüyor. Bunu saklı yordam (stored procedure) veya fonksiyon olarak tanımlayıp daha temiz bir şekilde kullanmak daha iyi bir pratik.

Stored Function ile Tekrar Kullanılabilir Dönüşüm

Eğer aynı dönüşümü birçok yerde kullanıyorsan, bir fonksiyon yazmak hayatını kolaylaştırır:

DELIMITER //

CREATE FUNCTION parse_tr_date(tr_date VARCHAR(50))
RETURNS DATE
DETERMINISTIC
BEGIN
    DECLARE en_date VARCHAR(50);
    SET en_date = tr_date;
    SET en_date = REPLACE(en_date, 'Ocak', 'January');
    SET en_date = REPLACE(en_date, 'Şubat', 'February');
    SET en_date = REPLACE(en_date, 'Mart', 'March');
    SET en_date = REPLACE(en_date, 'Nisan', 'April');
    SET en_date = REPLACE(en_date, 'Mayıs', 'May');
    SET en_date = REPLACE(en_date, 'Haziran', 'June');
    SET en_date = REPLACE(en_date, 'Temmuz', 'July');
    SET en_date = REPLACE(en_date, 'Ağustos', 'August');
    SET en_date = REPLACE(en_date, 'Eylül', 'September');
    SET en_date = REPLACE(en_date, 'Ekim', 'October');
    SET en_date = REPLACE(en_date, 'Kasım', 'November');
    SET en_date = REPLACE(en_date, 'Aralık', 'December');
    RETURN STR_TO_DATE(TRIM(en_date), '%d %M %Y');
END //

DELIMITER ;

-- Kullanımı
SELECT parse_tr_date('15 Mart 2023');
-- Sonuç: 2023-03-15

STR_TO_DATE ile Birlikte Kullanılan Fonksiyonlar

STR_TO_DATE genellikle tek başına değil, diğer tarih fonksiyonlarıyla birlikte kullanılır:

-- Tarih farkı hesaplama
SELECT 
    customer_name,
    STR_TO_DATE(registration_date_text, '%d/%m/%Y') AS reg_date,
    DATEDIFF(CURDATE(), STR_TO_DATE(registration_date_text, '%d/%m/%Y')) AS gun_sayisi
FROM customers
WHERE STR_TO_DATE(registration_date_text, '%d/%m/%Y') IS NOT NULL
ORDER BY gun_sayisi DESC;

-- Belirli bir tarih aralığında kayıt sayısı
SELECT COUNT(*) 
FROM events
WHERE STR_TO_DATE(event_date_text, '%Y%m%d') 
      BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE();

-- Hafta günü bazlı analiz
SELECT 
    DAYNAME(STR_TO_DATE(sale_date, '%d/%m/%Y')) AS hafta_gunu,
    COUNT(*) AS islem_adedi,
    SUM(amount) AS toplam
FROM sales
GROUP BY DAYOFWEEK(STR_TO_DATE(sale_date, '%d/%m/%Y')),
         DAYNAME(STR_TO_DATE(sale_date, '%d/%m/%Y'))
ORDER BY DAYOFWEEK(STR_TO_DATE(sale_date, '%d/%m/%Y'));

Performans Notları

Production ortamında büyük tablolarla çalışırken dikkat etmen gereken birkaç nokta var:

  • Index kullanamaz: WHERE koşulunda STR_TO_DATE(kolon, format) kullandığında bu kolondaki index devre dışı kalır. Fonksiyon tabanlı index (MariaDB 10.3.2+) tanımlanabilir ama bu ayrı bir konu.
  • Computed column: Eğer bu dönüşümü sık yapıyorsan, ayrı bir DATE kolonu ekleyip oraya dönüştürülmüş değeri yazman ve sorguları o kolon üzerinden yapman çok daha hızlı olur.
  • View kullanımı: Geçici çözüm olarak view oluşturabilirsin. Sorgular temizleşir ama performans avantajı sağlamaz.
  • Batch import: Büyük hacimli veri import ederken LOAD DATA INFILE komutunu STR_TO_DATE ile birlikte kullanabilirsin. Bu, satır satır INSERT’ten çok daha hızlıdır.

Sonuç

STR_TO_DATE basit görünen ama veri yönetiminde kritik bir rol oynayan bir fonksiyon. Özellikle veri migration işlemlerinde, farklı sistemlerden gelen verilerin normalize edilmesinde ve legacy veritabanı temizleme süreçlerinde sıklıkla ihtiyacın olacak.

Önemli noktalara bir daha değinelim: Format belirteçlerini doğru seçmek işin yarısı. Dönüşüm başarısız olduğunda NULL döner, exception yoktur, bu yüzden her zaman NULL kontrolü yapmalısın. Büyük tablolarda doğrudan WHERE içinde çağırmak performans sorunlarına yol açar, mümkünse dönüştürülmüş değeri ayrı bir kolonda tut. Türkçe ay adları gibi locale-spesifik durumları ön dönüşümle halletmek zorundasın.

Veritabanı yönetiminin rutinleri arasında tarih dönüşümleri belki glamorlu görünmüyor, ama doğru yapılmadığında session’ların saatlerini çalan, raporları mahveden sessiz bir baş belası haline gelir. STR_TO_DATE bu sorunun en temiz çözümü ve araç kutusunda her zaman hazır bulunmalı.

Bir yanıt yazın

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