ORACLE日期型別
ORACLE日期型別
參考:https://blogs.oracle.com/apex/date%2c-timestamp-and-format-masks
資料庫中有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:擴充套件了DATA型別,包含了分秒和時區。
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顯示時區,SYSTIMESTAMP顯示當前資料庫時區,CURRENT_TIMESTAMP顯示當前會話時區;
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、本地時區和資料庫時區轉換。TIMESTAMP隱含使用本地時區,預設使用會話時區插入,儲存時預設使用資料庫時區。
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.
參考:https://blogs.oracle.com/apex/date%2c-timestamp-and-format-masks
資料庫中有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:擴充套件了DATA型別,包含了分秒和時區。
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顯示時區,SYSTIMESTAMP顯示當前資料庫時區,CURRENT_TIMESTAMP顯示當前會話時區;
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、本地時區和資料庫時區轉換。TIMESTAMP隱含使用本地時區,預設使用會話時區插入,儲存時預設使用資料庫時區。
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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29519108/viewspace-2153769/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MongoDB日期型別查詢MongoDB型別
- elasticsearch之日期型別有點怪Elasticsearch型別
- Oracle資料型別對應Java型別Oracle資料型別Java
- ORACLE物件型別表Oracle物件型別
- Oracle 資料型別Oracle資料型別
- oracle 10g函式大全–日期型函式Oracle 10g函式
- 【Hive】日期從整形轉為Date型別Hive型別
- 日期型別有問題,如何解決?型別
- mysql儲存日期使用什麼型別MySql型別
- XSD 日期和時間資料型別資料型別
- 【轉】ORACLE資料型別Oracle資料型別
- Oracle anydata資料型別Oracle資料型別
- ORACLE NUMBER資料型別Oracle資料型別
- Oracle DG Standby Database型別OracleDatabase型別
- 匯入sql時報日期型別錯誤SQL型別
- SQL Server日期資料型別DATE的使用SQLServer資料型別
- Oracle OCP(19):資料型別Oracle資料型別
- oracle數值型別漫談Oracle型別
- Oracle的number資料型別Oracle資料型別
- Mysql日期(DATE, TIME, DATETIME, TIMESTAMP)型別的比較MySql型別
- 一對一視訊原始碼,通過日期字串轉換日期型別格式原始碼字串型別
- Oracle基本資料型別儲存格式淺析——RAW型別Oracle資料型別
- Oracle-insert into加日期Oracle
- Oracle字串資料型別簡述Oracle字串資料型別
- Oracle實驗(05):時間型別Oracle型別
- 【深度思考】JDK8中日期型別該如何使用?JDK型別
- Oracle更改預設日期格式 更改會話日期格式Oracle會話
- 【oracle】日期加減計算Oracle
- Oracle非法日期 處理方案Oracle
- Oracle和sqlserver資料型別對應OracleSQLServer資料型別
- Oracle 修改欄位型別和長度Oracle型別
- Oracle date 型別比較和String比較Oracle型別
- 資料型別與函式索引-Oracle篇資料型別函式索引Oracle
- oracle interval日期函式的bug!Oracle函式
- Go基礎知識-04 時間和日期型別(持續更新)Go型別
- MySQL把字串欄位轉換為日期型別進行比較MySql字串型別
- 1.1. Oracle 資料庫使用者型別Oracle資料庫型別
- [20190930]oracle raw型別轉化number指令碼.txtOracle型別指令碼