Comparison of oracle date and TIMESTAMP Data Types

 

DATE data type

　　

We are very familiar with this data type. We will always think of the date type when we need to represent the date and time. It can store months, years, days, centuries, hours, minutes, and seconds. It is typically used to indicate when something has happened or is about to happen. The problem with the DATE data type is that it indicates that the interval between two events is measured in seconds. This problem will be solved later in the article when discussing timestamp. You can use the TO_CHAR function to traditionally wrap the DATE data to express it in multiple formats.

　　

SQL> SELECT TO_CHAR (date1, 'Mm/DD/YYYY HH24: MI: ss') "Date" FROM date_table;

　　

Date

　　

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

　　

06/20/2003 16:55:14

　　

06/26/2003 11:16:36

　　

Most people I see are troubled by calculating the number of years between two periods, the number of months, the number of days, the number of hours, and the number of seconds. What you need to understand is that when you perform the subtraction operation on two dates, you get the number of days. You need to multiply the number of seconds per day (1 day = 86400 seconds), and then you can calculate the expected number of intervals again. The following is my solution. We can accurately calculate the two time intervals. I understand that this example can be shorter, but I want to show all the numbers to emphasize the calculation method.

　　

1 SELECT TO_CHAR (date1, 'mmddyyyy: HH24: MI: ss') date1,

　　

2 TO_CHAR (date2, 'mmddyyyy: HH24: MI: ss') date2,

　　

3 trunc (86400*(date2-date1 ))-

　　

4 60 * (trunc (86400 * (date2-date1)/60) seconds,

　　

5 trunc (86400*(date2-date1)/60 )-

　　

6 60 * (trunc (86400 * (date2-date1)/60)/60) minutes,

　　

7 trunc (86400*(date2-date1)/60)/60 )-

　　

8 24 * (trunc (86400 * (date2-date1)/60)/60)/24) hours,

　　

9 trunc (86400 * (date2-date1)/60)/60)/24) days,

　　

10 trunc (86400 * (date2-date1)/60)/60)/24)/7) weeks

　　

11 * FROM date_table

　　

DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS

　　

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

　　

06202003: 16: 55: 14 07082003: 11: 22: 57 43 27 18 17 2

　　

06262003: 11: 16: 36 07082003: 11: 22: 57 21 6 0 12 1

　　

TIMESTAMP Data Type

　　

The main problem with the DATE data type is that its granularity cannot determine which of the two events occur first. ORACLE has extended the TIMESTAMP data type in the DATE data type, which includes information about all DATE data types, such as year, month, day, hour, minute, and second, and contains information about decimal seconds. If you want to convert the DATE type to the TIMESTAMP type, use the CAST function.

　　

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

　　

Date

　　

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

　　

20-JUN-03 04.55.14.000000 PM

　　

26-JUN-03 11.16.36.000000 AM

　　

As you can see, there is a section ". 000000" at the end of the converted time period ". This is because there is no decimal second information during the conversion from date. The default value is 0. The display format is based on the default format specified by the NLS_TIMESTAMP_FORMAT parameter. When you move the data in the date Field of a table to the timestamp field of another table, you can directly write the insert select statement. oracle will automatically convert the data for you.

　　

1 SELECT TO_CHAR (time1, 'Mm/DD/YYYY HH24: MI: ss') "Date" FROM date_table

　　

Date

　　

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

　　

06/20/2003 16:55:14

　　

06/26/2003 11:16:36

 

The formatted display of TIMESTAMP data is the same as that of DATE data. Note that the to_char function supports date and timestamp, but trunc does not support the TIMESTAMP data type. This clearly shows that the use of TIMESTAMP data type is more accurate than the DATE data type when the difference between the two time is extremely important.

　　

If you want to display the fractional seconds of TIMESTAMP, refer to the following:

　　

1 SELECT TO_CHAR (time1, 'Mm/DD/YYYY HH24: MI: SS: ff3') "Date" FROM date_table

　　

Date

　　

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

　　

06/20/2003 16: 55: 14: 000

　　

06/26/2003 11: 16: 36: 000

　　

In the preceding example, only three digits after the decimal point are allowed.

　　

It is easier to calculate the data difference between timestamp than the old date data type. If you subtract it, you can see what will happen. The result is easier to understand, 17 days, 18 hours, 27 minutes, and 43 seconds in the first line.

　　

1 SELECT time1,

　　

2 time2,

　　

3 substr (time2-time1), instr (time2-time1), '') +) seconds,

　　

4 substr (time2-time1), instr (time2-time1), '') +) minutes,

　　

5 substr (time2-time1), instr (time2-time1), '') +) hours,

　　

6 trunc (to_number (substr (time2-time1), 1, instr (time2-time1, '') days,

　　

7 trunc (to_number (substr (time2-time1), 1, instr (time2-time1, '')/7) weeks

　　

8 * FROM date_table

　　

TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS

　　

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

　　

06/20/2003: 16: 55: 14: 000000 07/08/2003: 11: 22: 57: 000000 43 27 18 17 2

　　

06/26/2003: 11: 16: 36: 000000 07/08/2003: 11: 22: 57: 000000 21 06 00 12 1

　　

This means that you no longer need to worry about how many seconds a day is in troublesome computing. Therefore, getting the number of days, months, days, hours, minutes, And seconds is the use of the substr function to extract numbers.

　　

System Date and Time

　　

To obtain the system time, the Data Type of date is returned. You can use the sysdate function.

　　

SQL> SELECT SYSDATE FROM DUAL;

　　

To obtain the system time, the data type is returned as timestamp. You can use the aggregate impstamp function.

　　

SQL> SELECT distinct imestamp FROM DUAL;

　　

You can set the initialization parameter FIXED_DATE to specify the sysdate function to return a fixed value. This is used in code that is sensitive to test date and time. Note that this parameter is invalid for the systimestamp function.

　　

SQL> ALTER SYSTEM SET fixed_date = '2017-01-01-10:00:00 ';

　　

System altered.

　　

SQL> select sysdate from dual;

　　

SYSDATE

　　

---------

　　

01-JAN-03

　　

SQL> select distinct imestamp from dual;

　　

SYSTIMESTAMP

　　

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

　　

09-JUL-03 11.05.02.519000 AM-06:00

　　

When the date and timestamp types are used, the selection is clear. You can handle the date and timestamp types at will. When you try to convert to a more powerful timestamp, You need to note that they both have similarities and differences, which is enough to cause damage. The two have their own advantages in conciseness and interval size. Please select a proper one