Today I'd like to post brief summary about the handling of datetime values within the Oracle database. This is a topic which is relevant for every developer.
The first question is whether to use DATE or TIMESTAMP. TIMESTAMP is the more recent datatype for datetime values with more functionality. But there are many tools or programs which cannot handle TIMESTAMP values properly - therefore DATE is still more often being used (even on 10g or 11g systems). But this is not really a problem - DATE might be as suitable as TIMESTAMP. First we should work out the differences between DATE and TIMESTAMP, or expressed otherwise: What are the "features" of the TIMESTAMP datatype?

The smallest unit of a DATE value is a second. TIMESTAMP instead handles Milliseconds.

TIMESTAMP can handle time zones. There is timezone-aware variant: TIMESTAMP WITH TIME ZONE. And there are SQL functions like FROM_TZ which are useful for converting datetime values between timezones.

The hexadecimal notation for "217,7" is "D9","07". This as a 16bit number would be "07D9" and this converted back to decimal is "2009". We see that TIMESTAMP and DATE store the individual information for years, months, days, hours, minutes and seconds.

DATE allows datetime arithmetic as well as TIMESTAMP. One can work with numeric values as well as with interval types. The latter ones are (as we will see) more recommended. The "Unit" of DATE and TIMESTAMP is a "day", so "one DATE" means "one day". One hour is therefore 1/24 "DATE" and a minute is 1/1440 "DATE". If you like to know the datetime interval between two DATE values you just subtract one from the other.

When using the DATE datatype, the result is being converted into a NUMBER value; the UNIT is (as seen above) "days". So this result means: a bit more then 62 days. When using the TIMESTAMP datatype, the result is being expressed as an interval data type (here: Day-To-Second).

... but there is a rounding problem now. It would be much better to omit this roundtrip to the NUMBER value. The following syntax retrieves the result of the datetime arithmetic directly as an interval datatype - The DATE subtraction now returns the same result as the TIMESTAMP subtraction.

So in SQL*Plus we can retrieve the results in a readable format - for DATE as well as for TIMESTAMP values. But application developers often need structured access to the information in order to retrieve the individual information elements. The EXTRACT function allows this:

When it's about adding or subtracting from a datetime value, there is -again- the choice between using numeric (fractional) or interval values. So if we'd like to add 12 hours to a datetime value we could use the fractional method ...

... which works with DATE as well as with TIMESTAMP. But in the latter case there was an implicit conversion from TIMESTAMP to DATE (check with the DUMP function). And when it's about adding minutes or seconds it's obvious that we'll run into rounding issues again. So adding interval values is the better choice.

Now, as the last step we'd like to combine the two: We want to know how many years, months, days, hours, minutes and seconds are between two datetime values. For years and months we use the Year-To-Month interval type, we subtract this from the least datetime value and finally we use the Day-To-Second interval for the remainder. Check out the following PL/SQL code.

SQL Developer is extensible by default - this is known widely. But only a few know that it is not required to implement java code for this. Database-centric (SQL and PL/SQL-based) extensions can be implemented in a declarative manner with by XML files. The following articles describe the process.

Just download the zip archive and try the extensions - just navigate in SQL Developer to Tools then to preferences. In the preference window go to database and user defined extensions. Register the files in the downloaded ZIP archive there. The filename contains a hint whether to register the file as NAVIGATOR, EDITOR or ACTION. After that, restart SQL Developer and have a look ...

APEX Workspace Management Extension
The navigation tree contains now an entry APEX Workspaces; you have to be connected as SYS. I tested this extension with APEX 3.1 and APEX 3.2. Note that there are also some features in the navigator tree entries' context menus.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.