Shulou(Shulou.com)06/01 Report--

https://www.cnblogs.com/java-class/p/4742740.html1.DATE Data type

This data type is so familiar that we think of date when we need to represent dates and times. 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 represents a measure granularity of seconds between two events. This issue will be addressed later when discussing timestamp. DATE data can be traditionally wrapped for presentation in multiple formats using the TO_CHAR function.

1 SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;2 3 Date 4 --------------------------- 5 06/20/2003 16:55:14 6 06/26/2003 11:16:36

The trouble most people get into is counting the years, months, days, hours, and seconds between two times. What you need to understand is that when you subtract two dates, you get days. You need to multiply by the number of seconds per day (1 day =86400 seconds), and then you can calculate again to get the number of intervals you want. Here is my solution, which can accurately calculate the interval between two times. I understand this example could be shorter, but I'm showing all the numbers to emphasize the calculation.

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) weeks11 FROM date_table12 13 DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS 14 ----------------- ----------------- ---------- ---------- ---------- ---------- ---------- 15 06202003:16:55:14 07082003:11:22:57 43 27 18 17 2 16 06262003:11:16:36 07082003:11:22:57 21 6 0 12 1 back to top 2.TIMESTAMP data types

The main problem with DATE data types is that they are not granular enough to tell which of two events occurs first. ORACLE has extended the DATE data type to TIMESTAMP data type, which includes all DATE data types of the year, day, hour, minute, second information, but also includes the fractional second information. If you want to convert DATE type to TIMESTAMP type, use the CAST function.

1 SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t; 2 Date 3 ----------------------------------------------------- 4 20-JUN-03 04.55.14.000000 PM 5 26-JUN-03 11.16.36.000000 AM

As you can see, there is a ".000000" at the end of the converted time period. This is because when converting from date, there is no fractional second information, and the default is 0. And the display format is displayed according to the default format determined by the parameter NLS_TIMESTAMP_FORMAT. When you move data from a date type field in a table to a timestamp type field in another table, you can write an INSERT SELECT statement directly, and oracle will automatically do the conversion for you.

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table 2 Date 3 ------------------- 4 06/20/2003 16:55:14 5 06/26/2003 11:16:36

TIMESTAMP data is formatted and displayed the same way as DATE data. Note that the to_char function supports date and timestamp, but trunc does not support the TIMESTAMP data type. This has clearly shown that it is more accurate to use the TIMESTAMP data type than the DATE data type when the difference between two times is extremely important.

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table2 3 Date 4 ----------------------- 5 06/20/2003 16:55:14:000 6 06/26/2003 11:16:36:000

Calculating the data differences between timestamps is easier than the old date data type. When you subtract directly, see what happens. The results will be easier to understand, with the first lines of 17 days, 18 hours, 27 minutes and 43 seconds.

1 SELECT time1, 2 time2, 3 substr((time2 - time1), instr((time2 - time1), ' ') + 7, 2) seconds, 4 substr((time2 - time1), instr((time2 - time1), ' ') + 4, 2) minutes, 5 substr((time2 - time1), instr((time2 - time1), ' ') + 1, 2) 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 9 10 TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS 11 ------------------------- -------------------------- ------- ------- ----- ---- ----- 12 06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2 13 06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1

This means that there is no longer a need to worry about how many seconds of the day are involved in troublesome calculations. Thus, getting days, months, days, hours, minutes, and seconds becomes a matter of extracting numbers with the substr function.

Back to Top 3. System Date and Time

To get the system time, return the date data type. You can use the sysdate function.

1 SQL> SELECT SYSDATE FROM DUAL;

To get the system time, return the timestamp data type. You can use the systimpstamp function.

1 SQL> SELECT SYSTIMESTAMP FROM DUAL;

You can set the initialization parameter FIXED_DATE to specify that the sysdate function returns a fixed value. This is used in testing date and time sensitive code. Note that this parameter is not valid for the systemestamp function.

1 SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00'; 2 System altered. 3 4 SQL> select sysdate from dual; 5 SYSDATE 6 --------- 7 01-JAN-03 8 9 SQL> select systimestamp from dual; 10 SYSTMESTAMP 11 ---------------------------------------------------------

The date type is a date variable commonly used by Oracle, and its time interval is seconds. Two dates are subtracted to get the interval of two times, note that the unit is "day." For example, check how long it is until the opening of the London Olympics:

1 select to_date('2012-7-28 03:12:00','yyyy-mm-dd hh34:mi:ss')-sysdate from dual

The result is: 92.2472685185185 days, and then you convert the interval you want according to the corresponding time! This result may be useful to programmers, but for those who want to see the result directly, this number is not very intuitive, so it leads to the timestamp type.

timestamp is an extension of DATE type, can be accurate to fractional seconds (fractional_seconds_precision), can be 0 to9, default is 6. Subtracting two timestamps does not directly yield a book of days, but rather,

How many days, how many hours, how many seconds, etc. Also check how long it is until the opening of the London Olympic Games.

1 select to_timestamp('2012-7-28 03:12:00','yyyy-mm-dd hh34:mi:ss')-systimestamp from dual

The result is: +00000092 05:51: 24.03200000, a little interception, you can get 92 days, 5 hours, 51 minutes, 24 seconds, so that the user looks more intuitive! But this number is not very intuitive for programmers, if you want a specific length of time, and the accuracy is not required to milliseconds, you can convert the timestamp type to the date type, and then subtract directly.

Back to top 5.Conversion between date and timestamp can be done by

to_char to convert timestamp-->date:

1 select to_date(to_char(systimestamp,'yyyy-mm-dd hh34:mi:ss'),'yyyy-mm-dd hh34:mi:ss') from dual

date -->timestamp：

1 select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh34:mi:ss'),'yyyy-mm-dd hh34:mi:ss') from dual

When using date and timestamp types, the choice is clear. You can do whatever you want with date and timestamp types. When you try to switch to a stronger timestamp, be aware that they are similar and different enough to cause damage. Both have advantages in simplicity and spacing size, please choose reasonably.

Author: Orson

Source: http://www.cnblogs.com/java-class/

If you think reading this blog has given you something to gain, you may wish to click on the [Recommendation] in the lower right corner.

If you want to find my new blog more easily, click Follow Me in the lower left corner.

If you are interested in my blog content, please continue to follow my follow-up blog, I am [Orson]

Copyright of this article belongs to the author and blog garden, welcome to reprint, but without the author's consent must retain this paragraph statement, and in the article page obvious position to give the original link, otherwise reserve the right to pursue legal responsibility.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.