تایم‌استمپ‌های یونیکس در پایگاه‌داده‌ها — ذخیره‌سازی، کوئری‌کردن و بهترین روش‌ها

تقریباً هر اسکیما/ساختار دیتابیسی باید تاریخ و زمان را ذخیره کند. انتخاب معمولاً بین ذخیرهٔ یک تایم‌استمپ یونیکس (عدد صحیحِ ثانیه‌ها از 1970) و استفاده از نوع ستون datetime بومی دیتابیس است. هر دو کار می‌کنند. تفاوت‌ها زمانی خودش را نشان می‌دهد که می‌خواهید داده را کوئری کنید، مرتب کنید، مقایسه کنید و نمایش دهید — و مهم‌تر از همه وقتی با تایم‌زون‌ها درگیر می‌شوید.

مبدل تایم‌استمپ یونیکس برای تبدیل‌های تکی مناسب است. این مقاله بده‌بستان‌های سطح دیتابیس و این‌که دیتابیس‌های رایج هر رویکرد را چگونه مدیریت می‌کنند توضیح می‌دهد.

عدد صحیح در برابر ستون datetime بومی: بده‌بستان اصلی

ذخیره به‌صورت عدد صحیح (تایم‌استمپ یونیکس):

  • نوع ستون: INT یا BIGINT
  • ذخیره می‌کند: تعداد ثانیه‌ها (یا میلی‌ثانیه‌ها) از 1970-01-01 00:00:00 UTC
  • تایم‌زون: ندارد — عدد همیشه UTC است
  • محاسبات: ساده (created_at > NOW_UNIX - 86400 برای «24 ساعت اخیر»)
  • خوانایی: نیاز به تبدیل دارد (1714000000 → باید ببینید چه تاریخی است)

ذخیره به‌صورت datetime بومی:

  • نوع ستون: بسته به دیتابیس TIMESTAMP، TIMESTAMPTZ یا DATETIME
  • ذخیره می‌کند: نمایش ساخت‌یافتهٔ تاریخ و زمان
  • تایم‌زون: بسته به نوع و دیتابیس متفاوت است (پایین‌تر توضیح داده شده)
  • محاسبات: نیازمند توابع دیتابیس (DATEDIFF، EXTRACT، حساب با intervalها)
  • خوانایی: در خروجی کوئری انسان‌خوان است (2024-04-25 11:46:40)

هیچ‌کدام «همیشه درست» نیست. انتخاب به کیس استفاده، الگوی کوئری‌ها و این‌که تایم‌زون‌ها را در اپلیکیشن چطور مدیریت می‌کنید بستگی دارد.

PostgreSQL: TIMESTAMP در برابر TIMESTAMPTZ در برابر عدد

در PostgreSQL عملاً سه گزینهٔ رایج دارید:

TIMESTAMP WITHOUT TIME ZONE — دقیقاً همان چیزی را ذخیره می‌کند که وارد می‌کنید. هیچ تبدیل تایم‌زونی انجام نمی‌شود. اگر از یک سرور اپلیکیشن در نیویورک '2024-04-25 09:00:00' درج کنید، همان ذخیره می‌شود — حتی اگر در واقع معادل 13:00 UTC باشد. وقتی هم می‌خوانید، 2024-04-25 09:00:00 برمی‌گردد بدون هیچ کانتکست تایم‌زونی. برای سیستم‌هایی که تایم‌زون سرور ممکن است تغییر کند یا چند تایم‌زون دارند، خطرناک است.

TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) — همیشه به‌صورت داخلی UTC ذخیره می‌کند، مستقل از این‌که شما چه چیزی بدهید. هنگام نوشتن، PostgreSQL از تایم‌زون session به UTC تبدیل می‌کند. هنگام خواندن، برعکس، به تایم‌زون session برمی‌گرداند. این نوع برای اکثر کاربردهای تولیدی توصیه می‌شود. نامش کمی گمراه‌کننده است — تایم‌زون ذخیره نمی‌کند؛ UTC ذخیره می‌کند و تبدیل انجام می‌دهد.

-- Session timezone: America/New_York (UTC-4)
INSERT INTO events (created_at) VALUES ('2024-04-25 09:00:00');
-- Stored as: 2024-04-25 13:00:00 UTC

SELECT created_at FROM events;
-- Returns: 2024-04-25 09:00:00-04 (converted back to session timezone)

BIGINT (تایم‌استمپ یونیکس) — کاملاً مستقل از تایم‌زون است. تبدیل بر عهدهٔ اپلیکیشن است. وقتی می‌خواهید کلاً از مکانیزم تایم‌زون PostgreSQL عبور کنید و همه‌چیز را در لایهٔ اپ کنترل کنید، گزینهٔ مفیدی است.

-- Range query (last 7 days)
SELECT * FROM events WHERE created_at > EXTRACT(EPOCH FROM NOW())::bigint - 604800;

-- Convert to readable date
SELECT TO_TIMESTAMP(created_at) FROM events;

برای اکثر اپلیکیشن‌های جدید PostgreSQL: از TIMESTAMPTZ استفاده کنید. مدیریت تایم‌زون درست است، نوع خواناست و توابع تاریخ/زمان PostgreSQL مستقیم با آن کار می‌کنند.

MySQL: DATETIME در برابر TIMESTAMP در برابر INT

نام‌گذاری در MySQL گیج‌کننده است چون TIMESTAMP در MySQL با TIMESTAMPTZ در PostgreSQL یکی نیست.

DATETIME — تاریخ و زمانِ دقیقی را که می‌دهید ذخیره می‌کند. تبدیل تایم‌زونی ندارد. شبیه TIMESTAMP WITHOUT TIME ZONE در PostgreSQL. بازه: از 1000-01-01 تا 9999-12-31.

TIMESTAMP — داخلی UTC ذخیره می‌کند و هنگام خواندن/نوشتن با تایم‌زون اتصال (connection) تبدیل می‌کند. از نظر رفتار شبیه TIMESTAMPTZ است. اما بازه‌اش محدود است: از 1970-01-01 00:00:01 UTC تا 2038-01-19 03:14:07 UTC. این همان مشکل سال 2038 است — چون نوع ستون TIMESTAMP در MySQL داخلی از عدد 32‑بیتی استفاده می‌کند و در تاریخ overflow می‌کند.

INT یا BIGINT — مدیریت دستی تایم‌استمپ یونیکس. با BIGINT مشکل 2038 را دور می‌زنید و تا خیلی بعد از آن هم قابل ذخیره است.

-- 2038 problem with TIMESTAMP column
-- This will fail or be stored incorrectly:
INSERT INTO events (created_at) VALUES (FROM_UNIXTIME(2147483648)); -- Jan 19, 2038 + 1 second

-- INT/BIGINT approach avoids this
INSERT INTO events (created_at) VALUES (2147483648); -- stores fine
SELECT FROM_UNIXTIME(created_at) FROM events; -- converts for display

برای اپلیکیشن‌های MySQL که داده‌شان باید بعد از 2038 هم درست بماند: DATETIME یا BIGINT را به‌جای TIMESTAMP انتخاب کنید.

SQLite: همه‌چیز متن یا عدد است

SQLite نوع datetime بومی ندارد. تاریخ‌ها را به این شکل‌ها ذخیره می‌کند:

  • رشتهٔ متنی (فرمت ISO 8601 مثل '2024-04-25T11:46:40')
  • عدد صحیح (تایم‌استمپ یونیکس)
  • عدد اعشاری (شمارهٔ روز ژولیوسی)

توابع تاریخ SQLite با هر سه فرمت کار می‌کنند، اما داخلی همه‌چیز متن و عدد است. رویکرد عددی برای SQLite طبیعی است و ابهام را کم می‌کند:

-- Store as integer
CREATE TABLE events (created_at INTEGER);
INSERT INTO events (created_at) VALUES (strftime('%s', 'now'));

-- Query last 7 days
SELECT * FROM events WHERE created_at > strftime('%s', 'now') - 604800;

-- Display as human-readable
SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;

برای بسیاری از اپلیکیشن‌های SQLite، تایم‌استمپ یونیکسِ عددی معمولاً تمیزترین انتخاب است؛ هم چون SQLite آن را «طبیعی» می‌پذیرد و هم چون از سربارِ پارس‌کردن رشته‌های تاریخ جلوگیری می‌کند.

کوئری‌های رایج: عدد در برابر datetime

عملیاتعدد یونیکس (PostgreSQL)TIMESTAMPTZ (PostgreSQL)
رکوردهای 24 ساعت اخیرWHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 86400WHERE ts > NOW() - INTERVAL '24 hours'
رکوردهای 7 روز اخیرWHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 604800WHERE ts > NOW() - INTERVAL '7 days'
رکوردهای امروزWHERE ts >= date_trunc('day', NOW()) (اول باید تبدیل کنید)WHERE ts >= date_trunc('day', NOW())
گروه‌بندی بر اساس روزپیچیده‌تر — تبدیل و سپس truncateGROUP BY date_trunc('day', ts)
نمایش انسان‌خوانTO_TIMESTAMP(ts)ts (خودش خواناست)

در رویکرد عددی، برای عملیات‌هایی مثل truncate و گروه‌بندی بر اساس روز/هفته/ماه معمولاً باید تبدیل صریح انجام دهید. ستون‌های datetime بومی این کارها را در SQL تمیزتر انجام می‌دهند.

کدام را انتخاب کنیم؟ راهنمای تصمیم

از TIMESTAMPTZ (PostgreSQL) یا DATETIME (MySQL) استفاده کنید وقتی:

  • تیم شما زیاد SQL مستقیم می‌نویسد (کوئری‌ها خواناتر می‌شوند)
  • لازم دارید در کوئری‌ها بر اساس روز/هفته/ماه گروه‌بندی کنید
  • می‌خواهید از توابع زمانی بومی دیتابیس بدون سربار تبدیل استفاده کنید

از تایم‌استمپ یونیکسِ عددی استفاده کنید وقتی:

  • از MySQL استفاده می‌کنید و داده باید بعد از 2038 هم معتبر بماند
  • منطق تایم‌زون را کامل در لایهٔ اپلیکیشن مدیریت می‌کنید و می‌خواهید از تبدیل‌های دیتابیس عبور کنید
  • از SQLite استفاده می‌کنید
  • API می‌سازید که timestamp را به فرانت‌اند JavaScript می‌دهد (عددها بعد از مستندسازی «ثانیه vs میلی‌ثانیه» بدون ابهام‌اند)
  • حجم بسیار زیادی timestamp ذخیره می‌کنید و ذخیرهٔ فشرده‌تر می‌خواهید (مثلاً INT چهار بایتی در برابر نمایش datetime با اندازهٔ متفاوت)

از ترکیب دو رویکرد در یک اپلیکیشن پرهیز کنید. اینکه بعضی جدول‌ها datetime بومی داشته باشند و بعضی جدول‌ها تایم‌استمپ عددی، هم سربار تبدیل می‌آورد و هم برای هر توسعه‌دهنده هزینهٔ ذهنی دارد.

هر چیزی را که ذخیره می‌کنید، مستندسازی کنید: در تعریف ستون مشخص کنید UTC Unix seconds است یا UTC Unix milliseconds، یا datetime محلی. برای دیباگ و مهاجرت داده، از مبدل تایم‌استمپ یونیکس استفاده کنید تا هر مقدار مشخص را سریع راستی‌آزمایی کنید.

مقالات مرتبط

مشاهده مطالب بیشتر