New to Telerik Reporting? Download free 30-day trial

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

  1. Install or add without installation, the Npgsql data provider with version 6.0 or newer.
  2. Create a SqlDataSource with a query that has a parameter with DbType=DateTime and map it to the value of a Report Parameter of Type=DateTime
  3. 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()

See Also

In this article