توابع تاریخ شمسی جهت استفاده در Mysql

 

این پروژه رو تقدیم می کنم به سازمان مدیریت برنامه نویسان php

 

در تمامی پروژه‌هایی که نیاز به تاریخ شمسی است، عموما برنامه‌نویسان ایرانی با مشکلات خاصی ریز و درشتی روبرو هستند، بر ای مثال بهترین راهکار ذخیره‌سازی تاریخ شمسی در یک جدول چیست؟


من در خیلی از تالارهای گفتگو، در پاسخ به این سوال، بارها و بارها دیدم که پیشنهاد شده برای ذخیره‌سازی تاریخ و محاسبات دقیق‌تر بر روی فیلدهایی که نوع آنها تاریخ است، بهتر است که تاریخ را به صورت میلادی و از نوع فیلد timestamp ذخیره و نگهداری نمایید. چون این روش از هر لحاظ بسیار کارآمد و بهینه‌تر است. برای مثال ما می‌تونیم با داشتن یک تاریخ میلادی از نوع(timestamp)، براحتی تمامی تاریخ‌های دیگر همچون شمسی و قمری را استخراج کنیم. اما خب این روش نیز معایب خودش رو دارد. بزارید یک مورد از این معایب رو با بیان یک مثال مورد بررسی قرار دهیم.

همانطور که می‌دانید درMysql توابع بسیار خوب و کاربردی برای کار با مقادیر از نوع تاریخ و زمان وجود دارد که برنامه‌نویسان می‌تواند بدون درگیر شدن با کدنویسی و تنها با استفاده از یک تابع در کوئری خودشان سریع به نتیجه دلخواه‌شان برسند. به عنوان مثال فرض کنید ما می‌خواهیم یک آرشیو ماهیانه برای سیستم خبری ایجاد کنیم:

SELECT
   YEAR ( created ) AS sal,
   MONTH ( created ) AS mah,
   COUNT( * ) AS visit 
FROM
   ips 
GROUP BY
   sal,
   mah 
ORDER BY
   sal DESC,
   mah DESC

برنامه نویس با مثال بالا به راحتی می تواند آرشیو ماهیانه مطالب سیستم خود را ایجاد کند. البته به میلادی.
اما برای آرشیو ماهیانه شمسی آیا جواب گو هست. جواب این هست. خیر!!!.

برای این خواسته توسعه دهندگان روش‌های متنوع و مختلفی بسته به نوع نیاز خود بهره می‌گیرند. عده‌ای یک فیلد با جدول خود اضافه می‌کنند و مقادیر تاریخ شمسی را درج می‌کنند. عده‌ای دیگر می‌یابند تاریخ شروع مطالب. تاریخ پایانی رو پیدا می‌کنند. و بعد از تجزیه و تحلیل کردن آرشیو ماهانه را استخراج می‌کنند. تمامی روش‌های موجود دارای پردازش کم و بیش بالایی هستند. تا به نتیجه دلخواه برسند.

من در این پروژه با استفاده از قابلیت تعریف پروسیجر و تابع که در نسخه ۵٫x.x  به بعد ارائه شد.  یک سری تابع  ، همانند توابع MySql شبیه سازی کردم با این تفاوت که این توابع نتیجه را به صورت شمسی بر میگردانند.

در این پروژه توابع کاربردی date(),month(),year(), monthname() و … پیاده‌سازی شده‌اند:

SELECT
pdate(NOW()), # 1400-08-22 00:12:18
pmonthname( NOW( ) ), # آبان
pdate( '1982-12-25' ),# 1361-10-04
pyear( '1982-12-25' ),# 1361
pmonth( '1982-12-25' ), # 10
pmonthname( '1982-12-25' ),# دی
gdate( 1361, 10, 4 ), # 1982-12-25 00:00:00
gdatestr( '1361/10/4' ); # 1982-12-25 00:00:00

برای استفاده از این توابع شما کافیست مراحل زیر را انجام بدید.

۱- دریافت فایل توابع : دریافت

۲- ایپورت کردن فایل در دیتابیس مورد نظر

بعد از انجام مراحل بالا برای تست توابع می تونید از این مثال استفاده کنید.

SELECT
   pnum(pdate(NOW())),
   pyear('2009-09-22'),
   pmonth('2009-09-22'),
   pmonthname( NOW());

مثال آرشیو ماهیانه

SELECT
   pyear( created ) AS sal,
   pmonth( created ) AS mah,
   COUNT( * ) AS visit 
FROM
   ips 
GROUP BY
   sal,
   mah 
ORDER BY
   sal DESC,
   mah DESC

اگر نتیجه گرفتید. پس براحتی می توانید از این توابع کمال استفاده را ببرید.

نکته : در حال حاضر فقط این توابع را فقط می توان از نسخه‌های ۵.۱ و بیشتر مورد استفاده قرار داد.

جهت ادامه روند توسعه و رفع مشکلات این پروژه، من پروژه رو در github قرار دادم.

 

69 دیدگاه در “توابع تاریخ شمسی جهت استفاده در Mysql

    • خیلی مخلصیم و خوشحالم که این پروژه من توانسته کار شما را راه بیندازد
      در مورد نکته که متذکر شدید بله حق با شماست چون این این اسکریپت برای لوکال سرور نوشته شده و در حالات مختلفی شاید لازم باشد تغییراتی مثل موردی که شما گفتید اعمال شود.

  1. سلام،

    ممنون بابت توابع بسیار خوب اما یک مشکل وجود داره. وقتی در یک سرور production تنظیمات binary logging فعال باشه اجازه ی ساخت توابع داده نمیشه:

    This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

    برای رفع این مشکل مجبور هستیم چک کردن این مورد رو برداریم:
    SET GLOBAL log_bin_trust_function_creators = 1;
    که کار جالبی نیست بهتره keyword های لازم به توابع اضافه بشن که مشکل حل بشه. من متاسفانه در git بخش issue ها رو باز ندیدم و نشد اونجا مطرح کنم.

  2. سلام

    ممنون از زحماتتون .از کد شما استفاده کردم و درست کار می کرد . فقط آیا می شه اسم روزهای هفته رو استخراج کرد ؟تاریخ رو بگیره بگه که کدوم روز از هفته س مثل شنبه ،یکشنبه  … .

    شاد و سر بلند باشید

  3. سلام دوست عزیز من تابع ()time را بعنوان زمان و تاریخ در دیتابیس در جدول tbl_post و فیلد create_time  ذخیره میکنم و در کدی که شما قرار دادید نیازی به تبدیل تاریخ به unix  ندارم یعنی نیازی به  استفاده از  FROM_UNIX ندارم چون خروجی ()time خودش unix هست. فانکشن هایی که قرار دادید با موفقیت اجرا شد.

    ولی خروجی   `SELECT pmonth(`create_time`) as mo from `tbl_post

    صحیح نیست ولی

    select pmonth(now())              test خروجی صحیحی میده

    آیا فانکشن ها روی خروجی ()time کار نمیکند؟

    لطفا راهنمایی کنید چه مقداری را بعنوان تاریخ در فیلد create_time قرار دهم که توابع شما بتوانند درست عمل کنند pyear هم خروجی صحیحی نمیدهد و -۶۲۵ را برمیگرداند.

    نسخه mysql بنده ۵٫۶٫۲۱ می باشد. استفاده از FROM_UNIX خطا میدهد

  4. با سلام

    قبل هر چیز بگم بنده زیاد وارد نیستم اما برام جای سوال شد که چرا با یک کد ساده میشه این کارو انجام داد انقدر سختش کرد ؟

    قبل از هرچیزی اگه از تایم استمپ استفاده کنیم فقط یک فیلد در جدول خواهد بود که این خودش کارو راحت تر میکنه

    پس

    function jalali_to_gregorian($j_y,$j_m,$j_d,$mod=”){
    $j_y=tr_num($j_y); $j_m=tr_num($j_m); $j_d=tr_num($j_d);/* <= :این سطر ، جزء تابع اصلی نیست */
    $d_4=($j_y+1)%4;
    $doy_j=($j_m<7)?(($j_m-1)*31)+$j_d:(($j_m-7)*30)+$j_d+186;
    $d_33=(int)((($j_y-55)%132)*.0305);
    $a=($d_33!=3 and $d_4<=$d_33)?287:286;
    $b=(($d_33==1 or $d_33==2) and ($d_33==$d_4 or $d_4==1))?78:(($d_33==3 and $d_4==0)?80:79);
    if((int)(($j_y-19)/63)==20){$a–;$b++;}
    if($doy_j<=$a){
    $gy=$j_y+621; $gd=$doy_j+$b;
    }else{
    $gy=$j_y+622; $gd=$doy_j-$a;
    }
    foreach(array(0,31,($gy%4==0)?29:28,31,30,31,30,31,31,30,31,30,31) as $gm=>$v){
    if($gd<=$v)break;
    $gd-=$v;
    }
    return($mod==”)?array($gy,$gm,$gd):$gy.$mod.$gm.$mod.$gd;
    }

    function Jalali_To_TimeStamp($year,$month,$day,$time){
    $converttotimestamp=jalali_to_gregorian($year,$month,$day);
    $mtime=explode(‘:’,$time);
    $converttotimestamp=strtotime(“$converttotimestamp[0]-$converttotimestamp[1]-$converttotimestamp[2]”)+(($mtime[0]*60*60)+($mtime[1]*60)+$mtime[2]);
    return $converttotimestamp;
    }
    با توجه به دو تابع بالا
    $mytime=Jalali_To_TimeStamp(‘1394′,’1′,’1′,’00:01’);
    SELECT * FROM news WHERE publish_date>$mytime

     

  5. من از هرات افغانستان هستم. واقعا تشکر از زحمات شما که کار برنامه نویسان را خیلی راحت کردید. من قبلا از تاریخ جلالی استفاده می کردم. باز هم تشکر

    • خواهش می کنم. من فقط یکی از نیاز های خودم رو برطرف کردم . پابلیک کردم. لطفا اگر کندی خاصی رو دیدی اطلاع بدید. چون رو نسخه جدید برای مای اسکیوال ۵.۶ دارم کار میکنم.
      ممنون

  6. سلام
    من وقتی فایل رو ایمپورت میکنم این خطا را میگره . ضمنا من از ومپ ۲.۲ استفاده میکنم .
    Error

    SQL query:

    DELIMITER ; ;

    CREATE DEFINER = `root`@`localhost` FUNCTION `__mydiv` (
    `a` int,
    `b` int
    ) RETURNS bigint( 20 ) BEGIN # Copyright (C) 2009-2012 Mohammad Saleh Souzanchi
    # WebLog : http://www.saleh.soozanchi.ir
    # Version V1.0.2
    RETURN FLOOR( a / b ) ;

    END ; ;

    MySQL said: Documentation
    #۱۴۱۸ – This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

    • درود بر شما
      در ابتدا لطفا آخرین نسخه رو از گیت هاب دریافت کنید.
      اما از ظاهر خطا فکر میکنم. از تنظیمات جدید مای اسکیوال باشه.
      در صورتی که مشکلی بود لطفا در گیت گزارش کنید.

  7. سلام
    به نظر می‌رسه برای اینکه این مجموعه بتونه خوب استفاده بشه، باید:
    الف: قسمت definerها را اصلاح کرد. (در اصل باید حذفش کرد تا در همه بانک‌های داده قابل استفاده باشه)
    ب. تعریف توابع را اصلاح کرد و برای همه توابع شخصیت‌ش را مشخص کرد. این کار می‌تونه باعث افزایش راندمان توابع و بهبودامنیت توابع بشه.

  8. سلام
    من از MySQL 5.5.19 استفاده میکنم.
    با تغییر DELIMITER ;; به DELIMITER $$ درست شد.

    DROP FUNCTION IF EXISTS `__mydiv`;
    DELIMITER $$
    CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `__mydiv`(`a` int, `b` int) RETURNS bigint(20)
    BEGIN
    # Copyright (C) 2009-2011 Mohammad Saleh Souzanchi
    # WebLog : http://www.saleh.soozanchi.ir
    # Version V1.0.2

    return FLOOR(a / b);
    END;;
    DELIMITER ;

  9. سلام
    موقع وارد کردن تو دیتابیس پیغام زیر رو میده، ممنون میشم راهنمایی کنید

    SQL query:

    DELIMITER ; ;

    CREATE DEFINER = `root`@`127.0.0.1` FUNCTION `__mydiv` (
    `a` int,
    `b` int
    ) RETURNS bigint( 20 ) BEGIN # Copyright (C) 2009-2011 Mohammad Saleh Souzanchi
    # WebLog : http://www.saleh.soozanchi.ir
    # Version V1.0.2
    RETURN FLOOR( a / b ) ;

    END ; ;

    MySQL said: Documentation
    #۱۴۱۸ – This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

  10. با سلام.
    ممنون از زحمات شما بابت این توابع ضروری.
    متاسفانه امکان import کردن این توابع در MYSQL هاست من خطای
    #۱۵۴۸ – Cannot load from mysql.proc. The table is probably corrupted

    رو نشون میده. لطفا راهنمایی کنید.
    با تشکر.

    • سلام ممنون .
      خیر جز اینکه توابع رو تغییر بدید. تا فمری رو بتونید خودتون بدست بیارید.
      این یک پروژخ مربوط به ۲ سال پیش هست. فرصتی پیش نیومده تا الان مجدد کار کنم. اما با اومدن مای اسکیو ال ۵.۵ امکانات و دستورات خوبی رو ارائه داده. به زودی روش کار میکنم. اما به سمت قمری نخواهم رفت.

  11. سلام
    من می خواهم به جدولم یک ستون اضافه کنم ولی ابتدا باید چک کند که این فیلد وجود دارد یا نه وهمچنین اگرکاید سمتریک منلا با نام xوجود ندارد آن کلیدرابسازد
    خواهش می کنم زود تا۱-۶-۱۳۹۰جواب دهید

  12. روز خوش
    آقا صالح از اونجایی که خودتون تو کامنت ۱۶ گفتین که این اسکریپت توابع آزادن. من جسارت کردم و توابع تبدیل تاریخ شمسی به میلادی رو اضافه کردم و قدری اصلاحات دیگر روی اسکریپت اصلی انجام دادم و اون را در قالب مجوز gpl انتشار دادم.
    اگه راضی به اینکار نیستین اعلام کنید.
    دوستان عزیز هم اگه خواستن اسکریپت جدید رو داشته باشن به اینجا مراجعه کنن.
    http://spitman.azdaa.com/fa/?p=33
    اینم لینک دانلود
    http://spitman.azdaa.com/wp-content/uploads/code/pdate-mysql.sql

    • مهران عزیز من از اینکه مشکلات رو گزارش و در توسعه اون کمک کردید.
      ضمن اینکه هیچ مشکلی در عمل شما نیست. بسیار سپاس گزارم.
      فقط یک خواهش دارم. به دلیل اینکه روند تغییرات و توسعه این پروژه یکپارچه بشه من در گیت هاپ منتشرش کردم. ممنون میشم شما هم مواردی که در وبلاگتون ذکر کردید به همراه تعییرات در مخزن این پروژه منتشر کنید

  13. لطفا کامنت قبلی بنده رو حذف کنید.

    اشکال کاملا وارد هست.

    این قسمت:

    WHEN 12 THEN RETURN ‘بهمن’;
    WHEN 12 THEN RETURN ‘اسفند’;

    باید اصلاح بشه.

    لطفا فایل را اصلاح بفرمایید.

    phpkar :
    سلام و خدا یارتون
    مثل اینکه توی تابع pmonthname مشکلی هست. قسمت case تعداد ماهها (ماه یازدهم ۱۲ نوشته شده)
    ممنون

  14. ممنونم.

    من هنوز تست هم نکردم.

    اما گویا قسمت
    CASE PMONTH(gdate)

    که از ۱ تا ۱۲ هست … اما قسمت

    CASE m

    از ۰ تا ۱۱ هست.

    که البته فکر میکنم باید از ۱ تا ۱۲ باشند به جای ۰ تا ۱۱.

  15. @ترنم
    خوب نباید مشکلی داشته باشید. آیا ارور خواصی دریافت می کنید؟
    یا شاید انتظار دارید وقتی ایمپورت شد تو لیست تیبل ها بیاییند؟

    برای تست اینکه ایمپورت شده اند یا خیر یک کوئری اجرا کنید و دستورات رو در این کوئری استفاده کیید

  16. سلام و ممنون

    فقط خواستم در مورد licence اش سوال کنم. آیا می تونیم در هر نوع پروژه ای ازش استفاده کنیم؟

    در ضمن یک ابزار کاملا اوپن سورس رو هم می خوام معرفی کنم. حتما به درد دوستای PHPکار می خوره:
    tinybutstrong

  17. بنظرم بهتره به جای استفاده از این توابع sql از توابع تبدیل تاریخ در خود php استفاده کنیم. چون در آرشیو ماهانه، مهم ماه ارسال مطلب هست،
    در اینجا ممکن است نتیجه چندماه یکسان باشند که با array_unique() تکراری ها رو حذف می کنیم و پس از آن آرایه رو برای چند روز کش می کنیم و در هنگام درخواست از کش که بصورت آرایه سریال ذخیره شده استفاده می کنیم. مثل وردپرس که تقریبا هر ۲ روز کش می کنه

  18. @اوژن استوار
    ممنون دوست عزیز
    در جواب سوال شما باید خدمتتون عرض کنم که بنده برنامه نویس وب هستم. با زبان پی اچ پی کار میکنم. و کلا با محصولات ماکروسافتی میانه خوبی ندارم.

    اما میتونم یک پاسخ خدمتتون بدم. اون هم این هست. که این گونه ابزار های برای ایجاد پل ارتباطی بین شمای کد نویس و سرویس دهنده شما هست. و قالبا به صورت ویزاردی ایجاد میشوند تا به روند کار شما سرعت ببخشند.

    من چند سالی هست که قیافه ویژاول استدیو رو ندیدم. اما تا اونجا که یادم هست. اونچه مورد نیاز شما هست رو جواب گو هست. اما اگر به دنبال کار حرفه با mssql هستید. بهتره از خود SQL Server Management Studio استفاده کنید.

  19. سلام و خسته نباشید
    مطلب جالبی بود … یک برنامه نویس حالیش نمیشه چه سوالی رو کجا بپرسه …

    من میخواستم واسه نرم افزار(حسابداری) که خودم دارم مینویسم دیتابیس طراحی کنم برنامم رو تو visual Studio 2008 به زبان C# نوشتم آیا برای طراحی دیتابیس از Sql Server خود ویژال استفاده کنم به مشکل و محدودیتی بر نمیخورم مثل بک آپ گیری و … ؟ اگر این امکان وجود نداره از کدام ورژن Sql Server استفاده کنم؟
    چه تفاوتی میکنه که دیتابیس رو تو خود ویژال یا جداگانه در SQl Serverطراحی کنیم؟

  20. تلاش علمی – عملی شما به همراه آزاد اندیشی فکری شما برای در اختیار قرار حاصل تلاش خود در اختیار دیگر عزیزان قابل تقدیره.
    صمیمانه از شما تشکر می کنم و براتون آزروی موفقیت دارم

    با احترام
    مخلصی

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این سایت از اکیسمت برای کاهش هرزنامه استفاده می کند. بیاموزید که چگونه اطلاعات دیدگاه های شما پردازش می‌شوند.