Date funksiyalar — NOW, DATE_FORMAT, DATEDIFF, DATE_ADD

~20 daq · MySQL · Funksiyalar

Muammodan boshlaylik

Deyarli har bir hisobot vaqtga bog'liq:

• Bugungi buyurtmalar
• O'tgan haftadagi tushum, oyma-oy
• 30 kundan beri faol bo'lmagan mijozlar
• Bugun tug'ilgan kuni bo'lgan mijozlar
• To'lanmagan buyurtma 3 kundan keyin avtomatik bekor bo'lsin

Sana va vaqt bilan to'g'ri ishlash — SQL ning eng amaliy ko'nikmalaridan biri.

Hozirgi vaqt

SELECT NOW();            -- 2026-11-04 14:30:15  — sana va vaqt
SELECT CURDATE();        -- 2026-11-04           — faqat sana
SELECT CURTIME();        -- 14:30:15             — faqat vaqt
SELECT UTC_TIMESTAMP();  -- 2026-11-04 09:30:15  — UTC da

NOW() — sessiya vaqt mintaqasida:

SELECT @@global.time_zone, @@session.time_zone;   -- SYSTEM, SYSTEM
SET time_zone = '+05:00';                         -- Toshkent

[!TIP] Ko'p loyihalarda qoida: server va baza — UTC, foydalanuvchiga ko'rsatishda — uning mintaqasiga o'giriladi (ilovada). Shunda server boshqa mamlakatga ko'chirilsa ham vaqtlar buzilmaydi.

Qismlarga ajratish

SELECT
    YEAR(yaratilgan)      AS yil,      -- 2026
    MONTH(yaratilgan)     AS oy,       -- 10
    DAY(yaratilgan)       AS kun,      -- 12
    HOUR(yaratilgan)      AS soat,     -- 18
    DAYOFWEEK(yaratilgan) AS hafta_kuni,   -- 1=yakshanba ... 7=shanba
    WEEKDAY(yaratilgan)   AS hafta_kuni2,  -- 0=dushanba ... 6=yakshanba
    QUARTER(yaratilgan)   AS chorak,   -- 4
    DATE(yaratilgan)      AS sana      -- 2026-10-12
FROM buyurtmalar WHERE id = 2;

Qaysi soatlarda ko'p buyurtma beriladi?

SELECT HOUR(yaratilgan) AS soat, COUNT(*) AS buyurtmalar
FROM buyurtmalar
GROUP BY soat
ORDER BY soat;

Reklamani qachon ishga tushirish kerakligini shu ko'rsatadi.

Formatlash: DATE_FORMAT

SELECT DATE_FORMAT(yaratilgan, '%d.%m.%Y %H:%i') FROM buyurtmalar;
-- 12.10.2026 18:40

Oylik guruhlash uchun:

SELECT DATE_FORMAT(yaratilgan, '%Y-%m') AS oy, COUNT(*)
FROM buyurtmalar
GROUP BY oy;

O'zbekcha oy nomlari uchun — ELT:

SELECT ELT(MONTH(yaratilgan),
    'yanvar','fevral','mart','aprel','may','iyun',
    'iyul','avgust','sentabr','oktabr','noyabr','dekabr') AS oy
FROM buyurtmalar;

Matndan sanaga — STR_TO_DATE:

SELECT STR_TO_DATE('12.10.2026', '%d.%m.%Y');   -- 2026-10-12

Excel dan kelgan dd.mm.yyyy formatidagi sanalarni import qilishda kerak.

Qo'shish va ayirish

SELECT NOW() + INTERVAL 3 DAY;
SELECT NOW() - INTERVAL 1 MONTH;
SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH);   -- 2026-02-28 (oy oxiri moslashadi)
SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

INTERVAL birliklari: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.

Amaliy misollar

-- Oxirgi 7 kundagi buyurtmalar
SELECT * FROM buyurtmalar WHERE yaratilgan >= NOW() - INTERVAL 7 DAY;

-- 3 kundan beri to'lanmagan buyurtmalarni bekor qilish
UPDATE buyurtmalar
SET holat = 'bekor'
WHERE holat = 'yangi' AND yaratilgan < NOW() - INTERVAL 3 DAY;

-- Yetkazib berish sanasi: 2 ish kuni (oddiy variant)
SELECT CURDATE() + INTERVAL 2 DAY;

Farq: DATEDIFF, TIMESTAMPDIFF

SELECT DATEDIFF('2026-12-31', '2026-11-04');   -- 57 — kunlar

DATEDIFF faqat kunlarni beradi. Boshqa birliklar — TIMESTAMPDIFF(birlik, boshi, oxiri):

SELECT TIMESTAMPDIFF(HOUR,  '2026-11-01 10:00', '2026-11-04 14:30');   -- 76
SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-11-04');               -- 9
SELECT TIMESTAMPDIFF(YEAR,  '1998-07-21', CURDATE());                  -- 28 — yosh

-- Mijozlar: oxirgi buyurtmadan beri necha kun
SELECT m.ism, DATEDIFF(CURDATE(), MAX(b.yaratilgan)) AS kun_oldin
FROM mijozlar m
JOIN buyurtmalar b ON b.mijoz_id = m.id
GROUP BY m.id, m.ism
HAVING kun_oldin > 30;   -- "uxlab qolgan" mijozlar

-- Buyurtmadan yetkazishgacha o'rtacha vaqt (soatlarda)
SELECT AVG(TIMESTAMPDIFF(HOUR, yaratilgan, yetkazilgan)) FROM buyurtmalar
WHERE yetkazilgan IS NOT NULL;

Davr boshlari va oxirlari

SELECT LAST_DAY('2026-02-10');                         -- 2026-02-28
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01');             -- joriy oyning 1-kuni
SELECT CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY;    -- joriy haftaning dushanbasi

-- Joriy oydagi buyurtmalar
SELECT COUNT(*) FROM buyurtmalar
WHERE yaratilgan >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND yaratilgan <  DATE_FORMAT(CURDATE(), '%Y-%m-01') + INTERVAL 1 MONTH;

Muhim: funksiyani ustunga qo'ymang

Bugungi buyurtmalar:

-- ❌ Indeks ishlamaydi — har bir qator uchun DATE() hisoblanadi
SELECT * FROM buyurtmalar WHERE DATE(yaratilgan) = CURDATE();

-- ❌ Indeks ishlamaydi
SELECT * FROM buyurtmalar WHERE YEAR(yaratilgan) = 2026 AND MONTH(yaratilgan) = 10;

-- ✅ Oraliq — indeks ishlaydi
SELECT * FROM buyurtmalar
WHERE yaratilgan >= CURDATE() AND yaratilgan < CURDATE() + INTERVAL 1 DAY;

-- ✅
SELECT * FROM buyurtmalar
WHERE yaratilgan >= '2026-10-01' AND yaratilgan < '2026-11-01';

Natija bir xil, lekin million qatorli jadvalda farq — soniyalar va millisekundlar. Funksiyani ustunga emas, solishtirilayotgan qiymatga qo'llang. Bu "sargable" so'rov deyiladi (34-dars).

Tug'ilgan kunlar

-- Bugun tug'ilgan kuni bo'lganlar
SELECT ism FROM mijozlar
WHERE MONTH(tugilgan_kun) = MONTH(CURDATE())
  AND DAY(tugilgan_kun) = DAY(CURDATE());

-- Kelgusi 7 kun ichida
SELECT ism, tugilgan_kun FROM mijozlar
WHERE DATE_FORMAT(tugilgan_kun, '%m%d')
      BETWEEN DATE_FORMAT(CURDATE(), '%m%d')
          AND DATE_FORMAT(CURDATE() + INTERVAL 7 DAY, '%m%d');

Bu yerda funksiya ustunda — lekin boshqa yo'l yo'q va tug'ilgan kun so'rovlari kam bajariladi. (Dekabr oxirida yil almashinuvini alohida hisobga olish kerak.)

Nima uchun shunday?

Nima uchun DATE(yaratilgan) = CURDATE() indeksdan foydalana olmaydi?

Indeks — yaratilgan qiymatlarining tartiblangan ro'yxati (telefon kitobi kabi). MySQL undan "2026-11-04 00:00 dan 2026-11-05 00:00 gacha" oralig'ini bir zumda topa oladi. Lekin DATE(yaratilgan) qiymatlari indeksda yo'q — ular hisoblanishi kerak. Telefon kitobidan "familiyasining ikkinchi harfi 'a' bo'lganlar" ni qidirish kabi: kitob familiya bo'yicha tartiblangan, lekin bu savol uchun hamma sahifani varaqlash kerak.

Xulosa

Eslab qoling:

• NOW(), CURDATE(), UTC_TIMESTAMP() — hozirgi vaqt; mintaqaga e'tibor bering
• YEAR, MONTH, DAY, HOUR, WEEKDAY — qismlar
• DATE_FORMAT(d, '%d.%m.%Y') — ko'rsatish; STR_TO_DATE — matndan
• + INTERVAL n DAY / DATE_ADD / DATE_SUB — qo'shish/ayirish
• DATEDIFF — kunlar; TIMESTAMPDIFF(birlik, a, b) — istalgan birlik
• LAST_DAY — oy oxiri
• WHERE da ustunni funksiyaga o'ramang — >= boshi AND < oxiri oralig'ini ishlating

Keyingi dars

28-dars: Conditional — IF, CASE WHEN, COALESCE, NULLIF, IFNULL