CODEWITHSUNDEEP
c#postgresqldapperutc
Wellspring
Wellspring

Reputation: 1340

pass UTC dates in query via Dapper

I'm posting an answer here for myself (if no one else) since I never found it laid out obviously elsewhere in SO. Lost several hours on this.

I wanted to make use of Dapper in the following manner:

await dapper.Connection().QueryAsync<T>(insertSQL, obj);

insertSQL could be, for example, an insert statement adding date fields into a table record. The obj object provides those fields' values as parameters.

The problems begin if the dates need to be of type UTC. The database fields are timestamptz, so that's fine. But how does one convince Dapper to send in UTC-aware SQL?

I banged my head on this problem for quite a while.

Upvotes: 1

Views: 3167

Answers (1)

Wellspring
Wellspring

Reputation: 1340

What worked for me: Use 'DateTimeOffset', not 'DateTime' C# types.

When you use DateTime, there are some things you can do (DateTimeKind, etc.) but they don't help Dapper send in a properly formulated statement to (in my case) PostgreSQL. You'll perhaps begin to think Dapper can't/won't help you.

But if your C# object stores dates in DateTimeOffset format, Dapper will help you just fine. So in your classes, avoid DateTime property types when you're thinking UTC style.

This is probably rather too vague, but perhaps it's enough to send the next person like me down a good road, if they are asking the same question I was asking today.

PS -- now that I understand DateTimeOffset vs DateTime, it's obvious why Dapper was failing. It was converting to PostgreSQL "DateTimeOffset" (read, timestamptz) from what it was sucking in from the C# object as a DateTime (no time zone info). It was bound to fail. Store a DateTimeOffset in your C# object, however, and when Dapper goes to provide the database with a datetime-with-timezone, Dapper will happily provide all details, including the date, time, and the relevant offset in hours.

Upvotes: 3

Related Questions