قبل از شروع بهتره اول انواع مختلف از روش‌های ذخیره‌سازی زمان تو دیتابیس رو با هم بررسی کنیم.
در مجموع ۵ روش ذخیره‌سازی وجود داره که اطلاعات هر کدومشون رو تو عکس پایین می تونید ببیند:
با مثال میریم جلو، فرض کنید یه جدول داریم به اسم test_date و ۲ تا فیلد داره به اسم‌های timestamp,datetime که کد ساخت این جدول به شکل زیر میشه:
create TABLE test_date ( `timestamp` timestamp null, `datetime` datetime null );
حالا یه تاریخ مشخص رو تو هر دو تا فیلد ذخیره میکنیم:
insert into test_date (timestamp, datetime) values ( '2021-01-01 00:00:00' , '2021-01-01 00:00:00' );
حالا همین دیتا رو از دیتابیس می گیریم:
خب همون جوری که میبینید هر دو داده شبیه هم ان :) خب پست تموم شد :)
ولی ماجرا به همینجا ختم نمیشه، mysql وقتی که مقدار timestamp رو قراره سیو کنه اول میاد بر اساس time_zone که برای سیستم ست شده به UTC (time_zone='00:00') تبدیلش می کنه و بعد تو دیتابیس ذخیره میکنه...
و موقعی که قراره از دیتابیس خونده بشه همین روند تکرار میشه... یعنی از دیتابیس بر اساس UTC خونده میشه و بر اساس time_zone فعلی سیستم، زمان رو نمایش میده.
نکته: مقدار پیشفرض time_zone برای هر کانکشن، time_zone سرور هستش. که شما میتونید بعد از وصل شدن عوضش کنید.
با این توضیح اگه time_zone شما در سرور هیچ تغییری نکنه دیتایی که سیو شده همیشه ثابت هستن و
تغییری نخواد کرد. (که برای کشور ایران بجز امسال و سال ۱۳۸۷ (تا جایی که من یادم میاد) ساعت‌ها رو جلو کشیدن )
برای درک بهتر این موضوع time_zone کانکشن خودمون رو عوض می کنیم و اطلاعاتی که ذخیره کردیم رو دوباره از دیتابیس می گیریم.
set session time_zone = '+03:30';
این موضوع بعضی مواقع مشکلات زیادی ایجاد میکنه(البته قابل حل).
به عنوان مثال: برای شرکتی توش هستم، اکثر فیلدهای دیتابیسش به صورت TIMESTAMP هستش و قرار بود که بر یه گزارش برای تعداد و مبلغ سفارش‌ها به تفکیک روز، هفته، ماه و سال (شمسی) داشته باشیم.
راهی که اول امتحان کردیم این بود که زمان ها رو با فانکشن UNIX_TIMESTAMP به حالت عددی تبدیل کنیمُ بعد با یه سری ضرب و تقسیم به اولین روز/هفته/ماه/سال برسیم و بعدش با تابع group_by این نتیجه‌ای که نیاز بود رو بدست بیاریم. که کوئریش به این شکل میشد.
select FLOOR((UNIX_TIMESTAMP(created_at) - 12600 /*3.5 * 60 * 60 = 12600*/ ) / 86400 /* 24*60*60 = 86400 */ ) as day, count(*) as count from users group by day
ولی مقدار ۱۲۶۰۰ ، تو 6 ماه اول باید ۱۶۲۰۰ میشد! که نتیجه صحیح نبود.
به همین ترتیب برای محاسیه هفتگی و ماهیانه و سالیانه مقدار ۸۶۴۰۰ تغییر میکرد.
که برای حل این مشکل مجبور شدیم یه جدول دیگه بسازیم و تاریخ‌های میلادی و میلادی و عدد هفته رو از ۱۰ سال قبل تا ۲۰ سال بعد (که معلوم نیست هنوز از پروژه استفاده بشه یا نه) ذخیره کردیم و تاریخ های ایجاد سفارش‌ها رو با جدول جدیدی که درست کرده بودیم، جوین زدیم... (اگه تجربه مشابه داشتین و راه حل بهتری داشتین، با منم به اشتراک بذارین?)
در مجموع باید با توجه به ساختاری که نیاز دارین یکی از این دو مورد رو تو پروژه هاتون استفاده کنید.
طبق جدولی که اول گذاشتم، استفاده از TIMESTAMP فضای ۴ بایتی نیازه داره و DATETIME فضای ۸ بایتی که قطعا این مورد تو داده های کم زیاد به چشم نمیاد... ولی وقتی دیتا ها میلیونی میشه بهتره تصمیم درستی در این مورد گرفته بشه.
این توضیحات رو میتونید از داکیومنت اصلی mysql هم بخونید.
