NPoco, PostgreSQL and DateTimeOffset

Setting up PostgreSQL to run with NPoco is very simple. Firstly you need to install the Npgsql driver from NuGet. From the Package Manager Console type:

Install-Package Npgsql

Once this has been installed, we need to make a change to the web.config.

  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" 
           invariant="Npgsql" 
           description="Data Provider for PostgreSQL" 
           type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

Now we are setup to create our connection string. Here is a sample one.

<add name="Conn" 
    connectionString="Server=localhost;Database=db;User Id=user1;
                      Password=user1;Use Extended Types=true" 
    providerName="Npgsql"/>

The important part here is that you use the “User Extended Types=true”. This will return “TimeStampTz” types using Npgsql’s own type “NpgsqlTimeStampTZ” instead of a plain DateTime. Note: Npgsql does not automatically convert “timestamptz” types to DateTimeOffset, but using NPoco’s built in Mapper we can do this ourselves.

if (DestType == typeof(DateTimeOffset) || DestType == typeof(DateTimeOffset?)
    || DestType == typeof(DateTime) || DestType == typeof(DateTime?))
{
    return x =>
    {
        if (x is NpgsqlTimeStampTZ)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTimeOffset))
                return (DateTimeOffset)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTimeOffset?))
                return (DateTimeOffset?)((NpgsqlTimeStampTZ)x);
        }
        if (x is NpgsqlTimeStamp)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlTimeStamp)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlTimeStamp)x);
        }
        if (x is NpgsqlDate)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlDate)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlDate)x);
        }

        return x;
    };
}

Here we check if the Destination Type is “DateTime” or “DateTimeOffset” and convert the Npgsql type into the CLR type. To wire this Mapper up we do the following:

var db = new Database("Conn") { Mapper = new Mapper() };

Usually I will create a static method that will create my Database so that I don’t have to write this every time. eg.

public class DbHelper
{
    public static IDatabase GetDb()
    {
        return new Database("Conn") { Mapper = new Mapper() };
    }
}

Now I can call DbHelper.GetDb() everywhere I need my database and the mapper will be plugged in appropriately.

As you can see, it is very easy to get up and running with NPoco and PostgreSQL. Please leave a comment if you have any questions.

Adam