Shulou(Shulou.com)06/01 Report--

Transfer to: https://www.2cto.com/database/201110/107435.html

I)

Four types of time in Oracle

Date

Timestamp

Timestamp with local time zone

Timestamp with time zone

Of these four types, the first two are completely independent of the time zone. Their "behavior" is like the varchar2 or number type, that is, what you insert is the same value, then you store the same value, and the query results in the same value (including your query using ADO.NET or ODP.NET in the .NET environment). There are no so-called "parameter" settings that can change them (of course, You can change their display format, but not the value.

The latter two types are closely related to time zone information, but there are big differences between them. Timestamp with local timezone does not actually store time zone information. When inserting values into this type of column, the user provides time zone information, but Oracle automatically converts it into time under dbtimezone for storage. Therefore, timestamp with local timezone has time zone information, that is, database time zone, but it does not store time zone information. When the query occurs, Oracle converts the time to the client's time zone (sessiontimezone) for display.

Unlike timestamp with local time zone, which delegates time zone conversion to Oracle servers, timestamp with time zone simply saves the time + time zone information you provided in insert to the database.

II)

Two parameters related to the time zone

Dbtimezone

Sessiontimezone

-- session time zone

Select sessiontimezone from dual

-Database time zone

Select systimestamp from dual

-- time with zone

Select dbtimezone from dual

The former represents the database time zone and the latter represents the client time zone. Only timestamp with local time zone is affected by time zone changes. Both parameters can be modified through the alter command.

Changes to the client operating system time zone will affect the sessiontimezone; modification server operating system time zone of the oracle client on this machine and will not change the dbtimezone.

SQL > select dbtimezone from dual

DBTIMEZONE

-

06:00

SQL > select sessiontimezone from dual

SESSIONTIMEZONE

-

+ 08:00

Modify the alter command for the client or database time zone:

Alter session set time_zone='+10:00'

Alter database set time_zone='+10:00'

If a field of type timestamp with local timezone exists in the database, ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns appears when you modify the dbtimezone

III)

How to insert a timestamp with time zone or timestamp with local time zone type into a database

SQL > create table tz1 (twtz timestamp with time zone, twltz timestamp with local time zone)

SQL > insert into tz1 values (timestamp'2011-01-03 15purl 00lv 00.000000 + 05R 001R timestampposts 2011-01-03 15purl 0000Rd 00.000000 + 05R 00')

SQL > select sessiontimezone from dual

SESSIONTIMEZONE

+ 08:00

SQL > select * from tz1

TWTZ TWLTZ

03-JAN-11 03.00.00.000000 PM + 05:00 03-JAN-11 06.00.00.000000 PM

SQL > alter session set time_zone='-06:00'

SQL > select * from tz1

TWTZ TWLTZ

03-JAN-11 03.00.00.000000 PM + 05:00 03-JAN-11 04.00.00.000000 AM

In addition to using a time zone logo in the form of "+ 05:00", you can also use abbreviations for the time zone, such as GMT,PST, and so on (you can see the system view V$TIMEZONE_NAMES). If no time zone information is given during insert, the default is the time zone of the current client.

IV)

Some time zone related functions:

Function

Return value

Return value type

SYSTIMESTAMP

Current date/time, in Database TZ

TIMESTAMP WITH TIME ZONE

CURRENT_TIMESTAMP

Current date/time, in Client Session TZ

TIMESTAMP WITH TIME ZONE

LOCALTIMESTAMP

Local date/time in Client Session, but with no TZ info

TIMESTAMP

DBTIMEZONE

Database time zone, in HH:MI offset from GMT

VARCHAR2

SESSIONTIMEZONE

Session time zone, in HH:MI offset from GMT

VARCHAR2

EXTRACT (part FROM date_time)

Extracts year, hour, seconds, time zone name, etc. From a supplied datetime or interval expression.

VARCHAR2

SYS_EXTRACT_UTC (date_time with TZ)

GMT (UTC) time of date/time supplied

TIMESTAMP

TZ_OFFSET (TZ)

Returns hour/minute offset from GMT of TZ

VARCHAR2

FROM_TZ (timestamp,TZ)

Converts a TIMESTAMP to TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

TO_TIMESTAMP

Convert char + fmt model to TIMESTAMP

TIMESTAMP

TO_TIMESTAMP_TZ

Convert char + fmt model to TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

V)

Basic conversion of time zone

The east-west time zone is marked with a + sign and the west time zone is marked with a-sign. At a specific point in time, the larger the time zone number (considering the plus or minus sign), the later the time. For example, Beijing is in + 08:00, St. Louis is in-06:00, Beijing is already evening, St. Louis is still in the early morning, and their previous difference is + 08 St-(- 06:00) = 14 (due to the influence of daylight saving time, there may be an hour's error).

VI)

Which type of time should be chosen?

If the time precision that needs to be recorded exceeds seconds, select the timestamp type.

If you need to automatically convert time between the database time zone and the client time zone, select timestamp with local time zone.

If you need to record the time zone information that the customer inserted, select timestamp with time zone.

VII)

ODP.NET and OracleGlobalization

For the timestamp with time zone type, it is not difficult to convert it manually in the application because it contains the original time zone information. In addition to manual methods, we can also set the relevant properties under OracleGlobalization to allow ODP.NET to convert automatically for you. Look at an example:

Conn.Open (); / / connection should be opened before SetSessionInfo () could be invoked.

/ /

OracleGlobalization og = OracleGlobalization.GetClientInfo ()

Og.TimeZone = "America/Chicago"

OracleGlobalization.SetThreadInfo (og)

Conn.SetSessionInfo (og)

/ /

OracleCommand cmd = new OracleCommand ()

Cmd.Connection = conn

Cmd.CommandText = "select twtz from tz1"

OracleDataReader dr = cmd.ExecuteReader ()

If (dr.HasRows)

{

While (dr.Read ())

{

OracleTimeStampTZ otstz = dr.GetOracleTimeStampTZ (dr.GetOrdinal ("twtz"))

Console.WriteLine ("twtz:" + otstz.ToString ())

}

}

Dr.Close ()

/ /

/ / an alias is necessary when using'AT LOCAL' predicate

Cmd.CommandText = "select twtz AT LOCAL as twtz from tz1"

Dr = cmd.ExecuteReader ()

If (dr.HasRows)

{

While (dr.Read ())

{

OracleTimeStampTZ otstz = dr.GetOracleTimeStampTZ (dr.GetOrdinal ("twtz"))

Console.WriteLine ("twtz AT LOCAL:" + otstz.ToString ())

}

}

Dr.Close ()

/ / output:

Twtz: 03-JAN-11 03.00.00.000000 PM + 05:00

Twtz AT LOCAL: 03-JAN-11 04.00.00.000000 AM AMERICA/CHICAGO

You can see that when the time zone is set and'AT LOCAL' is used in the sql statement, the original time is automatically converted to Chicago's time (Chicago is in West 6, so the difference from the original time zone East 5 is 11 hours).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.