Cannot Write DateTime with Kind=Unspecified to PostgreSQL
Environment
Product | Progress® Telerik® Reporting |
Data Source Component | SqlDataSource |
Data Provider | Npgsql 6+ |
Description
I have a PostgreSQL database and I am using the Npgsql data provider to connect to said database. I filter the query by date and also have a Report Parameter of type DateTime
that I pass to the SqlDataSource component. On previewing the report, there are red error messages in place of my data items.
Steps to Reproduce
- Install or add without installation, the Npgsql data provider with version 6.0 or newer.
- Create a SqlDataSource with a query that has a parameter with
DbType=DateTime
and map it to the value of a Report Parameter ofType=DateTime
- Set the SqlDataSource as the data source of the report or a data item inside the report, and preview it.
Error Message
Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
Cause\Possible Cause(s)
Starting with version 6.0 of the Npgsql data provider, UTC timestamps have been cleanly separated from non-UTC timestamps, aligning with the PostgreSQL types. The former is represented by timestamp with time zone
and DateTime with Kind UTC
, the latter by timestamp without time zone
and DateTime with Kind Local
or Unspecified
.
Suggested Workarounds
Since the Npgsql data provider now expects a UTC value, we can work around the problem by calling the DateTime.ToUniversalTime method on the Value
property of the DateTime Report Parameter.
For example:
= Parameters.Parameter1.Value.ToUniversalTime()