（一）Datetime and Interval Data Types
The datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.
不同时间类型由多个不同的字段组成
Both datetimes and intervals are made up of fields. The values of these fields determine the value of the data type.
The fields that apply to all Oracle Database datetime and interval data types are:
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
TIMESTAMP WITH TIME ZONE also includes these fields:
TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR
- Datetime Data Types
You can insert values into a datetime column in the following ways:
- Insert a character string whose format is based on the appropriate NLS format value
- Insert a literal
- Insert a literal for which implicit conversion is performed
- Use the TO_TIMESTAMP, TO_TIMESTAMP_TZ, or TO_DATE SQL function
The following examples show how to insert data into datetime data types.
Example:
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);
SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');
SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');
SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 America/Los_Angeles');
SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);
SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');
SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';
SQL> ALTER SESSION SET TIME_ZONE='-7:00';
SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);
SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');
SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');
SQL> ALTER SESSION SET TIME_ZONE='-07:00';
SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);
SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');
SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
SQL> SELECT * FROM table_tsltz;
C_ID C_TSLTZ
---------- ------------------------------------
1 01-JAN-03 02.00.00.000000 AM
2 01-JAN-03 02.00.00.000000 AM
3 01-JAN-03 03.00.00.000000 AM
- DATE Data Type
For each DATE value, Oracle Database stores the following information: century, year, month, date, hour, minute, and second. The default date format for an Oracle Database date value is derived from the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE initialization parameters.
If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.
date类型会存储日期与时间，在比较时建议使用trunc函数或使用非等于或不等于的比较
Oracle Database DATE columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to midnight.
You can use the TRUNC (date) SQL function to ensure that the time fields are set to midnight, or you can make the query a test of greater than or less than (<, <=, >=, or >) instead of equality or inequality (= or !=).
Otherwise, Oracle Database may not return the query results you expect.
- TIMESTAMP Data Type
TIMESTAMP用于扩展DATE类型，它会存储小数秒，但不会存储时区信息
The TIMESTAMP data type is an extension of the DATE data type. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE data type.
The TIMESTAMP data type uses 7 or 11 bytes of storage.
在指定TIMESTAMP类型时可以指定秒的小数位数，范围为0-9，默认为6。存储占用7~11 bytes
Specify the TIMESTAMP data type as follows:
TIMESTAMP [(fractional_seconds_precision)]
可以按如下方式指定格式
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
The value of NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP data type.
- TIMESTAMP WITH TIME ZONE Data Type
TIMESTAMP WITH TIME ZONE比TIMESTAMP多占用2bytes用于存储时区信息，它有两种存储方式，存储TZR(time zone region) 或存储相对UTC(Coordinated Universal Time, formerly Greenwich Mean Time)的offset。在显示或计算时不需要转化，但注意它不能用于主键，如果在此类型列创建索引将变为函数索引
The time zone is stored as a time zone region name or as an offset from UTC.
The data is available for display or calculations without additional processing.
A TIMESTAMP WITH TIME ZONE column cannot be used as a primary key. If an index is created on a TIMESTAMP WITH TIME ZONE column, it becomes a function-based index.
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value.
指定类型语法：
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
使用字面值的两种形式：
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
TIMESTAMP '1999-01-15 8:00:00 America/Los_Angeles'
下面是关于转化为夏令标准时间时可能产生的歧义及使用TZD解决方法：
To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the TZR format element and the corresponding TZD format element. The TZD format element is an abbreviation of the time zone region with Daylight Saving Time information included. Examples are PST for U. S. Pacific Standard Time and PDT for U. S. Pacific Daylight Time. The following specification ensures that a Daylight Saving Time value is returned:
TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'
If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle Database returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If ERROR_ON_OVERLAP_TIME is set to FALSE (the default value), then Oracle Database interprets the ambiguous datetime as Standard Time.
The default date format for the TIMESTAMP WITH TIME ZONE data type is determined by the value of the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
- TIMESTAMP WITH LOCAL TIME ZONE Data Type
与TIMESTAMP WITH TIME ZONE不同在于它不存储时区信息，所以只需要11bytes存储，它是将客户端写入时间本地化为数据库时区存储，而在显示时会转化为客户端的时区时间
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WITH TIME ZONE as follows: data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle Database returns it in the users' local session time zone.
TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM or TZR TZD format elements are specified.
一般在你对于时区信息不感兴趣或对于转化夏令时不要求准确可以用TIMESTAMP WITH LOCAL TIME ZONE替代TIMESTAMP WITH TIME ZONE
The TIMESTAMP WITH LOCAL TIME ZONE data type is appropriate when the original time zone is of no interest, but the relative times of events are important and daylight saving adjustment does not have to be accurate.
指定类型语法：
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
There is no literal for TIMESTAMP WITH LOCAL TIME ZONE, but TIMESTAMP literals and TIMESTAMP WITH TIME ZONE literals can be inserted into a TIMESTAMP WITH LOCAL TIME ZONE column.
The default date format for TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the NLS_TIMESTAMP_FORMAT initialization parameter.
- Interval Data Types
You can insert values into an interval column in the following ways:
- Insert an interval as a literal. For example:
INSERT INTO table1 VALUES (INTERVAL '4-2' YEAR TO MONTH);
- Use the NUMTODSINTERVAL, NUMTOYMINTERVAL, TO_DSINTERVAL, and TO_YMINTERVAL SQL functions.
- INTERVAL YEAR TO MONTH Data Type
INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. Specify INTERVAL YEAR TO MONTH as follows:
INTERVAL YEAR [(year_precision)] TO MONTH
year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default value of year_precision is 2.
Interval values can be specified as literals. There are many ways to specify interval literals.
INTERVAL '123-2' YEAR(3) TO MONTH
- INTERVAL DAY TO SECOND Data Type
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. Specify this data type as follows:
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2. fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
（二）Datetime and Interval Arithmetic and Comparisons
1. Datetime and Interval Arithmetic
You can perform arithmetic operations on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH) data. You can maintain the most precision in arithmetic operations by using a timestamp data type with an interval data type.
注意在使用数值常量运算时将会丢失秒的精度
You can use NUMBER constants in arithmetic operations on date and timestamp values. Oracle Database internally converts timestamp values to date values before doing arithmetic operations on them with NUMBER constants. This means that information about fractional seconds is lost during operations that include both date and timestamp values. Oracle Database interprets NUMBER constants in datetime and interval expressions as number of days.
Each DATE value contains a time component. The result of many date operations includes a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours. These fractions are also returned by Oracle Database built-in SQL functions for common operations on DATE data. For example, the built-in MONTHS_BETWEEN SQL function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.
Oracle Database performs all timestamp arithmetic in UTC time. For TIMESTAMP WITH LOCAL TIME ZONE data, Oracle Database converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP WITH TIME ZONE data, the datetime value is always in UTC, so no conversion is necessary.
2. Datetime Comparisons
When you compare date and timestamp values, Oracle Database converts the data to the more precise data type before doing the comparison.
For example, if you compare data of TIMESTAMP WITH TIME ZONE data type with data of TIMESTAMP data type, Oracle Database converts the TIMESTAMP data to TIMESTAMP WITH TIME ZONE, using the session time zone.
The order of precedence for converting date and timestamp data is as follows:
DATE
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE
For any pair of data types, Oracle Database converts the data type that has a smaller number in the preceding list to the data type with the larger number.
- Explicit Conversion of Datetime Data Types
If you want to do explicit conversion of datetime data types, use the CAST SQL function. You can explicitly convert DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE to another data type in the list.
（三）Datetime SQL Functions
Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.
Some of the datetime functions were designed for the Oracle Database DATE data type. If you provide a timestamp value as their argument, then Oracle Database internally converts the input type to a DATE value. Oracle Database does not perform internal conversion for the ROUND and TRUNC functions.
The following table shows the datetime functions that were designed for the Oracle Database DATE data type.
The following table describes additional datetime functions.
The following table describes functions that are used in the Daylight Saving Time (DST) upgrade process, and are only available when preparing or updating windows.
（四）Datetime and Time Zone Parameters and Environment Variables
1. Datetime Format Parameters
The following table contains the names and descriptions of the datetime format parameters.
Their default values are derived from NLS_TERRITORY.
You can specify their values by setting them in the initialization parameter file.
You can also specify their values for a client as client environment variables. For Java clients, the value of NLS_TERRITORY is derived from the default locale of JRE. The values specified in the initialization parameter file are not used for JDBC sessions.
To change their values during a session, use the ALTER SESSION statement.
- Time Zone Environment Variables
The time zone environment variables are:
ORA_TZFILE, which enables you to specify a time zone on the client and Oracle Database server. Note that when you specify ORA_TZFILE on Oracle Database server, the only time when this environment variable takes effect is during the creation of the database.
ORA_SDTZ, which specifies the default session time zone.
- Daylight Saving Time Session Parameter
ERROR_ON_OVERLAP_TIME is a session parameter that determines how Oracle Database handles an ambiguous datetime boundary value. Ambiguous datetime values can occur when the time changes between Daylight Saving Time and standard time.
The possible values are TRUE and FALSE. When ERROR_ON_OVERLAP_TIME is TRUE, then an error is returned when Oracle Database encounters an ambiguous datetime value. When ERROR_ON_OVERLAP_TIME is FALSE, then ambiguous datetime values are assumed to be the standard time representation for the region. The default value is FALSE.
- Daylight Saving Time Upgrade Parameter
DST_UPGRADE_INSERT_CONV is an initialization parameter that is only used during the upgrade window of the Daylight Saving Time (DST) upgrade process. It is only applicable to tables with TIMESTAMP WITH TIME ZONE columns because those are the only ones that are modified during the DST upgrade.
During the upgrade window of the DST patching process (which is described in the DBMS_DST package), tables with TIMESTAMP WITH TIMEZONE data undergo conversion to the new time zone version. Columns in tables that have not yet been converted will still have the TIMESTAMP WITH TIMEZONE reflecting the previous time zone version. In order to present the data in these columns as though they had been converted to the new time zone version when you issue SELECT statements, Oracle adds by default conversion operators over the columns to convert them to the new version. Adding the conversion operator may, however, slow down queries and disable usage of indexes on the TIMESTAMP WITH TIMEZONE columns. Hence, Oracle provides a parameter, DST_UPGRADE_INSERT_CONV, that specifies whether or not internal operators are allocated on top of TIMESTAMP WITH TIMEZONE columns of tables that have not been upgraded. By default, its value is TRUE. If users know that the conversion process will not affect the TIMESTAMP WITH TIMEZONE columns, then this parameter can be set to FALSE.
Oracle strongly recommends that you set this parameter to TRUE throughout the DST patching process. By default, this parameter is set to TRUE. However, if set to TRUE, query performance may be degraded on unconverted tables. Note that this only applies during the upgrade window.
扫一扫
559
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
