How to master the time-interval data in Oracle

Prior to the 9i version, Oracle had no built-in way to record the passage of time. Date data is used to record individual points of time, but to express a time amount (that is, an interval), the designer of the database must convert the time interval to the original unit seconds and then save it with a number column.

Although number is a data type that can accurately represent time in seconds, it makes the calculation of time difficult. For example, 60 seconds is 1 minutes, 60 Minutes is 1 hours, 24 hours equals 1 days--These numbers are very crappy in a decimal system based on the numbers.

In Oracle 9i, the time interval type of data INTERVAL with the SQL 99 standard is added to MONTH and INTERVAL day to SECOND, which, together with several other data types, make the processing of time more accurate. Data types such as TIMESTAMP, TIMESTAMP with time zone, and TIMESTAMP with the local times zone are accurate to a fraction of a second, and the latter two also address the temporal changes caused by geography.

In SQL and Pl/sql, you can use time-interval data, which are defined in the same way:

INTERVAL year[(year_precision)] to MONTH

INTERVAL day[(day_precision)] to second[(fractional_seconds_precision)]

For exact values, the default values are specified: The year and day are two digits, and the number of points in a second is six digits.

The size of the time interval is represented by interval, followed by an expression placed in single quotes, and the text used to interpret the expression. A hyphen (-) is used to connect between years and months with the year to month, representing the time interval size. The day to second represents a space between days and times when the time interval is sized. For example, the following is a 2-year 6-month time interval representation:

INTERVAL ' 2-6 ' to MONTH

The following example shows 3 days 12个小时30分钟 6.7 seconds:

INTERVAL ' 3 12:30:06.7 ' Day to SECOND (1)

The time interval can be positive, or it can be negative. They can be added or subtracted from various timestamp data types, resulting in a new timestamp data type. They can also do addition and subtraction operations to get a new time interval.

List A shows how to create a table to record the start time and duration of an event, such as experiments. After the data is collected, the summary functions built into SQL do not need to be converted to the original unit seconds to report the total duration and average duration.

List A

CREATE TABLE Experiment

(experiment_id number (9),

Experiment_desc VARCHAR2 (80),

Experiment_start TIMESTAMP,

Experiment_duration INTERVAL Day (1) to SECOND (4)

);

Table created.

INSERT into experiment

VALUES (

1, ' busted urban myth ', ' 01-jun-2006 02:00:00 PM ',

INTERVAL ' 1 2:31:15.1250 ' Day (1) to SECOND (4)

);

1 row created.

Col Experiment_desc format A40

Col experiment_start format A30

Col experiment_duration format A20

SELECT * from experiment;

experiment_id experiment_desc-----------------------------------------------------experiment_start Experiment_ DURATION--------------------------------------------------1 busted urban myth 01-jun-06 02.00.00.000000 PM +1 02:31:15 .1250

--now compute the experiment's ending Time

SELECT experiment_id, Experiment_start,

Experiment_start + experiment_durationexperiment_end

From experiment;

experiment_id experiment_start-------------------------------------------experiment_end------------------------ ---------------------------------------------------1 01-jun-06 02.00.00.000000 pm 02-jun-06 04.31.15.125000000 pm

Unfortunately, the To_char function does not include any format models that can be mapped to fragments of data types at various intervals. However, you can use the new extract function to extract and merge the fragments. The format is as follows:

EXTRACT (Timepart from Interval_expression)

List B gives an example of using this method.

List B

SELECT EXTRACT (Day from experiment_duration) | |

' Days, ' | | EXTRACT (HOUR from experiment_duration) | |

' Hours, ' | | EXTRACT (MINUTE from experiment_duration) | |

' Minutes ' Duration

From experiment;

DURATION

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

1 days, 2 hours, minutes

First of all, the number of days from the Experiment_duration column is extracted, the word "day" is associated with it. For the hour and minute portions of the duration of the experiment, the operation is the same as the previous method.