دوال التاريخ والوقت DateTime Functions

-- MySQL
SELECT NOW();              -- 2025-01-15 14:30:45
SELECT CURRENT_TIMESTAMP;  -- 2025-01-15 14:30:45
SELECT CURRENT_TIMESTAMP();-- 2025-01-15 14:30:45
SELECT CURDATE();          -- 2025-01-15
SELECT CURRENT_DATE;       -- 2025-01-15
SELECT CURTIME();          -- 14:30:45
SELECT CURRENT_TIME;       -- 14:30:45

-- PostgreSQL
SELECT NOW();              -- 2025-01-15 14:30:45.123456+03
SELECT CURRENT_TIMESTAMP;  -- 2025-01-15 14:30:45.123456+03
SELECT CURRENT_DATE;       -- 2025-01-15
SELECT CURRENT_TIME;       -- 14:30:45.123456+03

-- SQL Server
SELECT GETDATE();          -- 2025-01-15 14:30:45.123
SELECT SYSDATETIME();      -- 2025-01-15 14:30:45.1234567
SELECT GETUTCDATE();       -- 2025-01-15 11:30:45.123 (UTC)

-- Oracle
SELECT SYSDATE FROM dual;  -- 15-JAN-25
SELECT SYSTIMESTAMP FROM dual;

-- طلبات اليوم
SELECT * FROM orders
WHERE DATE(order_date) = CURDATE();

-- طلبات الأسبوع الحالي
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- إدخال سجل جديد بتاريخ حالي
INSERT INTO logs (action, created_at)
VALUES ('تسجيل دخول', NOW());

-- تحديث تاريخ آخر تعديل
UPDATE products
SET updated_at = NOW()
WHERE product_id = 1;

-- MySQL - دوال مخصصة
SELECT
    order_date,
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    DAY(order_date) AS day,
    HOUR(order_date) AS hour,
    MINUTE(order_date) AS minute,
    SECOND(order_date) AS second
FROM orders;

-- EXTRACT (معيارية - تعمل في معظم القواعد)
SELECT
    order_date,
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day,
    EXTRACT(HOUR FROM order_date) AS hour,
    EXTRACT(MINUTE FROM order_date) AS minute,
    EXTRACT(SECOND FROM order_date) AS second
FROM orders;

-- MySQL
SELECT
    order_date,
    DAYOFWEEK(order_date) AS day_of_week,     -- 1=الأحد, 7=السبت
    WEEKDAY(order_date) AS weekday,            -- 0=الاثنين, 6=الأحد
    DAYOFYEAR(order_date) AS day_of_year,     -- 1-366
    WEEK(order_date) AS week_number,           -- رقم الأسبوع
    QUARTER(order_date) AS quarter,            -- الربع (1-4)
    DAYNAME(order_date) AS day_name,           -- Sunday, Monday...
    MONTHNAME(order_date) AS month_name        -- January, February...
FROM orders;

-- PostgreSQL
SELECT
    order_date,
    EXTRACT(DOW FROM order_date) AS day_of_week,  -- 0=الأحد
    EXTRACT(DOY FROM order_date) AS day_of_year,
    EXTRACT(WEEK FROM order_date) AS week_number,
    EXTRACT(QUARTER FROM order_date) AS quarter,
    TO_CHAR(order_date, 'Day') AS day_name,
    TO_CHAR(order_date, 'Month') AS month_name
FROM orders;

-- مبيعات حسب الشهر
SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

-- مبيعات حسب الربع
SELECT
    YEAR(order_date) AS year,
    QUARTER(order_date) AS quarter,
    SUM(total_amount) AS quarterly_sales
FROM orders
GROUP BY YEAR(order_date), QUARTER(order_date);

-- مبيعات حسب يوم الأسبوع
SELECT
    DAYNAME(order_date) AS day_name,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_sales
FROM orders
GROUP BY DAYOFWEEK(order_date), DAYNAME(order_date)
ORDER BY DAYOFWEEK(order_date);

-- MySQL: DATE_ADD / DATE_SUB
SELECT
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date,
    DATE_ADD(order_date, INTERVAL 1 MONTH) AS next_month,
    DATE_ADD(order_date, INTERVAL 1 YEAR) AS next_year,
    DATE_SUB(order_date, INTERVAL 30 DAY) AS thirty_days_ago
FROM orders;

-- وحدات INTERVAL المتاحة:
-- SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
-- SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND
-- HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE
-- DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR
-- YEAR_MONTH

-- أمثلة إضافية
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);           -- بعد ساعتين
SELECT DATE_ADD(NOW(), INTERVAL '1:30' HOUR_MINUTE); -- بعد ساعة ونصف
SELECT DATE_ADD(NOW(), INTERVAL '1-6' YEAR_MONTH);   -- بعد سنة و 6 أشهر

-- MySQL: DATEDIFF (الفرق بالأيام)
SELECT DATEDIFF('2025-12-31', '2025-01-01');  -- 364

-- TIMESTAMPDIFF (الفرق بوحدة محددة)
SELECT
    order_date,
    ship_date,
    TIMESTAMPDIFF(DAY, order_date, ship_date) AS days_to_ship,
    TIMESTAMPDIFF(HOUR, order_date, ship_date) AS hours_to_ship
FROM orders;

-- حساب عمر العميل
SELECT
    customer_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM customers;

-- PostgreSQL: طرح مباشر
SELECT
    ship_date - order_date AS days_diff,
    AGE(ship_date, order_date) AS age_diff
FROM orders;

-- SQL Server: DATEDIFF
SELECT DATEDIFF(day, order_date, ship_date) AS days_diff
FROM orders;

-- تاريخ انتهاء الاشتراك (بعد سنة)
SELECT
    user_id,
    subscription_date,
    DATE_ADD(subscription_date, INTERVAL 1 YEAR) AS expiry_date
FROM subscriptions;

-- الاشتراكات المنتهية قريباً (خلال 30 يوم)
SELECT * FROM subscriptions
WHERE DATE_ADD(subscription_date, INTERVAL 1 YEAR)
      BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);

-- متوسط وقت التوصيل
SELECT
    AVG(DATEDIFF(ship_date, order_date)) AS avg_delivery_days
FROM orders
WHERE ship_date IS NOT NULL;

-- الطلبات المتأخرة (أكثر من 5 أيام)
SELECT *
FROM orders
WHERE DATEDIFF(ship_date, order_date) > 5
   OR (ship_date IS NULL AND DATEDIFF(CURDATE(), order_date) > 5);

-- MySQL: DATE_FORMAT
SELECT
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_date,
    DATE_FORMAT(order_date, '%d/%m/%Y') AS arabic_date,
    DATE_FORMAT(order_date, '%W, %M %d, %Y') AS full_date,
    DATE_FORMAT(order_date, '%H:%i:%s') AS time_only,
    DATE_FORMAT(order_date, '%Y-%m') AS year_month
FROM orders;

-- رموز التنسيق الشائعة:
-- %Y = السنة (4 أرقام)    %y = السنة (رقمين)
-- %M = اسم الشهر كامل     %m = رقم الشهر (01-12)
-- %b = اسم الشهر مختصر    %c = رقم الشهر (1-12)
-- %d = اليوم (01-31)      %e = اليوم (1-31)
-- %W = اسم اليوم كامل     %a = اسم اليوم مختصر
-- %H = الساعة (00-23)     %h = الساعة (01-12)
-- %i = الدقائق (00-59)    %s = الثواني (00-59)
-- %p = AM/PM

-- PostgreSQL: TO_CHAR
SELECT
    order_date,
    TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_date,
    TO_CHAR(order_date, 'DD/MM/YYYY') AS arabic_date,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS full_date,
    TO_CHAR(order_date, 'HH24:MI:SS') AS time_only
FROM orders;

-- SQL Server: FORMAT
SELECT
    order_date,
    FORMAT(order_date, 'yyyy-MM-dd') AS iso_date,
    FORMAT(order_date, 'dd/MM/yyyy') AS arabic_date,
    FORMAT(order_date, 'dddd, MMMM dd, yyyy') AS full_date
FROM orders;

-- Oracle: TO_CHAR
SELECT
    order_date,
    TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_date,
    TO_CHAR(order_date, 'DD/MM/YYYY') AS arabic_date
FROM orders;

-- MySQL: تحويل المنطقة الزمنية
SELECT
    NOW() AS server_time,
    CONVERT_TZ(NOW(), 'UTC', 'Asia/Riyadh') AS riyadh_time,
    CONVERT_TZ(NOW(), 'UTC', 'America/New_York') AS new_york_time;

-- عرض المنطقة الزمنية الحالية
SELECT @@global.time_zone, @@session.time_zone;

-- PostgreSQL
SELECT
    NOW() AS server_time,
    NOW() AT TIME ZONE 'UTC' AS utc_time,
    NOW() AT TIME ZONE 'Asia/Riyadh' AS riyadh_time;

-- تخزين التوقيت بـ UTC وعرضه محلياً
SELECT
    created_at,
    CONVERT_TZ(created_at, 'UTC', 'Asia/Riyadh') AS local_time
FROM events;

SELECT
    DATE(order_date) AS date,
    DAYNAME(order_date) AS day_name,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_sales,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(order_date), DAYNAME(order_date)
ORDER BY date DESC;

SELECT
    'هذا الشهر' AS period,
    COUNT(*) AS orders,
    SUM(total_amount) AS sales
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
  AND MONTH(order_date) = MONTH(CURDATE())

UNION ALL

SELECT
    'الشهر الماضي' AS period,
    COUNT(*) AS orders,
    SUM(total_amount) AS sales
FROM orders
WHERE order_date >= DATE_SUB(
    DATE_FORMAT(CURDATE(), '%Y-%m-01'),
    INTERVAL 1 MONTH
)
AND order_date < DATE_FORMAT(CURDATE(), '%Y-%m-01');

SELECT
    c.customer_id,
    c.customer_name,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(CURDATE(), MAX(o.order_date)) AS days_since_last_order,
    CASE
        WHEN MAX(o.order_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 'نشط'
        WHEN MAX(o.order_date) >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) THEN 'متوسط'
        WHEN MAX(o.order_date) >= DATE_SUB(CURDATE(), INTERVAL 180 DAY) THEN 'خامل'
        ELSE 'مفقود'
    END AS customer_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- إنشاء جدول تقويم مؤقت
WITH RECURSIVE calendar AS (
    SELECT DATE('2025-01-01') AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM calendar
    WHERE date < '2025-12-31'
)
SELECT
    cal.date,
    DAYNAME(cal.date) AS day_name,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS daily_sales
FROM calendar cal
LEFT JOIN orders o ON DATE(o.order_date) = cal.date
GROUP BY cal.date
ORDER BY cal.date;

SELECT
    HOUR(order_date) AS hour,
    COUNT(*) AS order_count,
    SUM(total_amount) AS hourly_sales,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS percentage
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY HOUR(order_date)
ORDER BY hour;

SELECT
    user_id,
    plan_name,
    start_date,
    DATE_ADD(start_date, INTERVAL duration_months MONTH) AS end_date,
    DATEDIFF(
        DATE_ADD(start_date, INTERVAL duration_months MONTH),
        CURDATE()
    ) AS days_remaining,
    CASE
        WHEN DATE_ADD(start_date, INTERVAL duration_months MONTH) < CURDATE()
            THEN 'منتهي'
        WHEN DATE_ADD(start_date, INTERVAL duration_months MONTH)
             <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
            THEN 'ينتهي قريباً'
        ELSE 'نشط'
    END AS status
FROM subscriptions;

-- MySQL: التحقق من تاريخ صحيح
SELECT
    date_string,
    STR_TO_DATE(date_string, '%Y-%m-%d') AS parsed_date,
    CASE
        WHEN STR_TO_DATE(date_string, '%Y-%m-%d') IS NULL
        THEN 'تاريخ غير صحيح'
        ELSE 'تاريخ صحيح'
    END AS validation
FROM raw_data;

-- البحث عن تواريخ مستقبلية غير منطقية
SELECT * FROM orders
WHERE order_date > NOW();

-- البحث عن تواريخ ميلاد غير منطقية
SELECT * FROM customers
WHERE birth_date > CURDATE()
   OR TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) > 120;

-- التحقق من تواريخ NULL
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_dates,
    SUM(CASE WHEN ship_date IS NULL THEN 1 ELSE 0 END) AS null_ship_dates
FROM orders;