oracle difference in the date and timestamp

2010-10-22  来源:本站原创  分类:Database  人气:130 

If you want to store in the ORACLE DATE and time information, it is actually composed of two field data types of your choice, then, let us look at the difference between these two data types and what they offer.

DATE data type

This data type is too familiar to us, when we need to show the date and time, then think of date type. It can store month, year, day, century, hours, minutes and seconds. It is typically used to indicate when something has occurred or will occur. DATE data type of problem is that it represents the time interval between two events is a measure of granularity of seconds. This issue will be discussed later in the article when the timestamp has been resolved. DATE TO_CHAR function can use the data to traditional packaged to achieve the purpose that a variety of 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

I saw most of the people into trouble is to calculate the time interval between the two the number of years, months, days, hours and seconds. You need to understand is that when you subtract the two dates when the operation is to be a number of days. You need to multiply the number of seconds per day (1 day = 86400 seconds), then you can re-calculate the number of intervals you want. Here is my solution, you can accurately calculate the two time intervals. I understand that some of this example can be more brief, but I was to show that all the figures 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)) 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 432,718,172
06262003:11:16:36 07082003:11:22:57 2,160,121

TIMESTAMP data type

DATE data type is not the main problem is its size enough to differentiate the two events which occurred first. ORACLE DATE data type has been extended from the TIMESTAMP data type, which includes all the DATE data type, year, month, day, hour information, but also the small matter of seconds the information. If you want to convert the TIMESTAMP type of DATE 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, in the transition period after the tail has a ".000000." This is because the conversion over from the date when the information is not a small matter of seconds, the default is 0. And the display format determined in accordance with the default parameters NLS_TIMESTAMP_FORMAT format. When you type a date in the table to another table field in the timestamp data type field in the go, you can directly write INSERT SELECT statement, oracle will automatically convert 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

TIMESTAMP DATE data display and data in the same format. Note, to_char function to support date and timestamp, but does not support the TIMESTAMP data type trunc. This has clearly demonstrated the difference in time when two extremely important cases, the use TIMESTAMP data type is more precise than the DATE data type.

TIMESTAMP if you want to display small seconds, 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 reality after the decimal point three of the content.

Calculated timestamp difference between the data date older than the type of data easier. When you subtract directly, then see what happens. The results will be more easy to understand, the first line of the 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 432,718,172
06/26/2003: 11:16:36:000000 07/08/2003: 11:22:57:000000 210,600,121
This means that one day no longer need to care about the number of seconds in the troubles of the calculation. Thus, by the number of days, months, days, hours, minutes and seconds to be extracted with substr function figure things out.

System date and time

In order to get the system time, to return into the date data type. You can use the sysdate function.

SQL> SELECT SYSDATE FROM DUAL;

In order to get the system time, to return into the timestamp data type. You can use the systimpstamp function.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

You can set the initialization parameter FIXED_DATE specify sysdate function returns a fixed value. This is used in the test date and time-sensitive code. Note that this parameter is not valid for 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 type, the choice is clear. You are free to dispose of date and timestamp types. When you try to switch to more powerful when the timestamp, you need to note where they are both similar and more different places, but enough to cause damage. Granularity both in terms of simplicity and have their own advantages, make a reasonable choice.

相关文章
  • oracle difference in the date and timestamp 2010-10-22

    If you want to store in the ORACLE DATE and time information, it is actually composed of two field data types of your choice, then, let us look at the difference between these two data types and what they offer. DATE data type This data type is too f

  • MySQL, the difference between DATETIME.DATE and TIMESTAMP 2010-12-24

    DATETIME, DATE and TIMESTAMP types are similar. This description of their characteristics and their similarities and differences. DATETIME need to contain both types can be used for date and time information value. MySQL to retrieve and display DATET

  • Java中Date,Calendar,Timestamp的区别以及相互转换与使用 2015-04-01

    以下是对Java中Date,Calendar,Timestamp的区别以及相互转换与使用进行了详细的介绍,需要的朋友可以过来参考下 1 Java.util.Date包含年.月.日.时.分.秒信息. // String转换为Date String dateStr="2013-8-13 23:23:23"; String pattern="yyyy-MM-dd HH:mm:ss"; DateFormate dateFormat=new SimpleDateFormat(

  • oracle difference in date and timestamp 2010-10-22

    If you want to store in ORACLE DATE and time information, it is actually composed of two field data types of your choice, then, let us look at the difference between these two data types and what they provide. DATE data type This data type is too fam

  • Oracle on the time / date of action 2010-05-12

    Oracle on the time / date of action 1. Date Time interval operation The current time minus 7 minutes Java code select sysdate, sysdate - interval '7 'MINUTE from dual select sysdate, sysdate - interval '7 'MINUTE from dual The current time less the t

  • [Oracle] to_date () and to_char () Date and string conversion 2011-01-13

    [Oracle] to_date () and to_char () Date and string conversion to_date ("The string to convert," "convert format"), two parameters must match the format, otherwise it will error. The second parameter is the format in accordance with the

  • and oracle in java format date in a different part of the 2010-07-23

    Format is the same as the date format java code: Date of transfer string: new SimpleDateFormat ('yyyy-MM-dd'). format (new Date ()); Turn date string new SimpleDateFormat ("yyyy-MM-dd"). parse (src); oracle in: Date of transfer string: SELECT to

  • Oracle difference between keywords and reserved words 2011-05-25

    Oracle keywords and reserved words there is a difference. In some cases, keywords can be used. For example, ROLE can be used for table or field names. However, reserved words can not. Oracle has to know which keywords and reserved words in addition t

  • Mysql and Oracle difference 2010-05-29

    Friday went to a company to interview the manager asked that Mysql and Oracle on the issue of the distinction, not previously concluded, the answer is not very good, but seat of your pants, first, the following conclusions: 1. Oracle is a large datab

  • The java in the Date, String, Timestamp conversion problem between 2010-06-17

    1. Access to the system current time: 1. System.out.println (new Timestamp (new java.util.Date (). GetTime)); / / contains the minutes and seconds 2. System.out.println (new java.sql.Date (new java.util.Date (). GetTime)); / / does not contain the mi

  • Java in the Date and Timestamp 2010-08-02

    Order one: Here's an article explaining my personal view is very detailed, the use of the java.sql.Date still puzzled look. java.sql.Date stores only date data is not stored time data / / Time data will be lost preparedStatement.setDate (1, new java.

  • Summarize the Oracle database on the date of the operation 2010-11-08

    Oracle on the date of operation is quite flexible, before a brief summary of the time, only the most commonly used to write out some of this increase collected from the network statement. --1. In the system time based on the delay of 5 seconds select

  • oracle query on the date of 2010-11-25

    http://topic.csdn.net/t/20060311/17/4608256.html select * from mytable where to_char (install_date, 'YYYYMMDD')> '20050101 ' select * from mytable where install_date> to_date ('20050101 ',' yyyymmdd '); Get the current date is the first few weeks of

  • MySql SqlServer Oracle difference between the three? (Rpm) 2010-09-21

    For application developers, currently the most popular of the two back-end database is MySQL and SQLServer. The most basic similarity is that both data storage and query system are. You can use the sql database to access data from these two because t

  • Summarize the Oracle database on the date of operation 2010-11-08

    Oracle on the date of operation is quite flexible, simple summary of the previous one, only the most commonly used to write out some of this increase to be collected from the network statement. --1. In the system time on the basis of delay 5 seconds

  • Computing time to write your own function DateDiff Oracle difference 2011-08-17

    / ** * @ Author: wangxl * Calculate the difference between two time * / CREATE OR REPLACE FUNCTION DATEDIFF (I_DATE1 IN VARCHAR, I_DATE2 VARCHAR) RETURN VARCHAR AS TOTAL_SECONDS NUMBER; O_RESULT VARCHAR2 (50); DAYS NUMBER; HOURS NUMBER; MINUTES NUMBE

  • Java中Date及Timestamp时间相关内容[转] 2012-03-07

    Java SQL DAO java.util.date java.sql.date java.sql.timestamp 整理一: 这里的一片文章,我个人认为讲解的很详细,有对 java.sql.Date的使用还有困惑的请看. java.sql.Date 只存储日期数据不存储时间数据 // 会丢失时间数据 preparedStatement.setDate(1, new java.sql.Date(date.getTime())); //可以这样来处理 preparedStatement.set

  • hibernate 关于date 和Timestamp 2013-04-01

    在hibernate使用保存系统的时间,对javaBean使用了 date类型,发觉它不能保存时分秒,就使用了timestamp就能保存时分秒了. java.util.Date datetime=new java.util.Date(); java.sql.Timestamp time=new java.sql.Timestamp(datetime.getTime());

  • oracle difference in uri 2010-07-08

    Has been engaged in mysql do not know, oracle something like this 1.jdbc: oracle: oci: @ sid this is the need to install the oracle client 2.jdbc: oracle: thin: @ host: port: sid The oracle of the jar plus package directly on the line

  • ORACLE SQL to get date time period 2011-08-01

    The role of the following sql: get 60 days from the date of 2011-08-01. SELECT to_date ('2011-08-01 ',' yyyy-MM-dd ') + rownum -1 FROM DUAL connect by rownum <= 60