46 / 100

TIMESTAMP

Sana va vaqt turi

Kirish: Ushbu qo'llanmada siz PostgreSQL TIMESTAMP ma'lumot turi yordamida ma'lumotlar bazasida sana va vaqt qiymatlarini (vaqt mintaqasisiz) saqlashni o'rganasiz.

PostgreSQL TIMESTAMP ma'lumot turiga kirish

PostgreSQLda TIMESTAMP ma'lumot turi bir vaqtning o'zida ham sanani, ham vaqtni saqlash imkonini beradi. TIMESTAMP turi o'zida hech qanday vaqt mintaqasi (timezone) ma'lumotini saqlamaydi.

Bu shuni anglatadiki, PostgreSQL serverining vaqt mintaqasi o'zgarganda ham bazada saqlangan TIMESTAMP qiymatlari avtomatik ravishda o'zgarmaydi.

Agar siz sana va vaqtni mintaqa bilan bog'liq holda saqlamoqchi bo'lsangiz, TIMESTAMPTZ turidan foydalanishingiz kerak.

PostgreSQL TIMESTAMP qiymati uchun xotiradan 8 bayt joy ajratadi.

TIMESTAMP ustunini e'lon qilish sintaksisi:

query.sql

CREATE TABLE table_name (
  column_name TIMESTAMP
);

Standart qiymat sifatida joriy mahalliy sana va vaqtni o'rnatish uchun LOCALTIMESTAMP funksiyasidan foydalanishingiz mumkin:

query.sql

CREATE TABLE table_name (
  column_name TIMESTAMP DEFAULT LOCALTIMESTAMP
);

Kiritish va chiqarish formati

PostgreSQL sana va vaqt uchun xalqaro ISO 8601 (YYYY-MM-DD HH:MI:SS) standartidan foydalanadi (masalan: 2024-12-31 23:59:59).

PostgreSQL TIMESTAMP turiga misol

Mahalliy tadbirlar (spektakllar, kinolar) jadvalini saqlovchi events jadvalini yaratamiz:

query.sql

CREATE TABLE events (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  location VARCHAR(255) NOT NULL,
  start_at TIMESTAMP NOT NULL,
  end_at TIMESTAMP NOT NULL
);

Jadvalga bir nechta tadbir vaqtlarini kiritamiz:

query.sql

INSERT INTO
  events (title, location, start_at, end_at)
VALUES
  (
    '"O" by Cirque du Soleil',
    'O Theatre, Bellagio',
    '2024-12-08 19:00:00',
    '2024-12-08 21:30:00'
  ),
  (
    'David Copperfield',
    'David Copperfield Theater, MGM Grand',
    '2024-12-07 16:00:00',
    '2024-12-07 19:00:00'
  ),
  (
    'Awakening',
    'Awakening Theater, Wynn Las Vegas',
    '2024-12-06 19:00:00',
    '2024-12-06 21:00:00'
  ) 
RETURNING *;

Natija:

matn

id |          title          |               location               |      start_at       |       end_at
----+-------------------------+--------------------------------------+---------------------+---------------------
  1 | "O" by Cirque du Soleil | O Theatre, Bellagio                  | 2024-12-08 19:00:00 | 2024-12-08 21:30:00
  2 | David Copperfield       | David Copperfield Theater, MGM Grand | 2024-12-07 16:00:00 | 2024-12-07 19:00:00
  3 | Awakening               | Awakening Theater, Wynn Las Vegas    | 2024-12-06 19:00:00 | 2024-12-06 21:00:00

Tadbir davomiyligini hisoblash

Tugash vaqtidan (end_at) boshlanish vaqtini (start_at) ayirish orqali tadbirning davomiyligini (INTERVAL) hisoblash mumkin:

query.sql

SELECT
  title,
  end_at - start_at AS duration
FROM
  events;

Natija:

matn

          title          | duration
-------------------------+----------
 "O" by Cirque du Soleil | 02:30:00
 David Copperfield       | 03:00:00
 Awakening               | 02:00:00

Xulosa

Vaqt mintaqasiga bog'liq bo'lmagan sana va vaqtni saqlash uchun TIMESTAMP ma'lumot turidan foydalaning.
Ikkita TIMESTAMP o'rtasidagi ayirma oraliq vaqtni (INTERVAL) qaytaradi.

Oldingi

Keyingi