We are facing some critical issue in order to migrate to Postgres Sql. Below are the details of the issue that is happening.
Error Message:
Npgsql.PostgresException: 42883: operator does not exist: time without time zone = bigint.
The issue occurs when adding a comparison in the SQL expression for the type LocalTime (NodaTime), which has been stored in our Postgres server as TIME(0).
During the comparison process, OrmLite tries to convert the time to a DB value using our converter, which returns a timespan. I have tried returning TimeOnly as well but the same issue occurs.
During the creation of Db Command, the ormlite tries to convert the values into db parameters. Here it calls again the TimespanConverter (or TimeOnlyConverter if I return a TimeOnly from the ToDbValue method), which converts the value of the parameter to an integer. This happens in the method public virtual IDbDataParameter AddParam(object value) . Upon executing the query the above exception is thrown.
Here is the Converter that is working fine for Sql Server.
public class LocalTimeConverter : OrmLiteConverter, ISqlConverter {
public LocalTimeConverter(SqlServer2019OrmLiteDialectProvider dialectProvider) {
DialectProvider = dialectProvider;
}
public override DbType DbType => DbType.String;
public override string ColumnDefinition => "TIME(7)";
public IEnumerable<Type> ConvertsTypes() {
yield return typeof(LocalTime);
yield return typeof(LocalTime?);
}
public override object FromDbValue(Type fieldType, object value) {
if (value == null) {
return null;
}
var timeSpan = (TimeSpan) base.FromDbValue(typeof(TimeSpan), value);
var localTime = LocalTime.FromTicksSinceMidnight(timeSpan.Ticks);
return localTime;
}
public override object ToDbValue(Type fieldType, object value) {
if (value == null) {
return null;
}
var localTime = (LocalTime) value;
var timeSpan = GetTimeSpan(localTime);
return timeSpan.ToString("c", CultureInfo.InvariantCulture);
}
public override string ToQuotedString(Type fieldType, object value) {
if (value == null) {
return null;
}
var localTime = (LocalTime) value;
var timeSpan = GetTimeSpan(localTime);
return timeSpan.ToString("c", CultureInfo.InvariantCulture);
}
private TimeSpan GetTimeSpan(LocalTime localTime) {
var timeSpan = TimeSpan.FromTicks(localTime.TickOfDay);
return timeSpan;
}
}
The API call for Updating the values is below:
await db.Connection.UpdateOnlyAsync(() => new TimeWorked {
FactStatusKey = 2
},
p => p.BegunAt == stint.BegunAt &&
p.Date == date &&
p.Id == timesheetId ,
token: cancellationToken);
The schema for the table TimeWorked is:
[Alias("FactTimeWorked")]
public class TimeWorked {
[Alias("TimeWorkedKey")]
[PrimaryKey]
[AutoIncrement]
[Order(1)]
public int Key { get; set; }
[Order(3)]
[Required]
[Index(Name = "IX_TimeWorked_FactStatusKey")]
public int FactStatusKey { get; set; }
[Order(5)]
[Required]
[Index(Name = "IX_TimeWorked_Id")]
public EntityId Id => RevisionId.EntityId;
[Order(7)]
[Required]
public LocalDate Date { get; set; }
[Order(9)]
[Required]
public LocalTime BegunAt { get; set; }
}
I have truncated the properties which are not in the above context.