How to master Oracle time interval data

Welcome to the Oracle community forum and interact with 2 million technicians before the 9i version. Oracle has no built-in method to record the passage of time. Data of the DATE type is used to record individual time points. To express a time amount (that is, an interval), the Database Designer must convert the time interval to the original unit of seconds, and then use

Welcome to the Oracle community forum and interact with 2 million technicians> before the 9i version, Oracle has no built-in method to record the passage of time. Data of the DATE type is used to record individual time points. To express a time amount (that is, an interval), the Database Designer must convert the time interval to the original unit of seconds, and then use

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Before 9i, Oracle did not have a built-in method to record the passage of time. Data of the DATE type is used to record individual time points. To express a time amount (that is, an interval), the Database Designer must convert the time interval to the original unit of seconds, then, use a NUMBER column to save it.

Although the NUMBER data type can accurately represent time in seconds, it makes time calculation very difficult. For example, 60 seconds is 1 minute, 60 minutes is 1 hour, and 24 hours is equal to 1 day-these numbers are very bad in a decimal-based number system.

In Oracle 9i, according TO the SQL 99 standard, the time INTERVAL data INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are added, they work with several other data types to make time processing more accurate. TIMESTAMP, timestamp with time zone, timestamp with local time zone, and other data types all accurately express the TIME to several points per second, and the latter two also solve the TIME changes caused by geographical locations.

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 value is: Year and day are two digits, and one second is six digits.

The size of the INTERVAL is represented by INTERVAL, followed by an expression placed in single quotes, and the text used to explain the expression. Use year to month to indicate that the time interval between the hour and MONTH must be connected by a hyphen. Day to second indicates that a space is used TO connect the time interval between DAY and time. For example, the following is a representation of the interval of two years and six months:

INTERVAL '2-6 'YEAR TO MONTH

The following example indicates 3 days, 12 hours, 30 minutes, 6.7 seconds:

INTERVAL '3 12:30:06. 7' day to second (1)

The time interval can be positive or negative. They can be added or subtracted from various TIMESTAMP data types to obtain a new TIMESTAMP data type. You can also perform addition and subtraction between them to obtain a new time interval.

List A describes how to create A table to record the start time and duration of an event, such as an experiment. After the data is collected, the built-in summary function in SQL does not need to convert each other with the original unit in seconds, so that the total duration and average duration can be reported.

List

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 ',

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 model that can be mapped to segments of data types at various time intervals. However, you can use the new EXTRACT function to EXTRACT and merge these fragments. The format is as follows:

EXTRACT (timepart FROM interval_expression)

List B provides an example using this method.

List B

Select extract (day from experiment_duration) |

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

'Urs, '| EXTRACT (minute from experiment_duration) |

'Minutes 'duration

FROM experiment;

DURATION

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

1 days, 2 hours, 31 minutes

First, extract the number of Days from the experiment_duration column. The text "Days" is associated with it. For the hours and minutes of the experiment duration, the procedure is the same as that described above.