خیلی خوشحال می‌شدم تو این نوشته‌ها یه جاوای درست و حسابی هم میگفتم که باهم وارد jdbc بشیم و برخورد jdbc با type date رو بررسی کنیم. چون همیشه تو برنامه‌نویسی جاوا برنامه‌نویس‌ها تاریخ و زمان رو یا string می‌گیرن یا long، همیشه هم تو سطح اوراکل همه چیز رو اونجوری که دوست دارن می‌گیرن (البته اگه دیتابیس دست خودشون باشه)، به هر حال یه نقطه‌ای باید باشه که این ۲ تا رو به طور صحیح به هم وصل کنه.

جدول زیر در این پست مفروض است:

END_DATE START_DATE
02-May-90 02-May-89
10-May-89 04-May-89
10-May-90 04-May-89
04-Apr-89 04-Apr-89
04-May-89 04-Apr-89
10-May-89 04-Apr-89
10-May-91 04-Apr-89

این جدول T_DATE نام دارد. نوع داده‌های این جدول شامل موارد زیر می‌باشد.

  • فیلد START_DATE از نوع DATE
  • فیلد END_ DATE از نوع DATE

SYSDATE

این تابع، تاریخ جاری سیستم را بر می‌گرداند.

 

MONTHS_BETWEEN

این تابع، تعداد ماه‌های بین دو تاریخ را بر می‌گرداند. حال برای روشن شدن این موضوع، مثالی می‌زنیم. فرض کنید، بخواهید بدانید از شروع فعالیتتان، تا اتمام آن چند ماه طول کشیده است. بنابراین باید از تابع MONTHS_BETWEEN به شکل زیر استفاده کنید. 

SQL> SELECT START_DATE, END_DATE, MONTHS_BETWEEN (START_DATE, END_DATE)BETWEEN_ST_EN FROM T_DATE;

به جواب دستور توجه کنید. به نظر جواب قابل قبول نیست.

START_DAT END_DATE BETWEEN_ST_EN
--------- --------- -------------
02-MAY-89 02-MAY-90        -12
04-MAY-89 10-MAY-89 -.19354839
04-MAY-89 10-MAY-90 -12.193548
04-APR-89 04-APR-89          0
04-APR-89 10-MAY-89 -1.1935484
04-APR-89 10-MAY-91 -25.193548

حال دستور زیر را امتحان کنید.

SQL> SELECT START_DATE, END_DATE, MONTH_BETWEEN(END_DATE, START_DATE)BETWEEN_EN_ST FROM T_DATE;

نکته: تابع MONTHS_BETWEEN به ترتیب قرار گرفتن ماه‌ها حساس می‌باشد.

START_DAT END_DATE  BETWEEN_ST_EN
--------- --------- -------------
02-MAY-89 02-MAY-90           12
04-MAY-89 10-MAY-89    .19354839
04-MAY-89 10-MAY-90    12.193548
04-APR-89 04-APR-89            0
04-APR-89 04MAY-89             1
04-APR-89 10-MAY-89    1.1935484
04-APR-89 10-MAY-91    25.193548

توجه داشته باشید، اگر دو تاریخ در یک روز از دو ماه مختلف باشند حاصل، عدد صحیح خواهد بود، اگر دو تاریخ در یک روز و یک ماه باشند حاصل، عدد 0 خواهد بود، و اگر اختلاف دو تاریخ کمتر از یک ماه باشد حاصل، عددی منفی خواهد بود.

مثال۲:

در دستور زیر اختلاف ماه کنونی با 30 روز آینده از امروز را محاسبه کرده‌ایم:

SQL> SELECT MONTHS_BETWEEN(SYSDATE+30,SYSDATE) FROM DUAL;
MONTHS_BETWEEN(SYSDATE+30, SYSDATE)
-----------------------------------
 1.06451613

ADD_MONTHS

به وسیله‌ی این تابع می‌توانید به تاریخ مورد نظرتان، چند ماه اضافه کنید. به چگونگی استفاده از تابع ADD_MONTHS در دستور زیر توجه کنید.

SELECT START_DATE, END_DATE, ADD_MONTHS(START_DATE, 3) FROM T_DATE;

به پاسخ کویری دقت کنید. همان‌گونه که مشاهده می‌کنید، به داده‌های فیلد START_DATE، سه ماه اضافه گردیده‌ است.

START_DAT END_DATE ADD_MONTH
--------- -------- ---------
02-MAY-89 02-MAY-90 02-AUG-89
04-MAY-89 10-MAY-89 04-AUG-89
04-MAY-89 10-MAY-90 04-AUG-89
04-APR-89 04-APR-89 04-JUL-89
04-APR-89 10-MAY-89 04-JUL-89
04-APR-89 10-MAY-91 04-JUL-89

حالا مثالی دیگه می‌زنم. فرض کنید پروژه شما بنا به دلایلی برای مدت پنج ماه متوقف می‌شود. اینک شما باید برنامه‌ریزی جدید را به گونه‌ای انجام دهید که به END_DATE شما پنج ماه اضافه گردد. کوئری زیر را ببینید.

SQL> SELECT END_DATE PAST_END, ADD_MONTHS(END_DATE, 5)NEW_END FROM T_DATE;

در کوئری فوق، برای فیلد END_DATE، نام مستعار PAST_END، را برگزیدیم، سپس بوسیله‌ی تابع ADD_MONTHS، پنج ماه به داده‌های فیلد END_DATE، اضافه کردیم، و نام مستعار آن را NEW_END قرار دادیم. نتیجه‌ی کوئری مشاهده کنید.

PAST_END  NEW_END
-------- ----------
02-MAY-90 02-OCT-90
10-MAY-89 10-OCT-89
10-MAY-90 10-OCT-90
04-APR-89 04-SEP-89
04-MAY-89 04-OCT-89
10-MAY-89 10-OCT-89
10-MAY-91 10-OCT-91

مثال۲:

از تاریخ امروز به عنوان مبداً ۶ ماه آینده را نشان دهید:

SQL> SELECT ADD_MONTHS(SYSDATE,6)SYSDATE FROM DUAL;
ADD_MONTH SYSDATE
--------- -------
14-AUG-14 14-FEB-14

مثال۳:

از تاریخ امروز به عنوان مبداً ۶ ماه گذشته را نشان دهید:

SQL> SELECT ADD_MONTHS(SYSDATE,-6)SYSDATE FROM DUAL;
ADD_MONTH SYSDATE
--------- -------
14-AUG-13 14-FEB-14

LAST_DAY

این تابع، آخرین روز یک ماه مشخص را نشان‌می‌دهد. به چگونگی استفاده از تابع LAST_DAY در دستور زیر توجه کنید.

SELECT END_DATE, LAST_DAY(END_DATE) FROM T_DATE;

همان‌گونه که در ذیل ملاحضه می‌کنید، این تابع آخرین روز ماه‌های داده شده را برگدانده است. (تابع LAST_DAY برای افرادی که نمی دانند کدام ماه‌ها 30 روز و کدام ماه‌ها 31 روز است، کاربرد بسزایی دارد).

END_DATE LAST_DAY
-------- --------
02-MAY-90 31-MAY-90
10-MAY-89 31-MAY-89
10-MAY-90 31-MAY-90
04-APR-89 30-APR-89
04-MAY-89 31-MAY-89
10-MAY-89 31-MAY-89
10-MAY-91 31-MAY-91

شما می‌توانید با استفاده از این تابع، سال‌های کبیسه را نیز بدست آورید. به دستور زیر توجه کنید. برای این مثال از جدول DUAL استفاده می‌نماییم.

SQL> SELECT LAST_DAY ('1 FEB 08')LEAP_YEAR, LAST_DAY('1 FEB 09')NOW_LEAP_YEAR FROM DUAL;

همان‌گونه که در زیر مشاهده می‌کنید، این تابع سال کبیسه را به شما نشان می‌دهد.

LEAP_YEAR MON_LEAP_
--------- ---------
29-FEB-08 28-FEB-09

مثال۲:

آخرین روز ماه جاری را نمایش دهید:

SQL> SELECT LAST_DAY(SSYDATE) FROM DUAL;
LAST_DAY(
---------
28-FEB-14

NEXT_DAY

این تابع، اولین روز بعد از تاریخ مورد نظر را، نشان می‌دهد. به مثال زیر توجه کنید.

SQL> SELECT NEXT_DAY('1 FEB 08', 'TUSEDAY') FROM DUAL;

در کوئری فوق، توسط تابع NEXT_DAY خواسته شده‌است، تاریخ اولین سه‌شنبه بعد از 01-FEB سال 2008 را نشان می‌دهد. نتیجه‌ی کوئری را مشاهده کنید.

NEXT_DAY
--------
05-FEB-08

حالا مثال دیگه‌ای با استفاده از جدول T_DATE می‌زنم:

SQL< SELECT * FROM T_DATE;
START_DAT END_DATE
--------- ----------
02-MAY-89 02-MAY-90
04-MAY-89 10-MAY-89
04-MAY-89 10-MAY-90
04-APR-89 04-APR-89
04-APR-89 04-MAY-89
04-APR-89 10-MAY-89
04-APR-89 10-MAY-91

در این مثال می‌خواهیم، تاریخ روز‌های جمعه‌ی بعد از END_DATE را بدست آوریم. به دستور زیر توجه کنید.

SELECT END_DATE, NEXT_DAY(END_DATE, 'FRIDAY') FROM T_DATE;

در پاسخ این کوئری همان‌گونه که انتظار داشتیم، تاریخ روزهای جمعه بعد از END_DATE را، به نمایش گذاشته‌ است.

END_DATE NEXT_DAY
-------- --------
02-MAY-90 07-MAY-90
10-MAY-89 15-MAY-89
10-MAY-90 14-MAY-90
04-APR-89 10-APR-89
04-MAY-89 08-MAY-89
10-MAY-89 15-MAY-89
10-MAY-91 13-MAY-91

مثال۲:

اولین جمعه بعد از تاریخ جاری را نمایش دهید:

SQL> SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;
NEXT_DAY(
---------
21-FEB-14

نکته: این تابع در موقع زمانیندی سیستم برای انجام کاری یا گزارش‌گیری از جداول و یا ... بسیار کاربرد دارد.

NEW_TIME

تابع NEW_TIME، زمان را بر اساس زمان منطقه‌ای تطبیق می‌دهد. این تابع دارای سه آرگومان می‌باشد.اولین آرگومان تاریخ داده شده‌است. دومین و سومین آرگومان به شکل XST و XDT می‌باشد. S به معنای استاندارد و D به معنای تغییر با نور روز است. به مثال‌های زیر توجه کنید.

SQL> SELECT NEW_TIME('15 MAR 2008','AST','GMT') FROM DUAL;
NEW_TIME
--------
15-MAR-08

SQL> SELECT NEW_TIME('15 MAR 2008','EDT','PDT') FROM DUAL;
NEW_TIME
--------
14-MAR-08

حالا مثالی با استفاده از جدول T_DATE می‌زنم:

SQL> SELECT END_DATE, NEW_TIME(END_DATE, 'EDT', 'PDT') FROM T_DATE;
END_DATE NEW_TIME(
-------- ---------
02-MAY-90 01-MAY-90
10-MAY-89 89-MAY-89
10-MAY-90 09-MAY-90
04-APR-89 03-APR-89
04-MAY-89 03-MAY-89
10-MAY-89 09-MAY-89
10-MAY-91 09-MAY-91

نحوه نمایش تاریخ فارسی در اوراکل

برای نمایش تاریخ در فرمت صحیح تقویم جلالی می‌توانید از دستور زیر که یکی از دستورات DDL به حساب می‌آید استفاده کنید:

alter session set nls_calendar='persian';

SELECT SYSDATE FROM DUAL;

SYSDATE
------------------------
25 Bahman 1394

توجه داشته باشید فقط نمایش تاریخ در این statement یا session به صورت تاریخ فارسی در می‌آید و اگه این statement یا session از بین بره نمایش تاریخ دوباره به همان شکل میلادی است. البته چون اینکار از سطح دیتابیس اوراکل انجام می‌شود پس اگر بعد از ورود کاربران این دستور اجرا شود (مثلاً توسط یک trigger یا داخل دستور SQL) مشکلی ایجاد نمی‌کند.

مثال:

Select to_char(hiredate,'yyyy/mm/dd','nls_calendar=persian') From DUAL;
Select to_char(sysDate,'YY/MM/DD','NLS_Calendar=persian') From DUAL;

نکته: اگر این session بسته بشه و دوباره بدون اعمال nls_calendar=persian بیایم و select بگیریم تاریخ به صورت پیش‌فرض میلادی نمایش داده می‌شود.

SELECT SYSDATE FROM DUAL;
SYSDATE
------------------------
14-FEB-15

عملگرها در توابع تاریخ و زمان 

به مثال‌های زیر توجه کنید:

SQL> ALTER SESSION SET NLS_CALENDER='PERSIAN'
Session altered.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------
25 BAHMAN 1394
SQL> SELECT SYSDATE+30 FROM DUAL;
SYSDATE+30
------------------
25 ESFAND 1394
SQL> SELECT SYSDATE-30 FROM DUAL;
SYSDATE-30
------------------
25 DEY 1394

DURATION

به مثال زیر توجه کنید، در این کوئری می‌خواهیم DURATION امروز تا ۳۰ روز آینده رو به دست بیاوریم(یعنی ۳۰ روز دیگه به امروز چند روز تفاوت داره):

SQL> SELECT (SYSDATE+30)-SYSDATE FROM DUAL;
SYSDATE-(SYSDATE+30)
--------------------
                 30

نکته: توجه داشته باشید که DEFAULT تابع DATE‌ روز است و برای نمایش به فرمت‌های دیگر باید از TIMESTAMP استفاده کنیم.

نکته: خروجی دستورات این تابع را می‌توان هم در فیلدی از جنس STRING و هم در فیلدی از جنس DATE‌ ذخیره‌سازی کرد.

نکته: TYPE فیلد ذخیره‌سازی ما برای مقادیر تاریخ و زمان می‌تونه TIMESTAMP و DATE باشه.

تبدیل تاریخ به رشته

به مثال زیر توجه کنید:

SELECT SYSDATE FROM DUAL;
SYSDATE
------------------
25 BAHMAN 1394
SQL> SELECT to_char(sysdate, 'yyyy:mm:dd hh:mi:ss') FROM DUAL;
TO_CHAR(SYSDATE,'YY
-------------------
1394:11:25 11:12:00

در این مثال شما دیگه نیازی به TIMESTAMP هم ندارید ولی اگر هم می‌خواستیم این رشته رو نگه‌داری کنیم TIMESTAMP می‌توانستیم بگیریم. من به جای TIMESTAMP از رشته استفاده کردم.

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

تبدیل رشته به تاریخ

برای تبدیل خروجی داده به نوع داده‌ای DATE می‌توانید مانند مثال زیر عمل کنید:

SELECT TO_DATE('22/08/2014','DD/MM/YYYY') FROM DUAL;
TO_DATE('
---------
22-AUG-14

نکته: توجه داشته باشید تابع DATE در خودش زمان را نگه‌داری نمی‌کند و تنها تاریخ را نگه‌داری می‌کند. به مثال زیر توجه کنید:

SELECT TO_DATE('22/08/2014','DD/MM/YYYY HH:MI:SS') FROM DUAL;
TO_DATE('
---------
22-AUG-14

نکته: حتماً فرمت صحیح تاریخ باید در آرگومان دوم آورده شود تا بتواند رشته را به نوع داده‌ای DATE تبدیل کند:

SELECT TO_DATE('22/08/2014','DD/MM/Y') FROM DUAL;
 *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

حالا می‌خوام دچار اشکال منطقی‌اش کنم:

SELECT TO_DATE('10/08/2014','mm/dd/yy') FROM DUAL;
TO_DATE('
---------
08-OCT-14

همونطور که می‌بینید در خروجی جای ماه و روز عوض شده است.

انواع حالت‌‌های دیگر نمایش تاریخ

SQL> SELECT TO_CHAR(SYSDATE, 'yyyy:mm:dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014:02:14 11:17:33
SQL> SELECT TO_CHAR(SYSDATE, 'yyyy:mm:dd hh:mi:ss am') from dual;
TO_CHAR(SYSDATE,'YYYY:
----------------------
2014:02:14 11:17:50 am
SQL> SELECT TO_CHAR(SYSDATE, 'yyyy:mm:dd hh:mi:ss pm') from dual;
TO_CHAR(SYSDATE,'YYYY:
----------------------
2014:02:14 11:17:55 am
SQL> SELECT TO_CHAR(SYSDATE, 'yyyy:mm:dd hh!mi!ss pm') from dual;
TO_CHAR(SYSDATE,'YYYY:
----------------------
2014:02:14 11!18!39 am