Working with Dates in Oracle

Oracle DATE data type could be used to store both date and time parts. Does this mean it always stores both date and time parts or do we have to explicitly store time part? If it automatically stores time part, why time part is not displayed when I query dates in SQL developer or SQL plus? Can I use arithmetic functions directly with date fields? If yes, does the result contains value in days or months or years? How do we extract a specific part (i.e. day or year or secs etc) from date field?

This article is aimed to answer most of these questions.

Storage

Oracle DATE data type by default stores both date and time parts.  

The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

While inserting data, if time part is ignored then it will store zeros (i.e.  00:00:00 A.M.) in time part.  Remember, DATE data type does not store nano seconds.. if we need fraction of seconds to be stored, then we need to use timestamp.

We could use DUMP function to get the storage details

select dump (sysdate) from dual;

DUMP(SYSDATE)
_________________________________________
Typ=13 Len=8: 7,218,11,26,14,21,24,0

The above result indicates that data is stored as 8 bytes field.

Display

Oracle DATE fields are displayed as per the NLS_DATE_FORMAT settings in oracle database.

select * from SYS.NLS_DATABASE_PARAMETERS where parameter='NLS_DATE_FORMAT';

To display date in different format, change the value for NLS_DATE_FORMAT

Ex. alter session set nls_date_format='DD/MM/YYYY HH.MI.SS';

Or

dbms_session.set_nls('nls_date_format', 'DD/MM/YYYY HH.MI.SS');

For changing at database level, use ALTER SYSTEM.

Date Arithmetic Functions

In order to understand how dates could be used in the arithmetic functions like add, multiple etc.. we need to first understand what data types are accepted on arithmetic functions and also oracle data type internal conversions.

Excerpt from Oracle documentation:

You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype.

Since dates are stored as numbers in the oracle database, we can perform add/subtract arithmetic functions on dates, but multiply/divide are not supported on dates.

When two dates are used in binary +/- operations, the results will be displayed as number of days. If date fields also have time part then result will also have fraction of days (ex 2.33 , 3.44 etc). If we want the result as complete integer value, then we will have to truncate the time part using TRUNC function. The reason for result being number of days (with datatype as integer) is, the dates are stored as days (with data type as number) internally.

Therefore, apart from able to perform +/- on two dates, we can also perform +/- between date and integer (representing number of days).

Important date functions

EXTRACT

  This date function could be used to extract date or time part from fileds with datetime or interval data types

  Ex: select extract(month from sysdate) from dual;

ADD_MONTHS

   This function returns date plus interger months… ex.. add_months(sysdate,1) will return next month date

INTERVAL

   We have both INTERVAL date function and INTERVAL date data type. Both cases, it is a means of dealing with date/time intervals. For example if we want to store year to month interval (ex 2 years 2 months) then we can added column with data type as INTERVAL YEAR [(year_precision)] TO MONTH.

Similarly if we want to just increate the date by 20 seconds then we can use interval function as

sysdate + INTERVAL '20' SECOND

try below query

select to_char(sysdate,'YYYY/MM/DD HH:MI:SS'),to_char(sysdate + INTERVAL '20' SECOND,'YYYY/MM/DD HH:MI:SS') from dual;

MONTHS_BETWEEN

This function returns number of months between two dates

For example select Months_between('02-FEB-15','01-JAN-15') from dual; returns

1.03225806451612903225806451612903225806

The fraction of value return because the difference is 1 month and 1 day .. this one day is converted to month in result.

ROUND and TRUNC

Both round and trunc date functions do same thing.

TRUNC is meant to truncate the time part and ROUND is meant to round the date to nearest day/year or month etc..

For example if the date field has “2016/06/21 07:53:35” then TRUNC function returns 21-JUN-16 but ROUND function return 22-JUN-16.

You can see the difference in these functions if we do not specific the format string and time part in date field is after 12:00 noon. Otherwise both these functions return same result.

We can use ROUND and TRUNC function to return the first day of the year (i.e. using function as TRUNC(DATE,YEAR)) similarly month and week etc..

Does the commit in called program commits transactions in calling program or main procedure?

Yes, in normal process. But if we do not want this, then we have to process called program or child program transactions as autonomoustransactions.

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or roll back, without committing or rolling back the main transaction

To start an autonomous transaction, we need to use AUTONOMOUS_TRANSACTION pragma (compiler directive) at the top of the section.

Autonomous Transactions Vs Nested transactions

By default, if the main procedure/function shares its context with nested/child procedures/functions. We can create autonomous transactions using the AUONOMOUS_TRANSACTION pragma. This will mark the nested transaction as autonomous hence this autonomous transaction will run in its own context.

Therefore, autonomous transactions

·       Do not share resources such as locks with main transactions

·       Do not depend on main transactions

·       Its committed transactions are visible to other transactions immediately

·       When an autonomous transaction calls another non-autonomous transaction, then this non-autonomous will become nested therefore this child will share same context as parent autonomous transaction

Other useful reading related this

http://www.orafaq.com/node/1915

Different types of SQL statements

 

DDL -  Data Definition Language

These are the statements that are used to define the database structure or schema

i.e. CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

DML - Data Manipulation Language 

These are the statements are used for managing data within schema objects.

i.e. SELECT , INSERT, DELETE , UPDATE, MERGE

DCL -  Data Control Language

These are GRANT and REVOKE

TCL - Transaction Control Language

These are COMMIT, ROLLBACK,  COMMIT, SAVEPOINT, SET TRANSACTION

SCL - Session Control Language

i.e ALTER SESSION statements

Oracle : Aggregate cum Analytical Functions

 

I always remembered Max, Min, Avg, Sum, Count as aggregate functions. But actually these are also analytical functions

analytical functions will have following syntax

{analytical function} (arguments) OVER (analytical clause)

Aggregate functions with OVER clause will become analytical functions.. could become more useful thank they already are.

for example AVG could be used as

SELECT AVG(salary) "Average"

  FROM employees;

also as

SELECT manager_id, last_name, hire_date, salary,

       AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date

  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg

  FROM employees

  ORDER BY manager_id, hire_date, salary;

Some of the analytical functions used in my experience are

1. LEAD  - provides access to a row at a given physical offset beyond that position.. for example, we can use this function to display current row value and next row value of same field in single row


2. LAG -- Similar to LEAD, but gives access to previous row information


3. ROW_NUMBER


4. RANK


5. DENSE_RANK