How To Use A TIMESTAMP In Oracle

This help is based on examples so it would be easier to understand. The Oracle Timestamp is more accurate than Oracle Date and it stores an area location stamp. There are two main reasons why programmers are using the Oracle Timestamps. The location stamp is important because the same time in hours can mean different moments in different places on the world. For example a moment of at 11 am in London does mean 6 am in New York, so using Oracle Date the same moment can happen in different hours. Timestamp in Oracle can solve the problem and it converts the time number depending on the area location. The second reason is that we do have more exact time as the timestamps are storing smaller amounts of time units than a date and this could be important to follow the transactions and the database processes.

The first example is with Oracle timestamp we are going to use function Oracle CURRENT_TIMESTAMP and the difference between mostly used Oracle SYSTIMESTAMP is that the Oracle CURRENT_TIMESTAMP shows time location in words. The CURRENT_TIMESTAMP is used to show us – to people more readable way the Oracle timestamp value.

SELECT CURRENT_TIMESTAMP 
  FROM DUAL;

The next Oracle DBA example is with Oracle SYSTIMESTAMP and as you see the time location has changed to the GMT number +1 that is the same as “Europe/London” on the last example.

SELECT SYSTIMESTAMP
   FROM DUAL;

To take out or extract some value from Oracle Timestamp you can use Oracle Extract function. Using this function you can extract for example a SECOND, HOUR, MINUTE, DAY, MONTH or YEAR value using the same keywords. The syntax of Oracle Extract is :

EXTRACT(<extracting_value_name> FROM <timestamp_type>)

On the following example with Oracle Extract we will take out a day number and as this moment it was April 11th 2013 the function returns number 11.

SELECT EXTRACT(DAY FROM SYSTIMESTAMP) AS Timestamp_Day
   FROM DUAL;

To convert an Oracle Timestamp value to a date you can use the Oracle Trunc function. The function not only will turn the Timestamp to a date but also removes all time zone stamp values. You can truncate date to minutes (“MI“), hours (“HH“), DAY, MONTH or YEAR to do it use the coloured keyword.

TRUNC(<timestamp_value>,<date_model_name>)

The fourth example with Oracle Trunc and without any additional parameter will truncate the timestamp from all time value. Take a look at the example below how all time values are set to zeros.

SELECT TRUNC(SYSTIMESTAMP) AS Truncated_Timestamp
   FROM DUAL;

The next example with Oracle Trunc we will set the truncation limit up to minutes (“MI“). The Oracle Trunc function will truncate from Timestamp all values that are smaller than minutes and all time area stamps will be included into the removed attribute list.

SELECT TRUNC(SYSTIMESTAMP,'MI') AS Truncated_Timestamp
   FROM DUAL;

Another way to convert Oracle Timestamp to a date without truncating anything is to use Oracle Cast function. To cast different values to other built-in types you can apply it to Oracle Timestamp too. The syntax of Oracle Cast is following.

CAST(<timestamp_value> AS <new_value_name>)

On the following Oracle Cast example the Timestamp will be converted to Oracle Date type. Take a look at the following SQL query output the all time values (including seconds) have remained in the date type.

SELECT CAST(systimestamp AS DATE) AS THE_DATE
   FROM DUAL;

To know more deep about the Oracle Timestamp attributes take a look at function Oracle Dump. The Dump function returns description of timestamp where you can find in present the data type code, length in bytes, and internal representation of expr. Those attributes can be useful when you need to check if your timestamp value has been converted correctly.

DUMP(<timestamp_value>)

The example above demonstrating the output of Oracle Dump function with Oracle Timestamp.

SELECT DUMP(SYSTIMESTAMP)
   FROM DUAL;

Also it is important to know that using Oracle Timestamp in Oracle database table may cause problems for some applications. For example application Oracle Forms belongs to the same company as Oracle database but Oracle Forms would not compile when table is using Timestamp type columns. In this case the Timestamp should be stored in VARCHAR2 and using Oracle Cast function can be returned to Oracle Timestamp type. Still if it’s possible keep the Oracle Timestamp values in their own type to avoid an extra work of converting types from other built-in types to Oracle Timestamp.

See Also:
Oracle Select Oracle Extract Oracle Trunc Oracle Home