Date Time Related Data Types And Functions In Oracle

Developers always use time related data types in their applications to audit information. Oracle give additional functionality supplied functions and data types. One of my previous posts i mentioned how to use interval data types. Now i want to give some examples how to use some of them.
First it is possible to convert interval data from string values:
SQL> SELECT to_yminterval('02-10') FROM dual;

TO_YMINTERVAL('02-10')
----------------------
+000000002-10

SQL> SELECT to_dsinterval('02 01:00:30') FROM dual;

TO_DSINTERVAL('0201:00:30')
---------------------------
+000000002 01:00:30

Second, i think more powerful, convert datetime strings from external programing languages such as C# or Java, Oracle timestamps and manipulate easiliy with them. Suppose you want to convert format of a datetime string. ( '23:12:54.899 +02:00 Thu Nov 29 2001' to '011129' )With oracle's supplied functions you are not need to make conversions such find-replace. Look at example below:

SQL> SELECT tz_offset('Asia/Istanbul') FROM dual;

TZ_OFFSET('ASIA/ISTANBUL')
--------------------------
+02:00

SQL> SELECT to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY') FROM dual;

TO_TIMESTAMP_TZ('23:12:54.899+'
-------------------------------------------------
29-NOV-01 11.12.54.899000000 PM +02:00

SQL> SELECT to_char( to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY'), 'YYMMDD') FROM dual;

TO_CHAR(TO_TIMESTAMP_TZ('23:12'
------------------------------
011129

How To Extract Date Values In Oracle

Kimi zaman uygulamalar içinde tarih değerleri arasındaki farkları alıp kullanabilirsiniz. Oracle'da iki tane date tipi ile normal sayısal değerler arasında fark almak gibi işlem yapılır. Sonuç gün cinsinden numerik olarak döner. İstediğiniz formata dönüştürebilirsiniz. Şöyleki;
DECLARE
start_date DATE;
end_date DATE;
time_difference NUMBER;
time_difference_in_seconds NUMBER;
time_difference_in_minutes NUMBER;

BEGIN
start_date := to_date('09.09.2006 09:57:49',
'DD.MM.YYYY HH24:MI:SS');
end_date := to_date('09.09.2006 09:59:34',
'DD.MM.YYYY HH24:MI:SS');
time_difference := end_date - start_date;

time_difference_in_seconds := time_difference * 86400; --24*60*60
time_difference_in_seconds := trunc(time_difference_in_seconds, 4);

time_difference_in_minutes := time_difference * 1440; --24*60
time_difference_in_minutes := trunc(time_difference_in_minutes, 4);

dbms_output.put_line('Start Time : ' ||
to_char(start_date, 'DD.MM.YYYY HH24:MI:SS'));
dbms_output.put_line('End Time : ' ||
to_char(end_date, 'DD.MM.YYYY HH24:MI:SS'));
dbms_output.put_line('Difference in seconds :' ||
time_difference_in_seconds);
dbms_output.put_line('Difference in minutes :' ||
time_difference_in_minutes);
END;
Çıktı ise
Start Time : 09.09.2006 09:57:49
End Time : 09.09.2006 09:59:34
Difference in seconds :105
Difference in minutes :1,75
Fark alma işleminin birçok yolu bulunmaktadır. Ben sadece birini aktardım.