public void PqsqlDataReaderTest12()
{
mCmd.CommandText = @"select interval '23 days', timestamp '2038-01-01', timestamptz '2038-01-01 00:00:00+02', date '2038-01-01', time '00:00:00', timetz '23:23:23+02';";
using (PqsqlDataReader reader = mCmd.ExecuteReader())
{
Assert.IsTrue(reader.HasRows);
reader.Read();
TimeSpan ts0 = reader.GetTimeSpan(0);
Assert.AreEqual(TimeSpan.FromDays(23), ts0);
TimeSpan ts1 = reader.GetTimeSpan(1);
DateTime dt1 = reader.GetDateTime(1);
Assert.AreEqual(new DateTime(2038, 01, 01, 0, 0, 0), dt1);
DateTimeOffset dto1 = reader.GetDateTimeOffset(1);
Assert.AreEqual(new DateTimeOffset(2038, 01, 01, 0, 0, 0, TimeSpan.Zero), dto1);
TimeSpan ts2 = reader.GetTimeSpan(2);
DateTime dt2 = reader.GetDateTime(2);
Assert.AreEqual(new DateTime(2037, 12, 31, 22, 0, 0), dt2);
DateTimeOffset dto2 = reader.GetDateTimeOffset(2);
Assert.AreEqual(new DateTimeOffset(2038, 01, 01, 0, 0, 0, TimeSpan.FromHours(2)), dto2);
TimeSpan ts3 = reader.GetTimeSpan(3);
DateTime dt3 = reader.GetDateTime(3);
Assert.AreEqual(new DateTime(2038, 01, 01, 0, 0, 0), dt3);
DateTimeOffset dto3 = reader.GetDateTimeOffset(3);
Assert.AreEqual(new DateTimeOffset(2038, 01, 01, 0, 0, 0, TimeSpan.Zero), dto3);
TimeSpan ts4 = reader.GetTimeSpan(4);
Assert.AreEqual(TimeSpan.FromSeconds(0), ts4);
DateTime dt4 = reader.GetDateTime(4);
Assert.AreEqual(DateTime.MinValue, dt4);
DateTimeOffset dto4 = reader.GetDateTimeOffset(4);
Assert.AreEqual(DateTimeOffset.MinValue, dto4);
TimeSpan ts5 = reader.GetTimeSpan(5);
//Assert.AreEqual(TimeSpan.FromHours(21).Add(TimeSpan.FromMinutes(23)).Add(TimeSpan.FromSeconds(23)), ts5);
DateTime dt5 = reader.GetDateTime(5);
//Assert.AreEqual(DateTime.MinValue.AddHours(21).AddMinutes(23).AddSeconds(23), dt5);
DateTimeOffset dto5 = reader.GetDateTimeOffset(5);
//Assert.AreEqual(new DateTimeOffset(DateTime.MinValue.AddHours(23).AddMinutes(23).AddSeconds(23), TimeSpan.FromHours(2)), dto5);
reader.Close();
}
}
public void PqsqlTypeRegistryTest2()
{
mCmd.CommandText = "select localtimestamp, now()::timestamp, now(), now() at time zone 'UTC', '1999-01-01 00:00:00'::timestamp at time zone 'UTC', '2000-07-01 00:00:00'::timestamp at time zone 'Europe/Vienna'";
mCmd.CommandType = CommandType.Text;
PqsqlDataReader reader = mCmd.ExecuteReader();
bool read = reader.Read();
Assert.IsTrue(read);
DateTime localtimestamp0 = reader.GetDateTime(0);
DateTimeOffset localtimestampoff0 = reader.GetDateTimeOffset(0);
DateTime nownotz1 = reader.GetDateTime(1);
DateTimeOffset nownotzoff1 = reader.GetDateTimeOffset(1);
DateTime nowtz2 = reader.GetDateTime(2);
DateTimeOffset nowtzoff2 = reader.GetDateTimeOffset(2);
DateTime nowutc3 = reader.GetDateTime(3);
DateTimeOffset nowutcoff3 = reader.GetDateTimeOffset(3);
DateTime ts19990101000000_4 = reader.GetDateTime(4);
DateTimeOffset ts19990101000000_off4 = reader.GetDateTimeOffset(4);
DateTime ts20000701000000_5 = reader.GetDateTime(5);
DateTimeOffset ts20000701000000_off5 = reader.GetDateTimeOffset(5);
DateTimeOffset off0 = TimeZoneInfo.ConvertTime(new DateTimeOffset(localtimestamp0.Ticks, TimeSpan.Zero), TimeZoneInfo.Local);
DateTimeOffset off1 = TimeZoneInfo.ConvertTime(new DateTimeOffset(nownotz1.Ticks, TimeSpan.Zero), TimeZoneInfo.Local);
DateTimeOffset off2 = TimeZoneInfo.ConvertTime(new DateTimeOffset(nowtz2.Ticks, TimeSpan.Zero), TimeZoneInfo.Local);
DateTimeOffset off3 = TimeZoneInfo.ConvertTime(new DateTimeOffset(nowutc3.Ticks, TimeSpan.Zero), TimeZoneInfo.Local);
DateTimeOffset off4 = TimeZoneInfo.ConvertTime(new DateTimeOffset(ts19990101000000_4.Ticks, TimeSpan.Zero), TimeZoneInfo.Local);
DateTimeOffset off5 = TimeZoneInfo.ConvertTime(new DateTimeOffset(ts20000701000000_5.Ticks, TimeSpan.Zero), TimeZoneInfo.Local);
Assert.AreEqual(localtimestampoff0, off0);
Assert.AreEqual(nownotzoff1, off1);
Assert.AreEqual(nowtzoff2, off2);
Assert.AreEqual(nowutcoff3, off3);
Assert.AreEqual(ts19990101000000_off4, off4);
Assert.AreEqual(ts20000701000000_off5, off5);
read = reader.Read();
Assert.IsFalse(read);
}
public void PqsqlTypeRegistryTest5()
{
mCmd.CommandText = "select :ts0,:ts1,:ts2,:ts3,:ts4";
mCmd.CommandType = CommandType.Text;
PqsqlParameter p0 = new PqsqlParameter("ts0", DbType.DateTimeOffset, new DateTimeOffset(2016, 1, 1, 0, 0, 0, 0, TimeSpan.Zero));
mCmd.Parameters.Add(p0);
PqsqlParameter p1 = new PqsqlParameter
{
ParameterName = "ts1",
PqsqlDbType = PqsqlDbType.Unknown, // let PqsqlParameterCollection guess the type
Value = new DateTimeOffset(new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Local))
};
mCmd.Parameters.Add(p1);
PqsqlParameter p2 = new PqsqlParameter("ts2", DbType.DateTime, new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Local));
mCmd.Parameters.Add(p2);
PqsqlParameter p3 = new PqsqlParameter("ts3", DbType.DateTime, new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc));
mCmd.Parameters.Add(p3);
PqsqlParameter p4 = new PqsqlParameter("ts4", DbType.DateTime, new DateTime(2016, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
mCmd.Parameters.Add(p4);
PqsqlDataReader reader = mCmd.ExecuteReader();
bool read = reader.Read();
Assert.IsTrue(read);
DateTime ts0 = reader.GetDateTime(0);
DateTimeOffset tstz0 = reader.GetDateTimeOffset(0);
DateTimeOffset tsutc0 = tstz0.ToUniversalTime();
DateTimeOffset tslocal0 = tstz0.ToLocalTime();
Assert.AreEqual(ts0, tsutc0.DateTime);
DateTime ts1 = reader.GetDateTime(1);
DateTimeOffset tstz1 = reader.GetDateTimeOffset(1);
DateTimeOffset tsutc1 = tstz1.ToUniversalTime();
DateTimeOffset tslocal1 = tstz1.ToLocalTime();
DateTime ts2 = reader.GetDateTime(2);
DateTimeOffset tstz2 = reader.GetDateTimeOffset(2);
DateTimeOffset tsutc2 = tstz2.ToUniversalTime();
DateTimeOffset tslocal2 = tstz2.ToLocalTime();
Assert.AreEqual(ts2, tsutc2.UtcDateTime);
Assert.AreEqual(ts2.ToLocalTime(), tstz2.LocalDateTime);
Assert.AreEqual(ts2.ToLocalTime(), tsutc2.LocalDateTime);
DateTime ts3 = reader.GetDateTime(3);
DateTimeOffset tstz3 = reader.GetDateTimeOffset(3);
DateTimeOffset tsutc3 = tstz3.ToUniversalTime();
DateTimeOffset tslocal3 = tstz3.ToLocalTime();
Assert.AreEqual(ts3, tstz3.UtcDateTime);
Assert.AreEqual(ts3, tsutc3.DateTime);
DateTime ts4 = reader.GetDateTime(4);
DateTimeOffset tstz4 = reader.GetDateTimeOffset(4);
DateTimeOffset tsutc4 = tstz4.ToUniversalTime();
DateTimeOffset tslocal4 = tstz4.ToLocalTime();
Assert.AreEqual(ts4, tstz4.UtcDateTime);
Assert.AreEqual(ts4, tsutc4.DateTime);
read = reader.Read();
Assert.IsFalse(read);
}
