strange storing DateTimeOffset
strange storing DateTimeOffset
Hi
I discovered strange storing DateTimeOffset :).
I'm saving to db 2 dates:
new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0))
and
new DateTimeOffset(1884, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0))
first is saved as (little confused way of savings):
"1883-12-31 00:22:00+01:22"
second (looks normal):
"1884-12-31 00:00:00+01"
Why this 22 minutes are added to date : new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0)) and Offset ?
Dominik
I discovered strange storing DateTimeOffset :).
I'm saving to db 2 dates:
new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0))
and
new DateTimeOffset(1884, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0))
first is saved as (little confused way of savings):
"1883-12-31 00:22:00+01:22"
second (looks normal):
"1884-12-31 00:00:00+01"
Why this 22 minutes are added to date : new DateTimeOffset(1883, 12, 31, 0, 0, 0, 0, new TimeSpan(1, 0, 0)) and Offset ?
Dominik
Re: strange storing DateTimeOffset
Im using net core 3.1 and ubuntu 20 platform
Re: strange storing DateTimeOffset
It's worse than I thought for Linux and net core 3.1 and ef 6.4. Well, a simple program that writes the date default (DateTimeOffset) to the postgres database writes the value: 0001-01-01 00: 58: 00 + 01: 22, which is a wrong time because after subtracting the offset, the value goes beyond the minimum for the DateTimeOffset type.
Caution. The value written running on windows system(exactly the same code) writes the value: 0001-01-01 01: 22: 00 + 01: 22. This is a strange notation but correct.
The devart version used was 7.17. I am asking for your opinion. I am using dotConnect Postgresql commercially.
Caution. The value written running on windows system(exactly the same code) writes the value: 0001-01-01 01: 22: 00 + 01: 22. This is a strange notation but correct.
The devart version used was 7.17. I am asking for your opinion. I am using dotConnect Postgresql commercially.
Re: strange storing DateTimeOffset
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.
Output:
As you can see the date:
is written incorrectly as:
an attempt to read ends with an error:
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:
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.
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;
}
}
}
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')
Code: Select all
default (DateTimeOffset);
Code: Select all
0001-01-01 00: 58: 00 + 01: 22
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:
Code: Select all
"1884-12-31 00: 00: 00 + 01"
Code: Select all
"1883-12-31 00: 22: 00 + 01: 22"
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;
}
I also send a bug report to support.
Re: strange storing DateTimeOffset
Does the error persist when you run the same code on Windows?
Re: strange storing DateTimeOffset
Running the code on Windows causes the date to be written like this:
This is an unusual notation, but still correct.
Let me remind you that on Linux the date is incorrect (unreadable for .net core):
Code: Select all
0001-01-01 01:24:00 +01: 24
Let me remind you that on Linux the date is incorrect (unreadable for .net core):
Code: Select all
0001-01-01 00: 58: 00 + 01: 22
Re: strange storing DateTimeOffset
Please run this code on both your Linux and Windows, specify the outputs:
Code: Select all
var dateTime = new DateTime(1883, 12, 31, 0, 0, 0);
var offest1 = System.TimeZone.CurrentTimeZone.GetUtcOffset(dateTime);
var offest2 = new DateTimeOffset(dateTime).Offset;
Re: strange storing DateTimeOffset
Test program:
Windows output:
Linux output:
interesting ...
Code: Select all
using System;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
var dateTime = new DateTime(1883, 12, 31, 0, 0, 0);
var offest1 = System.TimeZone.CurrentTimeZone.GetUtcOffset(dateTime);
var offest2 = new DateTimeOffset(dateTime).Offset;
Console.WriteLine("offest1=" + offest1);
Console.WriteLine("offest2=" + offest2);
Console.ReadLine();
}
}
}
Windows output:
Code: Select all
offest1=01:00:00
offest2=01:00:00
Linux output:
Code: Select all
offest1=01:00:00
offest2=01:24:00
interesting ...
Re: strange storing DateTimeOffset
In PostgreSQL Server, all timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.
If you do not set PgSqlLocalization.TimeZone explicitly, the provider will use "new DateTimeOffset(dateTime).Offset" for converting timestamptz value on the client side. On Linux, the result value goes beyond the range of accepted values for System.DateTime when you insert and read default(DateTimeOffset).
Our provider includes the special type PgSqlTimeStamp that handles a wider range of dates than the standard System.DateTime. But PgSqlTimeStamp could not be used in EF6 mapping.
If you do not set PgSqlLocalization.TimeZone explicitly, the provider will use "new DateTimeOffset(dateTime).Offset" for converting timestamptz value on the client side. On Linux, the result value goes beyond the range of accepted values for System.DateTime when you insert and read default(DateTimeOffset).
Our provider includes the special type PgSqlTimeStamp that handles a wider range of dates than the standard System.DateTime. But PgSqlTimeStamp could not be used in EF6 mapping.
Re: strange storing DateTimeOffset
I do not accept such arguments. I wrote a program using EF 6.4 and the NPGSQL driver and ran it on linux. The date is written as:
and reads correctly.
In the case of the Devart driver, the default DateTimeOffset is saved as:
instead of 1:22 it is 00:58 which causes that if the offset is subtracted, it goes beyond the minimum value.
This is a bug in your codes because the other driver doesn't have such problems.
I have a site license and I do not accept the fact that I pay a lot of money for the license, I get the answer that it is not a bug but a feature. Likewise, I do not accept the use of:
because the original time zone (from the moment of writing) is lost after reading.
Please contact me urgently to solve this problem.
Code: Select all
0001-01-01 01: 22: 00 + 01: 22
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 Microsoft.Extensions.Configuration;
using Npgsql;
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]));
}
reader.Close();
}
}
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()
{
var name = "Npgsql";
SetProviderFactory(providerInvariantName: name,
providerFactory: NpgsqlFactory.Instance);
SetProviderServices(providerInvariantName: name,
provider: NpgsqlServices.Instance);
SetDefaultConnectionFactory(connectionFactory: new PostgresDbConnectionFactory());
}
}
public class PostgresDbConnectionFactory : IDbConnectionFactory
{
private NpgsqlConnectionFactory ProviderFactory = new NpgsqlConnectionFactory();
public DbConnection CreateConnection(string nameOrConnectionString)
{
if (nameOrConnectionString.Contains(";"))
{
return ProviderFactory.CreateConnection(nameOrConnectionString);
}
else
{
return ProviderFactory.CreateConnection(Program.Config.GetSection("ConnectionStrings").Get<Dictionary<string, ConnectionStringSettings>>()[nameOrConnectionString].ConnectionString);
}
}
}
}
Code: Select all
0001-01-01 00: 58: 00 + 01: 22
This is a bug in your codes because the other driver doesn't have such problems.
I have a site license and I do not accept the fact that I pay a lot of money for the license, I get the answer that it is not a bug but a feature. Likewise, I do not accept the use of:
Code: Select all
PgSqlLocalization.TimeZone
Please contact me urgently to solve this problem.
Re: strange storing DateTimeOffset
We will investigate the question and contact you as soon as possible.domdee wrote: ↑Sun 27 Jun 2021 20:21 I do not accept such arguments. I wrote a program using EF 6.4 and the NPGSQL driver and ran it on linux. The date is written as:and reads correctly.Code: Select all
0001-01-01 01: 22: 00 + 01: 22
Re: strange storing DateTimeOffset
We have sent the internal build with the fix to your email.
Re: strange storing DateTimeOffset
I have a problem with starting the driver. Well, the target code is in .net core 3.1 and the delivered driver has no nuget packages. This is most likely why when attaching files:
I get an exception:
Question:
How can I test a change to .net core 3.1 with the internal build you provided me?
Do you have an internal nuget packege feed?
Code: Select all
Devart.Data.dll Devart.Data.Linq.dll Devart.Data.PostgreSql.dll Devart.Data.PostgreSql.Entity.EF6.dll Devart.Data.PostgreSql.Linq.dll
Code: Select all
An unhandled exception of type 'System.MissingMethodException' occurred in Devart.Data.PostgreSql.dll: 'Method not found:' System.String System.AppDomainSetup.get_LicenseFile () '.'
Stack trace:
> at Devart.Common.CRLicenseProvider.a (LicenseContext A_0, Type A_1, String & A_2, String & A_3, String & A_4)
> at Devart.Common.CRLicenseProvider.a (LicenseContext A_0, Type A_1, String & A_2, Boolean & A_3, String & A_4, String & A_5)
> at Devart.Common.CRLicenseProvider.a (LicenseContext A_0, Type A_1, Boolean A_2)
> at Devart.Data.PostgreSql.PgSqlProviderFactory.CreateConnection ()
> at DefaultDateTimeOffsetIssue.PostgresDbConnectionFactory.CreateConnection (String nameOrConnectionString) in c: \ work \ TestDataOffsetDevart \ DefaultDateTimeOffsetIssue \ Program.cs: line 157
> at System.Data.Entity.Internal.LazyInternalConnection.Initialize ()
> at System.Data.Entity.Internal.LazyInternalConnection.get_Connection ()
> at System.Data.Entity.Internal.LazyInternalContext.get_Connection ()
> at System.Data.Entity.Database.CreateIfNotExists ()
> at DefaultDateTimeOffsetIssue.Program.Main (String [] args) in c: \ work \ TestDataOffsetDevart \ DefaultDateTimeOffsetIssue \ Program.cs: line 64
Question:
How can I test a change to .net core 3.1 with the internal build you provided me?
Do you have an internal nuget packege feed?
Re: strange storing DateTimeOffset
Please download the corresponding NuGet package from http://download.devart.com/nuget_postgr ... 0_1924.zip.
Re: strange storing DateTimeOffset
New build of dotConnect for PostgreSQL 7.20.1930 is available for download now: viewtopic.php?f=3&t=47232.