ORACLE中有哪些日期類型，相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

創(chuàng)新互聯(lián)建站于2013年開始，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目做網(wǎng)站、網(wǎng)站設(shè)計(jì)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元汪清做網(wǎng)站,已為上家服務(wù),為汪清各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:13518219792

數(shù)據(jù)庫中有4中日期類型：


1、DATE：是最早和使用最廣的日期類型，使用示例如下。


DATE is the oldest and most widely used data type. Though the name is "DATE", it stores also information about the time. Internally, DATE stores year, month, day, hour, minute and second as explicit values. To get the current timestamp as an instance of DATE, use the SYSDATE SQL function.


SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> select sysdate, dump(sysdate) as date_bytes from dual;


SYSDATE              DATE_BYTES                           
-------------------- -----------------------------------
2017-11-23 23:41:08  Typ=13 Len=8: 225,7,11,23,23,41,8,0  




2、TIMESTAMP：擴(kuò)展了DATA類型，包含了分秒和時(shí)區(qū)。


TIMESTAMP extends DATE by fractional seconds. Internally, time zone information is also contained, but in order to work with time zones, one of the other two data types, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE must be used. To get the current timestamp as a TIMESTAMP instance, use the LOCALTIMESTAMP SQL function. As the DUMP result below shows, TIMESTAMP stores year, month, day, hour, minute and second similar to DATE. The other bytes are there for fractional seconds and time zone information. TIMESTAMP instances consume more space on disk than DATE instances, which is natural - they contain more information.




SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';
SQL> select localtimestamp, dump(localtimestamp) ts_bytes from dual;


LOCALTIMESTAMP              TS_BYTES                                                               
--------------------------- ---------------------------------------------------------------------
2017-11-24 08:45:41.434175  Typ=187 Len=20: 225,7,11,24,8,45,41,0,24,252,224,25,1,0,3,0,127,1,0,0


3、SYSTIMESTAMP顯示時(shí)區(qū)，SYSTIMESTAMP顯示當(dāng)前數(shù)據(jù)庫時(shí)區(qū)，CURRENT_TIMESTAMP顯示當(dāng)前會(huì)話時(shí)區(qū)；


TIMESTAMP WITH TIME ZONE allows to explicitly work with time zone information. A time zone can be used to create a TIMESTAMP WITH TIME ZONE instance and it is explicitly contained in the output (use the right format mask). When the output of a TIMESTAMP WITH TIME ZONE value does not contain a time zone, you have incomplete data.
Oracle does not implicitly convert instances of this data type between time zones; this can be done explicitly with the AT TIME ZONE clause. To get the current time as a TIMESTAMP WITH TIME ZONE instance, we can use two functions: SYSTIMESTAMP returns the current time in the database time zone and CURRENT_TIMESTAMP returns it in the current session time zone. The following example uses SYSTIMESTAMP.


SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF6 TZR';
SQL> select systimestamp as ts_bytes from dual;


SYSTIMESTAMP
----------------------------------
2017-11-23 23:57:04.609608 -08:00


4、本地時(shí)區(qū)和數(shù)據(jù)庫時(shí)區(qū)轉(zhuǎn)換。TIMESTAMP隱含使用本地時(shí)區(qū)，默認(rèn)使用會(huì)話時(shí)區(qū)插入，存儲(chǔ)時(shí)默認(rèn)使用數(shù)據(jù)庫時(shí)區(qū)。


TIMESTAMP WITH LOCAL TIME ZONE does not expose the time zone information (to the application, it looks like a TIMESTAMP), but it uses tome zones implicitly: An instance is created without time zone information, the session time zone is assumed. Stored instances are normalized to the database time zone. When the data is retrieved, users see it (again) in the session time zone. 
The following example illustrates how TIMESTAMP WITH LOCAL TIME ZONE works: First, a table is created. Then the current time is retrieved and stored into the table ( SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE, but this is automatically converted to TIMESTAMP WITH LOCAL TIME ZONE ). When the table is then being selected, the returned data changes with the session time zone.


SQL> create table mytimestamps( ts timestamp with local time zone );
SQL> insert into mytimestamps values ( systimestamp );
SQL> alter session set time_zone='Europe/Berlin';
SQL> select * from mytimestamps;


TS
---------------------------------------------------------------------------
2017-11-24 12:55:39.761283


SQL> alter session set time_zone='EST';
SQL> select * from mytimestamps;


TS
---------------------------------------------------------------------------
2017-11-24 06:55:39.761283




Format Masks


Whether we work within an application or a tool like SQL Plus or SQL Developer; whenever we output a DATE or TIMESTAMP instance, we're converting it to VARCHAR2. We can do this explicitly using TO_CHAR or Oracle will do it implcitly. And now NLS format masks kick in - the format mask decides how a DATE is converted to a VARCHAR2 and back. The are defaults for NLS format masks at the database level, we can set them at the session level or we can pass a format mask to the individual TO_CHAR call.
看完上述內(nèi)容，你們掌握ORACLE中有哪些日期類型的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

網(wǎng)站名稱：ORACLE中有哪些日期類型
分享鏈接：http://aaarwkj.com/article12/pdphgc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊(cè)、移動(dòng)網(wǎng)站建設(shè)、ChatGPT、品牌網(wǎng)站設(shè)計(jì)、電子商務(wù)、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)