ok, I would like to summarize the topic.
I wrote a test program. It runs on Linux Ubuntu 20. On Linux, the time zone is CEST.
Code: Select all
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Globalization;
using System.IO;
using System.Linq;
using Devart.Common;
using Devart.Data.PostgreSql;
using Devart.Data.PostgreSql.Entity.Configuration;
using Devart.Data.PostgreSql.Entity.Migrations;
using Microsoft.Extensions.Configuration;
namespace DefaultDateTimeOffsetIssue
{
public class Table
{
public int Id { get; set; }
public DateTimeOffset Date { get; set; }
public string DateAsString { get; set; }
}
[DbConfigurationType(typeof(PgSqlConfiguration))]
public class MyDbContext : DbContext
{
public MyDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
{
Database.SetInitializer<MyDbContext>(new CreateDatabaseIfNotExists<MyDbContext>());
}
public IDbSet<Table> TestData { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder
.Entity<Table>()
.ToTable("TestData")
.HasKey(x => x.Id)
.Property(x => x.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Table>().Property(x => x.Date);
}
}
public class Program
{
public static IConfigurationRoot Config;
private static void Main(string[] args)
{
Config = ConfigureAppSettings();
Console.WriteLine($"Culture:\t{CultureInfo.CurrentCulture.DisplayName}");
Console.WriteLine($"Timezone:\t{TimeZoneInfo.Local.DisplayName} + {TimeZoneInfo.Local.Id}");
LogAssmVersion(typeof(DbCommandBase));
LogAssmVersion(typeof(PgSqlConnection));
LogAssmVersion(typeof(PgSqlConnectionInfo));
var dbInit = new MyDbContext("Domain");
dbInit.Database.CreateIfNotExists();
var con = dbInit.Database.Connection;
con.Open();
LogPgInfo(dbInit, "Version", "select version()");
LogPgInfo(dbInit, "Timezone", "select current_setting('TIMEZONE') tz");
using (var db = new MyDbContext("Domain"))
{
var date = default(DateTimeOffset);
var date2 = new DateTimeOffset(1884, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0));
var date3 = new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0));
db.TestData.Add(new Table { Id = 1, Date = date, DateAsString = date.ToString() });
db.TestData.Add(new Table { Id = 2, Date = date2, DateAsString = date2.ToString() });
db.TestData.Add(new Table { Id = 3, Date = date3, DateAsString = date3.ToString() });
db.SaveChanges();
}
using (var db = new MyDbContext("Domain"))
{
try
{
db.TestData.ToList();
}
catch (Exception ex)
{
Console.WriteLine("Exception reading dates" + ex.Message);
}
}
Console.ReadLine();
}
private 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());
}
private static void LogPgInfo(MyDbContext store, string label, string command)
{
using (var cmd = store.Database.Connection.CreateCommand())
{
cmd.CommandText = command;
Console.WriteLine("Pg {0}:\t{1}", label, cmd.ExecuteScalar());
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(String.Format("{0}", reader[0]));
}
}
}
private static IConfigurationRoot ConfigureAppSettings(string fileName = "appsettings")
{
var configurationBuilder = new ConfigurationBuilder()
.SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
.AddJsonFile($"{fileName}.json", false, true);
return configurationBuilder.Build();
}
}
public class ConnectionStringSettings
{
public string Name { get; set; }
public string ConnectionString { get; set; }
public string ProviderName { get; set; }
}
public class PgSqlConfiguration : DbConfiguration
{
public PgSqlConfiguration()
{
SetProviderFactory("Devart.Data.PostgreSql", Devart.Data.PostgreSql.PgSqlProviderFactory.Instance);
SetProviderServices("Devart.Data.PostgreSql", Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices.Instance);
SetDefaultConnectionFactory(new PostgresDbConnectionFactory());
PgSqlEntityProviderConfig.Instance.DatabaseScript.Schema.DeleteDatabaseBehaviour = DeleteDatabaseBehaviour.Database;
}
}
public class PostgresDbConnectionFactory : IDbConnectionFactory
{
private PgSqlProviderFactory ProviderFactory = PgSqlProviderFactory.Instance;
public DbConnection CreateConnection(string nameOrConnectionString)
{
var connection = ProviderFactory.CreateConnection();
if (nameOrConnectionString.Contains(";"))
{
connection.ConnectionString = nameOrConnectionString;
}
else
{
connection.ConnectionString = Program.Config.GetSection("ConnectionStrings").Get<Dictionary<string, ConnectionStringSettings>>()[nameOrConnectionString].ConnectionString;
}
return connection;
}
}
}
Output:
Code: Select all
Culture: und
Timezone: (UTC+01:00) GMT+01:00 + Europe/Warsaw
Dll Version: Devart.Data.dll, v5.0.2697.0
Dll Version: Devart.Data.PostgreSql.dll, v7.20.1905.0
Dll Version: Devart.Data.PostgreSql.Entity.EF6.dll, v7.20.1905.0
Pg Version: PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
Pg Timezone: Europe/Sarajevo
Europe/Sarajevo
Exception thrown: 'System.ArgumentOutOfRangeException' in System.Private.CoreLib.dll
Exception reading datesThe UTC time represented when the offset is applied must be between year 0 and 10,000. (Parameter 'offset')
As you can see the date:
is written incorrectly as:
an attempt to read ends with an error:
Code: Select all
The UTC time represented when the offset is applied must be between year 0 and 10,000. (Parameter 'offset')
It is also interesting that between 1884 and 1883 there was some change, dates later than 1884 are written as normal, for example:
and earlier dates have oddly changed time zones:
Maybe it is connected with:
https://stackoverflow.com/questions/142 ... lliseconds
and
https://en.wikipedia.org/wiki/Internati ... Conference
I think the error is somewhere in this or an analogous piece of code that calculates dates:
Code: Select all
if (string.IsNullOrEmpty(PgSqlLocalization.TimeZone))
{
DateTime localTime = DateTime.SpecifyKind(pgSqlTimeStamp.Value, DateTimeKind.Utc).ToLocalTime();
TimeSpan timeSpan1 = PgSqlLocalization.a(localTime);
TimeSpan timeSpan2 = PgSqlLocalization.a(new DateTime(localTime.Ticks, DateTimeKind.Local));
TimeSpan timeSpan3 = timeSpan1 - timeSpan2;
pgSqlTimeStamp = pgSqlTimeStamp.AddSeconds(timeSpan1.TotalSeconds);
pgSqlTimeStamp.h = (timeSpan3.Hours * 60 + timeSpan3.Minutes) * 60;
}
Yes, I do not have PgSqlLocalization.TimeZone set, because I want dates to return with a saved time zone and not a fixed time zone.
I also send a bug report to support.