Storing DateTime in PostgreSQL without loosing precision

Question

I have stumped upon an issue, that whenever I load what is presumably a DateTime from Postgresql database and compare it to initial value from C# - then the data does not match. For example, here's an original DateTime in C#:

Date: {09.07.2018 00:00:00}

Ticks: 636667391123714378

Time: {13:18:32.3714378}

Same date, returned from DB (after it was saved):

Date: {09.07.2018 00:00:00}

Ticks: 636667391123714370

Time: {13:18:32.3714370}

It seems like upon save - DateTime has lost some precision. Column type in database is a Timestamp. So, 2 questions:

1. What will be a proper way of storing the DateTime in PostgreSQL, so I don't loose any precision?

I was thinking about saving a number of UNIX milliseconds into an Integer field instead, of saving DateTime as a Timestamp, i.e.:

DateTime myDT = DateTime.Now;
long ms = new DateTimeOffset(myDT).ToUnixTimeMilliseconds();

// how I do it now
var parameterOld =
    new Npgsql.NpgsqlParameter("dateInserted", NpgsqlTypes.NpgsqlDbType.Timestamp) {Value = myDT };

// how I think would be a better approach
var parameterNew =
    new Npgsql.NpgsqlParameter("dateInserted", NpgsqlTypes.NpgsqlDbType.Integer) { Value = ms };

2. Alternatively, how do I compare 2 datetimes with a highest possible precision (C#/PostgreSql)?

My current solution, which is kind of working.. But I am loosing too much precision here:

public static bool IsEqualWithLossOfPrecision(this DateTime timestamp1, DateTime timestamp2)
{
    return (
        timestamp1.Year == timestamp2.Year &&
        timestamp1.Month == timestamp2.Month &&
        timestamp1.Day == timestamp2.Day &&
        timestamp1.Hour == timestamp2.Hour &&
        timestamp1.Minute == timestamp2.Minute &&
        timestamp1.Millisecond == timestamp2.Millisecond
    );
}

Any advises on 2 questions are welcomed.

I am using the latest available version of DB (10.4), Npgsql library v. 4.0.0, .net framework 4.5ish, windows 10

Answer 1

PostgreSQL's timestamps have a precision limit of microseconds.

If you need more, store the nanoseconds in a separate bigint attribute.