Followup
| Message |
Date: 2013-03-06 23:30
Sender: Damian W
Please replace this:
case "time":
case "interval":
if (storeType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(primitiveType, (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(primitiveType, null);
}
with this:
case "time":
case "interval":
if (storeType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateTimeTypeUsage(primitiveType, (byte)facet.Value);
}
else
{
return TypeUsage.CreateTimeTypeUsage(primitiveType, null);
}
TIME and INTERVAL are NOT DateTime representations but TimeSpan! | Date: 2012-03-16 01:56
Sender: Damian W
Sorry for constantly updating, but the solution lacked...
Storing values like 100 hours with the solution posted before ended in
INTERVAL '4.04:00:00' which is 96 hours and 57.6 Seconds due to {T} formatting.
Now I have a dedicated solution for TimeSpan and the standard solution (TIME) for anything else.
Code works, but it not prettiest
case PrimitiveTypeKind.Time:
if (_value is TimeSpan)
{
TimeSpan _valueTmp = (TimeSpan)_value;
sqlText.Append("INTERVAL ");
sqlText.Append("'");
sqlText.Append(Math.Round(_valueTmp.TotalHours).ToString());
sqlText.Append(":");
sqlText.Append(_valueTmp.Minutes);
sqlText.Append(":");
sqlText.Append(_valueTmp.Seconds);
sqlText.Append(".");
sqlText.Append(_valueTmp.Milliseconds);
sqlText.Append("'");
}
else
{
sqlText.AppendFormat(ni, "TIME '{0:T}'", _value);
}
break;
When the _value is TimeSpan, I retrieve the TotalHours and cut off everything that is not a full hour. The cutted parts are retrieved by their fields (Minutes, Seconds,
Milliseconds). Then the formatted string for 100 hours looks like INTERVAL '100:0:0.0' which works great. | Date: 2012-03-16 01:25
Sender: Damian W
That's not all. For storing there as to be an additional change in VisitedExpression.cs:
OLD:
case PrimitiveTypeKind.Time:
sqlText.AppendFormat(ni, "TIME '{0:T}'", _value);
NEW:
case PrimitiveTypeKind.Time:
sqlText.AppendFormat(ni, "'{0:T}'::INTERVAL", _value);
This is also possible:
sqlText.AppendFormat(ni, "INTERVAL '{0:T}'", _value);
This also works for columns that are type 'time' because postgresql can convert INTERVAL to TIME automatically. So this should not be a big deal and enabled the use of TimeStamp for
storing and retrieving | Date: 2012-03-16 00:33
Sender: Damian W
Hi again,
in 2.0.11.93 DateTime and DateTimeOffset for timestamp and timestamptz seems to work, good job.
For interval, just use this:
<code>
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], null);
}
</code>
INSTEAD OF THIS
<code>
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet)
&& !facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStore
PrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStore
PrimitiveType["interval"], null);
}
</code>
Then it can convert interval to TimeSpan. | Date: 2012-03-15 23:52
Sender: Damian W
In the current version of NpgSQL the following code is embedded to retrieve timestamptz as DateTimeOffset:
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) && !facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}
unfortunately it does not work. When compiling and starting my application (asp.net) I receive the following error:
Der PrimitiveType ist kein DateTime-Typ.
Beschreibung: Unbehandelte Ausnahme beim Ausführen der aktuellen Webanforderung. Überprüfen Sie die Stapelüberwachung, um weitere Informationen über diesen Fehler anzuzeigen und festzustellen, wo der Fehler im
Code verursacht wurde.
Ausnahmedetails: System.ArgumentException: Der PrimitiveType ist kein DateTime-Typ.
--> PrimitiveType is not a DateTime-Type
Code not working is:
[Column("created", TypeName = "timestamptz")]
public DateTimeOffset Created { get; set; }
THis also does not work (ok, quite obvious)
[Column("created", TypeName = "timestamp")]
public DateTimeOffset Created { get; set; }
this does work:
[Column("created", TypeName = "timestamp")]
public DateTime Created { get; set; }
Moreover I do have problems with the following:
[Column("hour_limit", TypeName = "interval")]
public TimeSpan HourLimitImpl { get; protected set; }
OR
[Column("hour_limit", TypeName = "interval")]
public DateTime HourLimitImpl { get; protected set; }
The mapping code should be:
case PrimitiveTypeKind.Time:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) && !facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["interval"], null);
}
Why are you using DateTime for Interval? (Well it does not work...so, it should be not important here, but I think using TimeSpan would be a better representation of interval)
The DateTime leads to
Das angegebene Schema ist ungültig. Fehler:
(30,12) : Fehler 2019: Die angegebene Elementzuordnung ist ungültig. Der Typ 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' von Element 'HourLimitImpl' in Typ 'Gateway' ist mit
'Npgsql.interval[Nullable=False,DefaultValue=,Precision=6]' von Element 'hour_limit' in Typ 'CodeFirstDatabaseSchema.Gateway' nicht kompatibel.
The TimeSpan leads to:
Der PrimitiveType ist kein DateTime-Typ.
is there a chance that you fix it with priority please? I think interval, timestamp and timestamptz are crucial field-types.
Thanks in advance. | Date: 2012-02-02 05:18
Sender: Mirko Geffken
I ran into this problem as well and a simple (and I think correct fix for the project team) would be to replace the code block in NpgsqlProviderManifest.GetStoreType(..) that looks as follows:
<CODE>
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}
</CODE>
with
<CODE>
case PrimitiveTypeKind.DateTimeOffset:
if (edmType.Facets.TryGetValue(PrecisionFacet, false, out facet) &&
!facet.IsUnbounded && facet.Value != null)
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], (byte)facet.Value);
}
else
{
return TypeUsage.CreateDateTimeOffsetTypeUsage(StoreTypeNameToStorePrimitiveType["timestamptz"], null);
}
</CODE>
I have no access to the source tree, so if someone could verify that this is correct and commit this that would be awesome! |
|
