Calculation of time difference between date and timestamp of Oracle time type

Oracle has two time types: Date and timestamp. Date, accurate to seconds, and timestamp to milliseconds.

 

1. Calculate the time difference of the date type

 

You can set the year, month, day, hour, minute, and second first.Use the to_char function to split the data and convert it to a value type using the to_number function.With these separate time options, you can easily remove them one by one. Remember to consider unit conversion. For example, convert them to hours or points.

Example:

Declare

V_date date;

V_year int;

V_month int;

V_day int;

V_hour int;

V_minute int;

V_second int;

Begin

V_date: = sysdate;

V_year: = to_number (to_char (v_date, 'yyyy '));

-- The sharding method of month, day, hour, minute, and second is the same as that of the previous year. Just replace yyyy with MM, DD, HH, mi, and SS respectively.

End;

--The time of the timestamp type can also be used in the same way, but this can only be accurate to seconds, and will be ignored in the following milliseconds.

 

2. Calculate the time difference of the timestamp type

 

The to_char function cannot be split into milliseconds. Another function can be used.Function Extract

Example

Declare

V_t timestamp;

V_year int;

V_s float;

Begin

V_t: = policimestamp;

 V_year: = extract (year from v_t );

-- The split method of month, day, hour and minute is the same as that of the year above. You only need to change year to month, day, hour, and minute.

V_s: = extract (second from v_t); -- note that although only the seconds can be split here, the seconds here contain the decimal point and the decimal point is represented in milliseconds.

End;