OrmLite DateTime Save/Select issue with timestampTZ (with time zone) PostgreSQL

ServiceStack.OrmLite PostgreSQL
1

Below is the code to reproduce.

Basically any PostgreSQL table that has a timestamptz or timestamp with time zone field/column will cause the error as that DateTime is converted due to time zone.

  created new select
  Expected: 2020-01-25 04:51:04.318
  But was:  2020-01-24 22:51:04.318

Note: There isn’t a ServiceStack.DataAnnotations attribute for timestamptz so custom sql was required to reproduce the result.

drop table if exists timestamp_model;
create table timestamp_model
(
    id       bigserial not null constraint timestamp_model_pkey  primary key,
    created  timestamptz not null,
    modified timestamptz not null
);

Note 2: There was a [PgSqlTimestampTz] attribute that is now obsolete due to being incorrectly used for the array [PgSqlTimestampTzArray] (if I remember correctly).

Below is the NUnit Test to reproduce the issue

using System;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite.Tests;
using ServiceStack.Text;

namespace ServiceStack.OrmLite.PostgreSQL.Tests.Issues
{

    public class TimestampModel
    {
        [AutoIncrement]
        [PrimaryKey]
        [ReturnOnInsert]
        public long Id { get; set; }
        
        public DateTime Created { get; set; }
        public DateTime Modified { get; set; }
    }

    [TestFixtureOrmLiteDialects(Dialect.AnyPostgreSql)]
    public class TimestampOnSelectAndReSaveTests : OrmLiteProvidersTestBase
    {
        private const int MillisecondsTimeout = 2000;
        public TimestampOnSelectAndReSaveTests(DialectContext context) : base(context) { }

        private const string DropCreateTableSql = @"drop table if exists timestamp_model;
create table timestamp_model
(
    id       bigserial not null
        constraint timestamp_model_pkey
            primary key,
    created  timestamptz not null,
    modified timestamptz not null
);";

        
        [Test]
        public void test_model_with_save_custom_sql()
        {
            using (var db = OpenDbConnection())
            {
                // db.DropAndCreateTable<TimestampModel>();
                db.ExecuteNonQuery(DropCreateTableSql);

                var created = SystemTime.UtcNow.RoundToMs();

                var record = new TimestampModel()
                {
                    Created = created,
                    Modified = created
                };

                db.Save(record);
                var id = record.Id;
                
                Assert.That(record, Is.Not.Null);
                Assert.That(record, Is.Not.EqualTo(0));

                Assert.That(record, Is.Not.Null);
                Assert.That(record.Id, Is.EqualTo(id));
                Assert.That(record.Created, Is.EqualTo(created), "created new");
                Assert.That(record.Modified, Is.EqualTo(created), "modified new");

                record = db.SingleById<TimestampModel>(id);

                Assert.That(record, Is.Not.Null);
                Assert.That(record.Id, Is.EqualTo(id));
                Assert.That(record.Created, Is.EqualTo(created), "created new select");
                Assert.That(record.Modified, Is.EqualTo(created), "modified new select");

                //System.Threading.Thread.Sleep(MillisecondsTimeout);

                var modified = SystemTime.UtcNow.RoundToMs();

                record.Modified = modified;

                db.Save(record);

                record = db.SingleById<TimestampModel>(id);

                Assert.That(record, Is.Not.Null);
                Assert.That(record.Id, Is.EqualTo(id));
                Assert.That(record.Created, Is.EqualTo(created), "created update");
                Assert.That(record.Modified, Is.EqualTo(modified), "modified update");

                db.Save(record);

                //System.Threading.Thread.Sleep(MillisecondsTimeout);

                record = db.SingleById<TimestampModel>(id);

                Assert.That(record, Is.Not.Null);
                Assert.That(record.Id, Is.EqualTo(id));
                Assert.That(record.Created, Is.EqualTo(created), "created update 2");
                Assert.That(record.Modified, Is.EqualTo(modified), "modified update 2");
            }
        }

        
    }


}
2

The DateTime data type does not capture Time Zone info, .NET’s built-in data type that captures the Time Zone Info is DateTimeOffset where it will automatically use timestamp with time zone (aka timestamptz) when creating the table schema.

Note it’s not needed for this (since its the default behavior for DateTimeOffset) but if you want to use an alternative RDBMS-specific data type you’ll most likely need to register a custom Type Converter.

1 Like
3

Good to know when altering a database table from timestamp to timestamptz on an existing project! :slight_smile: