Ask to use the extract function to calculate someone's birthday?

leoliny

Ask to use the extract function to calculate someone's birthday?

idpublic

lz
5, extract () find the field value of date or interval value

Date_value: = extract (date_field from [datetime_value | interval_value])

SQL> select extract (month from sysdate) "This Month" from dual;

This Month

----------

11

SQL> select extract (year from add_months (sysdate, 36)) "3 Years Out" from dual;

3 Years Out

-----------

2006

SQL>

idpublic

zt
Master time-interval data in Oracle
Author: Bob Watkins
2006-08-01 10:38 AM
 
  

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

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

In Oracle 9i, according to the SQL 99 standard, time interval data INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND were added, which together with several other data types make the time processing more accurate. Data types such as TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE all accurately express the time to a fraction of a second, and the latter two also solve the time change caused by geographical location.

In SQL and PL / SQL, you can use time-interval data, they are specified in the same way:

INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
For exact values, default values ​​are specified: year and day are two digits, and fractions of a second are six digits.

The size of the interval is represented by INTERVAL, followed by an expression enclosed in single quotes, and the text used to explain the expression. When using YEAR TO MONTH to indicate the size of the interval, use a hyphen (-) between the year and month. DAY TO SECOND indicates that the time interval should be connected by a space between the day and time. For example, here is how to express the time interval of 2 years and 6 months:

INTERVAL '2-6' YEAR TO MONTH
The following example represents 3 days, 12 hours, 30 minutes, and 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 to or subtracted from various TIMESTAMP data types to get a new TIMESTAMP data type. You can also add and subtract between them to get a new time interval.

Listing A shows 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 can report the total duration and average duration without the need to convert between the original unit seconds.

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

EXTRACT (timepart FROM interval_expression)
Listing B shows an example of this approach.

Listing 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, 31 minutes
First, extract the number of days from the experiment_duration column, and the text "Days" is associated with it. The operation is the same for the hours and minutes of the experiment duration.

Editor-in-chief: Zhang Huan

View international sources for this article

guess12300

up