Term: TIMESTAMP

Definition:
When storing date and time information in Oracle, there are two basic options for the column's datatype: the DATE datatype and the TIMESTAMP datatype.

The DATE datatype has the ability to store the month, day, year, century, hours, minutes, and seconds. It has a resolution or granularity of 1 second. To distinguish or record events with a separation of less than 1 second, the TIMESTAMP datatype is required.

The TIMESTAMP datatype stores all of the information that the DATE datatype stores, but also includes fractional seconds.

Example of converting a DATE datatype to TIMESTAMP datatype:

SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;

Date

-----------------------------------------

15-APR-03 06.32.41.000000 PM

21-APR-03 12.42.26.000000 AM

Calculating the time difference between two TIMESTAMP datatypes is very straightforward- you just do straight subtraction of the two timestamp values.

When doing subtraction between dates, remember that the result is a value that represents the number of days. Multiply that number by the number of seconds in a day (86400) to determine the interval or value that you want to find.

For more detailed information on dates and timestamps, please see the Related Links below.

Related Links:

Related Code Snippets:

Current_timestamp - The current_timestamp function returns the current date and time in the time z...
TIMESTAMP: List time zones - Produces a list of valid timezones.
TIMESTAMP: Get current timestamp - Produces the current timestamp. Equivalent to NOW() in mySQL.
TIMESTAMP: DBTIMEZONE - The dbtimezone function returns the database time zone as a time zone offs...
TIMESTAMP: DUMP - Returns the number of bytes and datatype of a value (in this case the SYSTIMESTAMP).
TIMESTAMP: EXTRACT - Returns the value of a specified datetime field from a datetime or interval v...
TIMESTAMP: FROM_TZ - The from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIME...
TIMESTAMP: LocalTimestamp - The localtimestamp function returns the current date and time in the t...
TIMESTAMP: SessionTimeZone - The sessiontimezone function returns the current session's time zone...
TIMESTAMP: SYS_EXTRACT_UTC - SYS_EXTRACT_UTC extracts the UTC (Coordinated Universal Time - former...
TIMESTAMP: SysTimeStamp - The systimestamp function returns the current system date and time (incl...
TIMESTAMP: TRUNC - The trunc function returns a date truncated to a specific unit of measure.
TIMESTAMP: TZ_OFFSET - The tz_offset function returns the time zone offset of a value. 'timezo...
SCN_TO_TIMESTAMP - This conversion function takes as an argument a number that evaluates to a syst...
TIMESTAMP_TO_SCN - The TIMESTAMP_TO_SCN function takes as an argument a timestamp value and return...
TO_TIMESTAMP - Converts a character string (CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to a val...
TO_TIMESTAMP_TZ - Converts a string datatype to a TIMESTAMP WITH TIME ZONE datatype. Note: ...