44 / 100

DATE

Sana ma'lumot turi

Kirish: Ushbu qo'llanmada siz PostgreSQL DATE ma'lumot turi va undan jadvallarda sanalarni (vaqtsiz) saqlash uchun qanday foydalanishni o'rganasiz.

PostgreSQL DATE ma'lumot turiga kirish

PostgreSQLda faqat sanalarni (soat, daqiqa va soniyalarsiz) saqlash uchun DATE ma'lumot turidan foydalaniladi.

DATE turidagi ustunni yaratish sintaksisi:

query.sql

CREATE TABLE table_name (
  column_name DATE,
  ...
);

DATE ustuni miloddan avvalgi 4713-yildan to milodiy 5874897-yilgacha bo'lgan sanalarni saqlashi mumkin. PostgreSQL har bir sana qiymati uchun xotiradan 4 bayt joy ajratadi.

Sana ustuniga standart qiymat belgilash uchun DEFAULT cheklovidan foydalaniladi:

query.sql

CREATE TABLE table_name (
  column_name DATE DEFAULT default_date
);

Joriy sanani avtomatik ravishda standart qiymat qilib o'rnatish uchun CURRENT_DATE funksiyasidan foydalanishingiz mumkin:

query.sql

CREATE TABLE table_name (
  column_name DATE DEFAULT CURRENT_DATE
);

Sanani kiritish formati

PostgreSQL turli xil sana formatlarini qabul qiladi, lekin doimo xalqaro ISO 8601 (YYYY-MM-DD) formatidan foydalanish tavsiya etiladi. Masalan, 2024-12-31 2024-yil 31-dekabrni bildiradi.

PostgreSQL DATE turiga misol

Quyidagi misolda ombordagi tovarlar qoldig'ini o'zgartirish tarixini saqlovchi stock_adjustments jadvali yaratiladi:

query.sql

CREATE TABLE stock_adjustments (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  adjustment_date DATE NOT NULL DEFAULT CURRENT_DATE
);

Ushbu jadvaldagi adjustment_date ustuniga standart qiymat qilib bugungi sana (CURRENT_DATE) belgilangan.

Standart sana bilan qator qo'shish

Agar qator kiritishda sanani ko'rsatmasak, PostgreSQL avtomatik tarzda joriy sanani yozadi:

query.sql

INSERT INTO
  stock_adjustments (product_name, quantity)
VALUES
  ('iPhone 16 Pro', 5) 
RETURNING *;

Natija:

matn

id | product_name  | quantity | adjustment_date
----+---------------+----------+-----------------
  1 | iPhone 16 Pro |        5 | 2024-12-08

Muayyan sana bilan qator qo'shish

Qator kiritishda sanani o'zingiz aniq belgilashingiz ham mumkin:

query.sql

INSERT INTO
  stock_adjustments (product_name, quantity, adjustment_date)
VALUES
  ('Galaxy S23 Ultra', 2, '2024-12-09') 
RETURNING *;

Natija:

matn

id |   product_name   | quantity | adjustment_date
----+------------------+----------+-----------------
  2 | Galaxy S23 Ultra |        2 | 2024-12-09

Sanadan kun, oy va yilni ajratib olish (EXTRACT)

EXTRACT() funksiyasi sanadan kun, oy yoki yil kabi alohida qismlarni ajratib beradi:

query.sql

SELECT
  product_name,
  quantity,
  adjustment_date,
  extract(day from adjustment_date) AS d,
  extract(month from adjustment_date) AS m,
  extract(year from adjustment_date) AS y
FROM
  stock_adjustments
ORDER BY
  adjustment_date;

Natija:

matn

  product_name   | quantity | adjustment_date | d | m  |  y
------------------+----------+-----------------+---+----+------
 iPhone 16 Pro    |        5 | 2024-12-08      | 8 | 12 | 2024
 Galaxy S23 Ultra |        2 | 2024-12-09      | 9 | 12 | 2024

Sanani formatlash (TO_CHAR)

Sanani o'zingiz xohlagan matnli formatda aks ettirish uchun TO_CHAR() funksiyasidan foydalanasiz:

query.sql

SELECT
  product_name,
  quantity,
  adjustment_date,
  to_char(adjustment_date, 'Month dd, yyyy') AS formatted_date
FROM
  stock_adjustments
ORDER BY
  adjustment_date;

Natija:

matn

  product_name   | quantity | adjustment_date |   formatted_date
------------------+----------+-----------------+--------------------
 iPhone 16 Pro    |        5 | 2024-12-08      | December  08, 2024
 Galaxy S23 Ultra |        2 | 2024-12-09      | December  09, 2024

Sanaga kun qo'shish va ayirish

Sanaga kunlar sonini qo'shish uchun + operatoridan, ayirish uchun esa - operatoridan foydalaniladi:

query.sql

date + days
date - days

Masalan, sanaga 3 kun qo'shib tasdiqlash sanasini (approval_date) hisoblaymiz:

query.sql

SELECT
  product_name,
  quantity,
  adjustment_date,
  adjustment_date + 3 AS approval_date
FROM
  stock_adjustments
ORDER BY
  approval_date;

Natija:

matn

  product_name   | quantity | adjustment_date | approval_date
------------------+----------+-----------------+---------------
 iPhone 16 Pro    |        5 | 2024-12-08      | 2024-12-11
 Galaxy S23 Ultra |        2 | 2024-12-09      | 2024-12-12

Xulosa

Vaqtsiz sanalarni saqlash uchun PostgreSQL DATE ma'lumot turidan foydalaning.
Joriy sana uchun CURRENT_DATE dan foydalaning.
Sanadan alohida qismlarni olish uchun EXTRACT(), formatlash uchun esa TO_CHAR() funksiyasi ishlatiladi.
Sanalar ustida + va - operatorlari orqali kunlar hisob-kitobini oson bajarish mumkin.

Oldingi

Keyingi