Support datetime2 type

[giggio]

Datetime2 supports precision for Sql Server, as shown on the docs. I am not sure in which version this was included, but I guess that since 2008. We should be able to map that.

[jzabroski]

You can write your own extension method that does this, too. Just use your extension method to wrap this.Execute.Sql

[jzabroski]

Here is a simple demo:

        public static ICreateTableColumnOptionOrWithColumnSyntax AsTsqlDateTime2(this ICreateTableColumnAsTypeSyntax syntax, int precision)
        {
            if (precision < 0) throw new ArgumentOutOfRangeException(nameof(precision));
            if (precision > 7) throw new ArgumentOutOfRangeException(nameof(precision));

            return syntax.AsCustom($"DateTime2({precision})");
        }

[giggio]

Yes, I am aware. But this is the type of thing that should be in the box, I believe. datetime2 is the recommended type, as seen on the docs:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

(as seen here)

[jzabroski]

For SQL Server, yes, but I am not even sure how AsDateTime2 got added to FluentMigrator in the first place as a core extension: https://github.com/fluentmigrator/fluentmigrator/blob/e82aafa20e6dbe3cefa221303fe23cf8bf59fffd/src/FluentMigrator/Builders/ExpressionBuilderWithColumnTypesBase.cs#L120

@fubar-coder even says it was a mistake to add DateTime2 to the core extensions:

W3Resource actually has the full mapping here: https://www.w3resource.com/sql/data-type.php#DATETIME

DBMS and version	Types
MySQL 5.7	DATE, TIME, DATETIME, TIMESTAMP, YEAR
PostgreSQL 9.5.3	TIMESTAMP [ WITHOUT TIME ZONE ], TIMESTAMP WITH TIME ZONE, DATE, TIME[ WITHOUT TIME ZONE ], TIME WITH TIME ZONE,
SQL Server	DATE, DATETIME2, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME
Oracle 11g	DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

Below are some ideas - nothing concrete yet. The TODO might be, for 4.0 (not yet released, major breaking changes allowed):

• [ ] Move AsDateTime2 into a provider-specific extension
• [ ] Consider adding backward compatibility [Obsolete] attribute on old AsDateTime2 method so that developers are warning about how to migrate, with technical advisory that it will be removed in say 5.0
• [ ] Consider writing Roslyn Analyzer that automatically refactors such warnings. - More just fun to get more into writing analyzers than truly useful. However, it could open the door for people to add interesting analyzers to migrations
• [ ] Create As AnsiDateTime which is effectively TIMESTAMP(n)
• [ ] Consider adding a fluent WithOffset or WithTimeZone extension, which would convert say a DateTime2 to a DateTimeOffset in the case of SQL Server
• [ ] Write up documentation explaining how each database provides support for ANSI DateTime with precision

[giggio]

As datetime2 is the recommended type, that is what should be used for Sql Server dates by default, IMO. I did not know that a AsDateTime2 method existed, I was actually asking for one. And I agree it should be sql server only, so, yes, it should eventually be removed from the base API. I also like the idea of having AsAnsiDateTime, WithOffset, WithTimeZone. It seems other dbs support some of those as well.

[jzabroski]

There is no such thing as a default DateTime type for SQL Server - all data types must be explicitly chosen. As a matter of practice, DateTime2(7) works the best since it supports all scenarios, including temporal tables for system auditing (DateTimeOffset cannot be used on GENERATED ALWAYS columns).

I think having a default behavior and letting people configure it can be rather confusing and result in unexpected behavior for companies using FluentMigrator where they allow on-premise deployment to multiple different database types.

We can perhaps look at what Entity Framework Migrations do as a comparison and see what that user community says about the experience. But I also wonder if that is such a small (but important) user base that it's hard to solicit feedback.

Do you just target SQL Server?

[jzabroski]

• [ ] Research having both WithOffset and WithTimeZone, that might needed to support different databases. e.g. IIRC Oracle may support storing columns in a different time zone than the server time, such that GETDATE() or Oracle equivalent would be auto-converted into the time zone of the target column. Does anyone know the answer? I last worked on Oracle 7 years ago.

[giggio]

My current project is Sql Server, so that is what I am targeting right now.

[fubar-coder]

SQLite for example doesn't know time zones 😅

[jzabroski]

You could say SQLite is often zoned out :)

[dreaminhex]

I'd like to revisit this. I am using FluentMigrator for both MSSQL and Postgres, but the issue is specifically with the datetime2 type in MSSQL. In our tables, we utilize it as datetime2(0). If you do not specify precision in your statement, MSSQL defaults to a precision of 7.

Yes, there are straightforward workarounds, like the following:

this.Execute.Sql("ALTER TABLE SomeTable ADD SomeColumn datetime2(0) NULL");

But I think the point is that if the type extension for DateTime2 exists (it does) and DateTime2 supports precision (it does), then I think it's reasonable to expect it to work out of the box similarly to AsDecimal().

e.g. this.Create.Column("SomeColumn").OnTable("SomeTable").AsDateTime2(0);

[jzabroski]

Changing the default precision is not going to happen, as it's a significant breaking change.

I also don't think it makes a lot of sense to change the behavior just because one person uses DateTime2(0) as if it were the T-SQL Date type. I can even somewhat understand why you would want to use only DateTime2(x) in your code, given Entity Framework 6 (legacy EF) and various other libraries often get tripped up when you have multiple C# DateTime to SQL Data Type mappings in your code. So, I am not criticizing your architecture around date time choices.

That said, customizing data types is a general, open ticket I am open to having someone working on. See #1063 - In theory, a custom type map would also therefore map the default parameters for data types that take length, scale or precision. Users who don't like the defaults could then inject a type map that overrides the default parameters. If you want to work on a PR for this, let me know. It's a fairly large, binary breaking change, so it would need to go in a 4.0 release, so it 100% needs some coordination before you do it.

In your example, you would configure an ITypeMap that maps FluentMigrator's DateTime to DateTime(0), and you would not even need AsDateTime2. This goes along with the observation that, for most systems, architects want to enforce a single way of specifying date and time. At most, I want two ways, personally - DateTime2(x) for almost all scenarios, and DateTimeOffSet for applications that require math on time zones in order to properly track un-representable times and times that occur twice in the same day. For example, the local time 3/8/2015 2:52:21 AM is invalid in time zone America/Los_Angeles - it does not exist because it's a "skipped time" and is thus not representable/constructable value.

As for a more elegant workaround than this.Execute.Sql, you can create an extension method that calls IfDatabase with the processor id for sqlserver and postgres.