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=DateTimeand 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()