YET Another UTC Offset issue
Posted: Fri 09 Nov 2018 21:05
We seem to be in the habit of finding your UTC bugs (see a previous post by myself in 2014). Run this code and you'll repeat the issue. Note that this has been tested in MT and PST timezones. I'm unsure if this'll repeat in Germany.
Code: Select all
using System;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Data.Common;
using System.Data.Entity;
using System.Globalization;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Infrastructure.Interception;
using Devart.Common;
using Devart.Data.PostgreSql;
using Devart.Data.PostgreSql.Entity.Migrations;
namespace DevartBugTester
{
// Setup a new DB and execute the following:
// create table data
// (
// id serial,
// date timestamp without time zone,
// constraint pk_data primary key (id)
// )
public class Data
{
public int Id { get; set; }
public DateTime Date { get; set; }
public const string Table = "data";
}
public class Projection
{
public int id { get; set; }
public DateTimeOffset date { get; set; }
}
public class Store : DbContext
{
public Store(string nameOrConnectionString)
: base(nameOrConnectionString)
{
Database.SetInitializer<Store>(null);
}
public IDbSet<Data> Data { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder
.Entity<Data>()
.ToTable(DevartBugTester.Data.Table)
.HasKey(x => x.Id)
.Property(x => x.Id)
.HasColumnName("id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Data>().Property(x => x.Date).HasColumnName("date");
}
}
public class StoreConfiguration : DbConfiguration
{
class CommandInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Console.WriteLine();
Console.WriteLine(command.CommandText);
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
}
public StoreConfiguration()
{
AddInterceptor(new CommandInterceptor());
}
}
class Program
{
static void Main(string[] args)
{
Console.WriteLine($"Culture:\t{CultureInfo.CurrentCulture.DisplayName}");
Console.WriteLine($"Timezone:\t{TimeZoneInfo.Local.DisplayName}");
LogAssmVersion(typeof(DbCommandBase));
LogAssmVersion(typeof(PgSqlConnection));
LogAssmVersion(typeof(PgSqlConnectionInfo));
var name = "Bug";
var store = new Store(name);
var con = store.Database.Connection;
con.Open();
LogPgInfo(store, "Version", "select version()");
LogPgInfo(store, "Timezone", "select current_setting('TIMEZONE') tz");
// Calculate the evil hour.
//var dsEnd = DateTime.SpecifyKind(new DateTime(2018, 11, 5), DateTimeKind.Utc);
//var localizedDsEnd = TimeZoneInfo.ConvertTimeFromUtc(dsEnd, TimeZoneInfo.Local);
//var evilHour = (dsEnd - localizedDsEnd).TotalHours;
//
// More direct way to calculate the evil hour:
var evilHour = -(TimeZoneInfo.Local.BaseUtcOffset.TotalHours);
Console.WriteLine($"Evil hour:\t{evilHour}");
Bootstrap(con, (int)evilHour);
void test(int id)
{
try
{
var item = store
.Data
.Where(x => x.Id == id)
.Select(x => new Projection
{
date = x.Date,
id = x.Id
})
.Single();
Console.WriteLine();
Console.WriteLine($"Success:\t{item.id}\t{item.date.UtcDateTime}");
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
};
// Success.
test(1);
test(2);
// Fail - causes "The UTC Offset of the local dateTime parameter does not match the offset argument."
test(3);
}
static void Bootstrap(DbConnection connection, int evilHour)
{
using (var cmd = connection.CreateCommand())
{
void execute(string sql)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
};
void addTest(int id, int hour)
{
execute($"insert into data values({id},'2018-11-04 {hour}:00:00')");
};
execute(
@"create table if not exists data
(
id serial,
date timestamp without time zone,
constraint pk_data primary key (id)
)");
execute("truncate table data");
addTest(1, 0);
addTest(2, evilHour + 1);
addTest(3, evilHour);
}
}
static void LogAssmVersion(Type type)
{
var info = new FileInfo(type.Assembly.Location);
Console.WriteLine(
"Dll Version:\t{0}, v{1}",
info.Name,
type.Assembly.GetName().Version.ToString());
}
static void LogPgInfo(Store store, string label, string command)
{
using (var cmd = store.Database.Connection.CreateCommand())
{
cmd.CommandText = command;
Console.WriteLine("Pg {0}:\t{1}", label, cmd.ExecuteScalar());
}
}
}
}