Schema Collection

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the Timestamp With Time Zone data type are:

Column Name	Description
COLUMN_TYPE or DATA_TYPE	Set to TIMESTAMP WITH TIME ZONE.
FORMAT	Format assigned to the SQL Engine object.
PROVIDERDBTYPE	It is set to TdType.TimestampWithZone.

Column Name

Description

COLUMN_TYPE

DATA_TYPE

Set to TIMESTAMP WITH TIME ZONE.

FORMAT

Format assigned to the SQL Engine object.

PROVIDERDBTYPE

It is set to TdType.TimestampWithZone.

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for the Timestamp With Time Zone data type are:

Column Name	Description
NumericScale	The number of significant digits in the fractional seconds portion of Time With Time Zone. The range of values are from Zero to Six.
DataType	System.Type object for the System.DateTimeOffset type.
ProviderType	Set to TdType.TimestampWithZone.
Format	Format assigned to the SQL Engine object.
ProviderSpecificDataType	System.Type object for the TdTimestampWithTimeZone type.

Configuring Parameters

The following example shows how to configure a Timestamp With Time Zone parameter using DbType type declaration and a BCL value. The TdParameter.Scale property reflects the number of fractional second digits and it must be equal to or less than the target SQL Engine object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration.

Note
The Data Provider truncates the fractional seconds to the Scale specified.

C#	Copy Code
TdParameter orderTimestampWZ = new TdParameter(); orderTimestampWZ.ParameterName = "orderTimestampWZ"; orderTimestampWZ.DbType = DbType.DateTimeOffset; // Fractional second digits orderTimestampWZ.Scale = 6; // Time Zone defaults to local time zone orderTimestampWZ.Value = new DateTimeOffset(new DateTime(2001, 1, 1, 13, 30, 10));

Copy Code

TdParameter orderTimestampWZ = new TdParameter();
orderTimestampWZ.ParameterName = "orderTimestampWZ";

orderTimestampWZ.DbType = DbType.DateTimeOffset;

// Fractional second digits
orderTimestampWZ.Scale = 6;

// Time Zone defaults to local time zone
orderTimestampWZ.Value = new DateTimeOffset(new DateTime(2001, 1, 1, 13, 30, 10));

The following example shows how to configure a Timestamp With Time Zone parameter using TdType type declaration and a Provider Specific value. It is recommend to always set TdParameter.Scale property, however the Data Provider will utilize TdTimestampWithTimeZone.Scale property when the TdParameter.Scale, TdParameter.Precision and TdParameter.Size properties are set to zero.

C#	Copy Code
TdParameter orderTimestampWZ = new TdParameter(); orderTimestampWZ.ParameterName = "orderTimestampWZ"; orderTimestampWZ.TdType = TdType.TimestampWithZone; // Fractional second digits orderTimestampWZ.Scale = 6; // Time Zone defaults to local time zone orderTimestampWZ.ProviderSpecificValue = new TdTimestampWithTimeZone(2011, 1, 1, 13, 30, 10);

Copy Code

TdParameter orderTimestampWZ = new TdParameter();
orderTimestampWZ.ParameterName = "orderTimestampWZ";

orderTimestampWZ.TdType = TdType.TimestampWithZone;

// Fractional second digits
orderTimestampWZ.Scale = 6;

// Time Zone defaults to local time zone
orderTimestampWZ.ProviderSpecificValue = new TdTimestampWithTimeZone(2011, 1, 1, 13, 30, 10);

Specifying Timestamp as Literal

The syntax for the Timestamp With Time Zone Literal is TIMESTAMP'YYYY-MM-DD hh:mi:ss.ssssss±hh:mi'. Timestamp literals consist of the word TIMESTAMP followed by character string literal representation of a Timestamp. The fractional seconds can vary from zero to six digits.

Note
We recommend to always use Parameters in order to take advantage of the SQL Engine's Request Cache.

The following example shows January 30, 2011 1:30:45 PM PDT represented as Timestamp With Time Zone Literal in the Command Text.

C#	Copy Code
using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;")) { cn.Open(); TdCommand cmd = cn.CreateCommand(); // Orders after January 30, 2011 1:30:45 PM cmd.CommandText = "SELECT Id, orderTimestampWZ from Order where orderTimestampWZ > TIMESTAMP'2011-01-30 13:30:45-07:00' "; using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("Id={0}, orderTimestamp={1}", reader.GetInt64(0), reader.GetDateTimeOffset(1)); } } } }

Copy Code

using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;"))
{
    cn.Open();
    TdCommand cmd = cn.CreateCommand();

    // Orders after January 30, 2011 1:30:45 PM
    cmd.CommandText = "SELECT Id, orderTimestampWZ from Order where orderTimestampWZ > TIMESTAMP'2011-01-30 13:30:45-07:00' ";

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("Id={0}, orderTimestamp={1}", reader.GetInt64(0), reader.GetDateTimeOffset(1));
            }
        }
    }
}

Retrieving Timestamp With Time Zone Data

The following methods and properties return the column or parameter value as a System.DateTimeOffset structure.

The following methods and properties return the column or parameter value as a TdTimestampWithTimeZone structure.

C#	Copy Code
using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;")) { cn.Open(); TdCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT Id, orderTimestampWZ from Order where orderTimestampWZ > ?"; cmd.Parameters.Add("orderTimestampWZ", TdType.TimestampWithZone); cmd.Parameters["orderTimestampWZ"].Scale = 0; cmd.Parameters["orderTimestampWZ"].Value = new DateTimeOffset(1990, 1, 30, 13, 30, 45, new TimeSpan(-7, 0, 0)); using (TdDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("[DateTimeOffset] OrderTimestampWZ = {0}", reader.GetDateTimeOffset(1).ToString()); Console.WriteLine("[TdTimestampWithTimeZone] OrderTimestamp = {0}", reader.GetTdTimestampWithTimeZone(1).ToString()); } } } } /* Output: [DateTimeOffset] OrderTimestampWZ = 9/17/1997 1:30:45 PM -07:00 [TdTimestamp] OrderTimestamp = 1997-09-17 01:30:45.123-07:00 */

Copy Code

using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;"))
{
    cn.Open();

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT Id, orderTimestampWZ from Order where orderTimestampWZ > ?";
    cmd.Parameters.Add("orderTimestampWZ", TdType.TimestampWithZone);
    cmd.Parameters["orderTimestampWZ"].Scale = 0;
    cmd.Parameters["orderTimestampWZ"].Value = new DateTimeOffset(1990, 1, 30, 13, 30, 45, new TimeSpan(-7, 0, 0));

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[DateTimeOffset] OrderTimestampWZ = {0}", reader.GetDateTimeOffset(1).ToString());
                Console.WriteLine("[TdTimestampWithTimeZone] OrderTimestamp = {0}",
                                               reader.GetTdTimestampWithTimeZone(1).ToString());
            }
        }
    }
}

/* Output:
    [DateTimeOffset] OrderTimestampWZ = 9/17/1997 1:30:45 PM -07:00
    [TdTimestamp] OrderTimestamp = 1997-09-17 01:30:45.123-07:00
*/

Schema Collection

Schema Table

Configuring Parameters

Specifying Timestamp as Literal

Retrieving Timestamp With Time Zone Data

See Also