By default, DynamoDB converts .NET DateTime to a string type (S) and stores dates in ISO-8601 format. See supported data types. The problem is .NET captures 7 digits for ticks whereas ISO-8601 stores 3. So, if I were to store a DateTime, fetch the entity, and compare the .NET date to what is fetched (for example in a test), this will fail.

TimeOfDay comparisons:

15:28:21.4731507 (.NET)
15:28:21.473 (DynamoDB)

Another problem I see is the DynamoDB SDK will convert UTC to local time when items are being retrieved. See a reported issue with a workaround. The suggested fix does not work for me. The converter will successfully convert the date via ToUniversalTime(), but it seems the returned value is being ignored. Thus, I still get a local time.

I'd like to avoid storing dates in ISO-8601. I know that I can store the ticks as a long property in my entities. This eliminates both problems described above. The caveat is I won't be able to use a DateTime property type anywhere in my entities going forward.

This brings me to my question. Is it possible to override the default SDK behavior so that DateTime is stored as a number type (N) in DynamoDB?

CodePudding user response：

To address the issue of truncated ticks, you can use the "o" date format string, which is the "round trip" date format, for this exact scenario. The GitHub example wasn't specifying a format in its ToEntry lambda, so I believe it was further truncating information when ToString() was then called without any format information.

You can modify the sample in GitHub like so:

public class CustomDateTimeConverter : IPropertyConverter
    {
        public DynamoDBEntry ToEntry(object value)
        {
            DateTime? dateTimeValue = value as DateTime?;
            if (dateTimeValue == null)
            {
                throw new ArgumentOutOfRangeException();
            }

            string dateTimeString = dateTimeValue?.ToUniversalTime().ToString("o");
            DynamoDBEntry entry = new Primitive
            {
                Value = dateTimeString,
            };
            return entry;
        }

        public object FromEntry(DynamoDBEntry entry)
        {
            Primitive primitive = entry as Primitive;
            if (primitive == null || !(primitive.Value is string) || string.IsNullOrEmpty((string)primitive.Value))
            {
                throw new ArgumentOutOfRangeException();
            }

            string dateTimeString = primitive.Value as string;
            DateTime dateTimeValue = DateTime.Parse(dateTimeString).ToUniversalTime();

            return dateTimeValue;
        }
    }

Few things to note here:

• We're calling ToString with "o" to get the round-trip format
• We force to UniversalTime before we call ToString("o"); to get the "Z" format of the string instead of the offset format no matter if you pass a DateTimeKind.Local or DateTimeKind.Utc in.
• We force to UniversalTime in FromEntry as well, since you seemed to want to use Utc. When comparing DateTime, it's always worth comparing .Kind as well.

We still add this to our context, as in the GitHub example:

context.ConverterCache.Add(typeof(DateTime), new CustomDateTimeConverter());

Note the impact that setting the output DateTimeKind has via this example:

var utcDate = DateTime.UtcNow;
// Create a book.
DimensionType myBookDimensions = new DimensionType()
{
    Length = 8M,
    Height = 11M,
    Thickness = 0.5M,
};

Book utcBook = new Book
{
    Id = 501,
    Title = "UtcBook",
    Isbn = "999-9999999999",
    BookAuthors = new List<string> { "Author 1", "Author 2" },
    Dimensions = myBookDimensions,
    publishedOn = utcDate,
};

var localDate = DateTime.Now;
Book localBook = new Book
{
    Id = 502,
    Title = "Local Book",
    Isbn = "999-1111",
    BookAuthors = new List<string> { "Author 1", "Author 2" },
    Dimensions = myBookDimensions,
    publishedOn = localDate,
};

// Add the book to the DynamoDB table ProductCatalog.
await context.SaveAsync(utcBook);
await context.SaveAsync(localBook);

// Retrieve the book.
Book utcBookRetrieved = await context.LoadAsync<Book>(501);
Console.WriteLine(@$"Original: {utcDate.TimeOfDay}({utcDate.Kind}) => Saved: {
    utcBookRetrieved.publishedOn.TimeOfDay}({utcBookRetrieved.publishedOn.Kind}). Equal? {
        utcDate.TimeOfDay == utcBookRetrieved.publishedOn.TimeOfDay} ToUniversal() Equal? {
            utcDate.ToUniversalTime().TimeOfDay == utcBookRetrieved.publishedOn.ToUniversalTime().TimeOfDay}");

Book localBookRetrieved = await context.LoadAsync<Book>(502);
Console.WriteLine(@$"Original: {localDate.TimeOfDay}({localDate.Kind}) => Saved: {
    localBookRetrieved.publishedOn.TimeOfDay}({localBookRetrieved.publishedOn.Kind}). Equal? {
        localDate.TimeOfDay == localBookRetrieved.publishedOn.TimeOfDay} ToUniversal() Equal? {
            localDate.ToUniversalTime().TimeOfDay == localBookRetrieved.publishedOn.ToUniversalTime().TimeOfDay}");

This prints:

Original: 20:12:52.4810270(Utc) => Saved: 20:12:52.4810270(Utc). Equal? True ToUniversal() Equal? True
Original: 15:12:52.4812120(Local) => Saved: 20:12:52.4812120(Utc). Equal? False ToUniversal() Equal? True

You can also use this method to export ticks, instead. Just change the ToEntry/FromEntry to convert to the number ticks.