In today's work, learned the following points of knowledge:
The difference between date and timestamp
The date type is an Oracle-commonly used datetime variable, and his time interval is seconds. Two date type subtraction is a two-time interval, noting that the unit is "days". For example: Check the current distance from the opening of the London Olympics:
Select To_date (' 2012-7-28 03:12:00 ', ' yyyy-mm-dd hh24:mi:ss ')-sysdate from dual
The result: 92.2472685185185 days, then you can calculate the interval you want according to the corresponding time! This result may be useful to programmers, and for those who want to see the results directly, this number is not very intuitive, so it leads to the timestamp type
Timestamp is a date-type extension that can be accurate to fractional seconds (fractional_seconds_precision), 0 to9, and 6 by default. Two timestamp subtract words, can not directly get the days of the book, but to get, how many days, how many hours, how many seconds, such as: the same look at the current distance from the opening of the London Olympic Games how long:
Select To_timestamp (' 2012-7-28 03:12:00 ', ' yyyy-mm-dd hh24:mi:ss ')-systimestamp from dual
The result: +000000092 05:51:24.032000000, a little intercept, you can get 92 days 5 hours, 51 minutes, 24 seconds, so users look 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 date type, and then directly subtract.
The conversion between date and timestamp can be transformed by To_char:
Timestamp-->date:select to_date (To_char (Systimestamp, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd Hh24:mi:ss ') from dual
Date-->timestamp:select To_timestamp (To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd Hh24:mi:ss ') from dual
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------------
The difference between date and timestamp in Oracle if you want to store date and time information in Oracle, you actually have a choice of two field data types, let's look at the differences between these two data types and what they provide.
Date data type
We are so familiar with this data type that we think of the date type when we need to represent the dates and the Times. It can store months, years, days, centuries, hours, minutes and seconds. It is typically used to indicate when things have happened or will happen. The problem with the date data type is that it represents a metric granularity of two events that occur at intervals of seconds. This issue will be resolved when the article is discussed later in timestamp. You can use the To_char function to wrap the date data in a traditional way to represent 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
The trouble for most people I see is to calculate the number of years, months, days, hours, and seconds between two time intervals. What you need to understand is that when you do a two-date subtraction, you get the number of days. You need to multiply the number of seconds per day (1 days = 86,400 seconds) and then you can calculate the number of intervals you want again. Here is my solution to accurately calculate the interval between two times. I understand that this example can be a little shorter, but I want to show all the numbers to emphasize the method of 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,
9 Trunc ((((86400* (date2-date1)/60)/60) days,
Trunc (((((((((86400* (date2-date1)/60)/60)/7)/24
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 types
The main problem with the date data type is that it does not have enough granularity to differentiate between two events which occur first. Oracle has extended the timestamp data type on the date data type, which includes information about the month and day of all date data types, and includes fractional seconds. If you want to convert the date type to a 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 ". 000000" at the end of the converted period. This is because when converting from date, there is no information for fractional seconds, the default is 0. and the display format is displayed in the default format of the parameter Nls_timestamp_format. When you move data from a Date Type field in one table to the timestamp Type field in another table, you can write the Insert SELECT statement directly, and Oracle will automatically convert it 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 the timestamp data is the same as the date data. Note that the TO_CHAR function supports date and timestamp, but Trunc does not support timestamp data types. This is a clear indication that the use of the timestamp data type is more accurate than the date data type when two time differences are extremely important.
If you want to display timestamp's fractional seconds information, 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 example above, I only realized the contents of 3 bits after the decimal point.
It is easier to calculate the data differences between timestamp than the old date data type. When you subtract directly, see what happens. The results will be easier to understand, with the first line 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
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 there is no need to worry about how many seconds a day is in the calculation of trouble. Therefore, the number of days, months, days, hours, minutes, and seconds will be the thing to extract numbers with the SUBSTR function.
System date and Time
To get the system time, return to the date data type. You can use the Sysdate function.
Sql> SELECT sysdate from DUAL;
To get the system time, return to the timestamp data type. You can use the Systimpstamp function.
Sql> SELECT Systimestamp from DUAL;
You can set the initialization parameters fixed_date specify the Sysdate function to return a fixed value. This is used in the test date and time-sensitive code. Note that this parameter is not valid for the Systimestamp function.
sql> ALTER SYSTEM SET fixed_date = ' 2003-01-01-10:00:00 ';
System altered.
Sql> select Sysdate from dual;
Sysdate
---------
01-jan-03
Sql> select Systimestamp from dual;
Systimestamp
---------------------------------------------------------
09-jul-03 11.05.02.519000 am-06:00
When using the date and timestamp types, the selection is very clear. You are free to dispose of the date and timestamp types. When you try to switch to a more powerful timestamp, it's important to note that they have a similar place, a different place, and enough to cause damage. The two have advantages in both brevity and spacing, please choose wisely.
Oracle Date and Timestamp differences
