تاریخ در SQL
سخت ترین قسمت کار با Date این است که مطمئن شویم فرمت تاریخی که شما سعی می کنید وارد کنید با فرمت ستون تاریخ در پایگاه داده هم خوانی داشته باشد.
تا زمانی که داده فقط شامل تاریخ باشد، Query شما همان طور که انتظار دارید کار خواهد کرد. اما اگر با بخش زمان درگیر شوید، کار کمی پیچیده خواهد شد.
قبل از صحبت درباره پیچیدگی های کار با تاریخ، توابع مهم آن را با هم مرور می کنیم:
توابع تاریخ در MySQL
جدول زیر، لیستی از مهم ترین توابع تاریخ در MySQL است:
تابع | توضیحات |
---|---|
NOW() | تاریخ و زمان جاری را برمی گرداند |
CURDATE() | تاریخ جاری را برمی گرداند |
CURTIME() | زمان جاری را برمی گرداند |
DATE() | بخش تاریخ را از یک عبارت date/time بیرون می کشد |
EXTRACT() | یکی از بخش های عبارت date/time مانند سال، ماه، روز، ساعت و ... را برمی گرداند |
DATE_ADD() | یک فاصله زمانی مشخص را به تاریخ اضافه می کند |
DATE_SUB() | یک فاصله زمانی مشخص را از تاریخ کم می کند |
DATEDIFF() | تعداد روز بین دو تاریخ را برمی گرداند |
DATE_FORMAT() | نمایش تاریخ و زمان در فرمت های مختلف |
توابع تاریخ در SQL Server
جدول زیر، لیستی از مهم ترین توابع تاریخ در SQL Server است:
تابع | توضیحات |
---|---|
GETDATE() | تاریخ و زمان جاری را برمی گرداند |
DATEPART() | یکی از بخش های عبارت date/time مانند سال، ماه، روز، ساعت و ... را برمی گرداند |
DATEADD() | یک فاصله زمانی مشخص را به تاریخ اضافه یا کم می کند |
DATEDIFF() | زمان بین دو تاریخ را برمی گرداند |
CONVERT() | نمایش تاریخ و زمان در فرمت های مختلف |
انواع داده های تاریخ در SQL
MySQL:
- DATE - format YYYY-MM-DD
- DATETIME - format: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
- YEAR - format YYYY or YY
SQL Server:
- DATE - format YYYY-MM-DD
- DATETIME - format: YYYY-MM-DD HH:MM:SS
- SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - format: a unique number
توجه: زمانی که یک جدول جدید تعریف می کنید باید برای هر ستون، Data Type آنرا مشخص کنید.
اگر مایل هستید کلیه ی انواع داده را مرور کنید به مطلب SQL Data Types مراجعه نمایید.
کار با تاریخ در SQL
توجه: اگر خودتان را با بخش زمان درگیر نکنید، براحتی می توانید دو تاریخ مختلف را با هم مقایسه کنید!
فرض کنید جدول "Orders" را داریم:
OrderId | ProductName | OrderDate |
---|---|---|
1 | Geitost | 2008-11-11 |
2 | Camembert Pierrot | 2008-11-09 |
3 | Mozzarella di Giovanni | 2008-11-11 |
4 | Mascarpone Fabioli | 2008-10-29 |
حالا می خواهیم رکوردهایی با تاریخ "2008-11-11" را انتخاب کنیم.
از Query زیر استفاده می کنیم:
نتیجه به شکل زیر خواهد بود:
OrderId | ProductName | OrderDate |
---|---|---|
1 | Geitost | 2008-11-11 |
3 | Mozzarella di Giovanni | 2008-11-11 |
حالا، فرض کنید که جدول "Orders" شبیه زیر باشد. (بخش زمان به فیلد OrderDate اضافه شده است)
OrderId | ProductName | OrderDate |
---|---|---|
1 | Geitost | 2008-11-11 13:23:44 |
2 | Camembert Pierrot | 2008-11-09 15:45:21 |
3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
اگر از Query بالا استفاده کنیم نتیجه ای نخواهیم داشت، بنابراین اگر می خواهید Queryی ساده ای داشته باشید، اجازه ندهید بخش زمان در تاریخ وارد شود.
توجه: در SQL Server با استفاده از تابع ()substring می توان قسمت تاریخ را بیرون کشید و سپس مقایسه کرد.
برای مشاهده فیلم های آموزشی مقدماتی تا پیشرفته پایگاه داده SQLServer کلیک کنید.
یعنی فقط کافی است محدودیت Default را با مقدار ()GetDate تنظیم نمایید.
برای کسب اطلاعات بیشتر در مورد محدودید Default در SQL به لینک زیر مراجعه فرمایید:
beyamooz.com/sql/101-advanced/279-%D9%85%D8%AD%D8%AF%D9%88%D8%AF%DB%8C%D8%AA-default-%D8%AF%D8%B1-sql
آیا باید از مقدار varChar برای ایجاد تاریخ شمسی استفاده کنیم و مقدرا date بدرد نمیخورد؟!
برای ذخیره تاریخ شمسی معمولا از varchar استفاده میشه و شما باید برای درج تاریخ، از توابع مخصوص اینکار هنگام ورود اطلاعات توسط کار استفاده کنید و هنگام ذخیره تاریخ در دیتابیس، مقدار اون رو به صورت یه رشته در دیتابیس ذخیره کنید.
دیتابیس موردنظر رو بذارید و اینکه عملیاتی که نیاز هست رو هم بگید، اینطوری بهتر میشه کمک کرد.
طبق گفتههای شما تاریخ موردنظر به صورت string ذخیره شده و عملیات محاسباتی روی تاریخ رو نمیشه رو این انجام داد. اگر هم انجام بشه، با ضریب خطا همراه خواهد بود. شما باید ابتدا این مقادیر رو به نحوی تبدیل به تاریخ کنید، و سپس از اون برای انجام محاسبات استفاده کنید. بعضی از این عملیات در خود SQL شدنی هست، ولی برای بعضی از اونها باید از راههای دیگه استفاده کرد.
substring(OrderDate,1,10)
beyamooz.com/sql-server/155-function/string-functions/454-%D8%AA%D8%A7%D8%A8%D8%B9-SUBSTRING-%D8%AF%D8%B1-SQL-Server
خیلی راحت با View
Select Name, name, name, family from table_name
ستون نام سه بار در خروجی تکرار میشه البته باید براشون نام مستعار بزارید با As
برای بدست آوردن تعداد تکرارهای یک فیلد، مجبوریم از Group by استفاده کنیم، به مثال زیر توجه کنید:select f1
from table_name
group by f1
having count(f1)=2
در مثال بالا، بر اساس فیلد f1 گروه بندی کرده ایم، پس براحتی با استفاده از count می توانیم تعداد تکرارهای هر گروه را بدست آوریم، یعنی تعداد تکرارهای f1، بنابراین با توجه به شرط ذکر شده توسط شما، فقط کافی است در قسمت having تعداد تکرار f1 را مساوی با 2 قرار دهیم.
برای اینکار شما باید از توابع مخصوص کار با تاریخ استفاده کنید. برای جمع یا تفریق تاریخ توابع خاصی وجود داره که معمولا در هر زبانی هست. ولی اگه موجود نباشه، باید خودتون به صورت دستی یه تابعی بنویسید که مقادیر تو تاریخ رو با هم جمع یا تفریق کنه، و نتیجه رو به صورت سال و ماه و روز نشون بده.