TIME

Kirish: Ushbu qo'llanmada siz PostgreSQL TIME ma'lumot turi va undan jadvallarda vaqt qiymatlarini (sanasiz) saqlash uchun qanday foydalanishni o'rganasiz.

PostgreSQL TIME ma'lumot turiga kirish

PostgreSQLda faqat kunning vaqtini (sanalarsiz) saqlash uchun TIME ma'lumot turidan foydalaniladi.

TIME turidagi ustunni yaratish sintaksisi:

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

TIME ustuni 00:00:00 dan 24:00:00 gacha bo'lgan vaqt oralig'ini saqlay oladi. PostgreSQL bitta vaqt qiymatini saqlash uchun xotiradan 8 bayt joy ajratadi.

Standart qiymat belgilash uchun DEFAULT cheklovidan, joriy vaqtni avtomatik o'rnatish uchun esa CURRENT_TIME funksiyasidan foydalaniladi:

CREATE TABLE table_name (
  column_name TIME DEFAULT CURRENT_TIME
);

Vaqtni kiritish formati

Vaqt kiritishda xalqaro ISO 8601 (HH:MM:SS) formatidan foydalanish tavsiya etiladi (masalan: 09:30:45).

Soniyalar aniqligi (precision)

PostgreSQL soniyaning ulushlarini 6 xonagacha (mikrosoniyalar) aniqlikda saqlash imkonini beradi:

CREATE TABLE table_name (
  column_name TIME(precision)
);

Bu yerda precision 1 dan 6 gacha bo'lishi mumkin (HH:MI:SS.pppppp, masalan: 09:30:45.123456).

Joriy vaqtni olish funksiyalari

• CURRENT_TIME: vaqt mintaqasi va mikrosoniyalar bilan joriy vaqtni qaytaradi:

SELECT CURRENT_TIME;
-- 11:42:54.539691-07

Ulushlarsiz olish uchun:

SELECT CURRENT_TIME(0);
-- 11:43:07-07

• LOCALTIME: mintaqasiz mahalliy vaqtni qaytaradi:

SELECT LOCALTIME;
-- 11:43:18.576448

SELECT LOCALTIME(0);
-- 11:43:32

PostgreSQL TIME turiga misol

Ish jadvallarini saqlovchi schedules jadvalini yaratamiz:

CREATE TABLE schedules (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  employee VARCHAR(100) NOT NULL,
  start_at TIME NOT NULL,
  end_at TIME NOT NULL
);

Jadvalga vaqt qiymatlarini kiritamiz:

INSERT INTO
  schedules (employee, start_at, end_at)
VALUES
  ('John Doe', '08:00:00', '12:00:00'),
  ('John Doe', '13:00:00', '17:00:00') 
RETURNING *;

Natija:

id | employee | start_at |  end_at
----+----------+----------+----------
  1 | John Doe | 08:00:00 | 12:00:00
  2 | John Doe | 13:00:00 | 17:00:00

Vaqtdan soat, daqiqa va soniyani ajratib olish (EXTRACT)

EXTRACT() funksiyasi vaqtdan soat (hour), daqiqa (minute) va soniyalarni (second) ajratib beradi:

SELECT
  employee,
  start_at,
  extract(hour from start_at) AS h,
  extract(minute from start_at) AS m,
  extract(second from start_at)::INT AS s
FROM
  schedules;

Natija:

employee | start_at | h  | m | s
----------+----------+----+---+---
 John Doe | 08:00:00 |  8 | 0 | 0
 John Doe | 13:00:00 | 13 | 0 | 0

Xulosa

• Sanasiz vaqt qiymatlarini saqlash uchun PostgreSQL TIME ma'lumot turidan foydalaning.
• Joriy vaqt uchun CURRENT_TIME yoki LOCALTIME funksiyalaridan foydalaning.
• Soat, daqiqa va soniyalarni ajratib olish uchun EXTRACT() funksiyasi qo'llaniladi.