0

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.

Wellspring
  • 1,128
  • 1
  • 10
  • 19

1 Answers1

3

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.

Wellspring
  • 1,128
  • 1
  • 10
  • 19
  • Please run a [col_description(table_oid, column_number)](https://www.postgresql.org/docs/current/static/functions-info.html) (or equivalent) and tell us what PostgreSQL data type "DateTimeOffset' was translated to. – paulsm4 Jul 21 '20 at 03:16
  • Still waiting for a reply. ALSO: be sure to "accept" your answer. – paulsm4 Jul 21 '20 at 21:06
  • was holding off for several reasons. Firstly because you've got me puzzled. I specify the field type of the postgreSQL table. No idea why you're asking me to validate what the DateTimeOffset was translated to. It was put into a timestamptz field. ?? What am I missing? Anyway I will be doing further work on this hopefully very soon, but that's the second reason... I've been delayed by other tasks. – Wellspring Jul 21 '20 at 23:25
  • Q: What's confusing you? `DateTime` and `DateTimeOffset` are .Net data types. Entity Framework (or Dapper- I' don't know how or if it relates to EF) maps .Net types to underlying DB types. PostgreSQL DB types are: https://www.postgresql.org/docs/current/datatype-datetime.html. I'm curious what the mapping is: time, timestamp, timestamptz, interval or "something else". And please "accept" your answer. – paulsm4 Jul 21 '20 at 23:46
  • You've got 90k+ points, and this is the 2nd time now that you've told me to accept my own answer, something I cannot do for yet another 24 hrs, still. Additionally you're asking me to ask Postgres what a column type is, the column of a table I myself created. And I've told you I made it timestamptz. And you think that asking Postgres to give me the data type of a column I myself defined.... will tell you something about Dapper, a lightweight ORM that did not create the table. Has your child hacked your account? Is this really Paul? Or am I on drugs and I didn't know it? Stay tuned... – Wellspring Jul 22 '20 at 00:49