strange behaviour of format DateTime (between Expression<DateTime> and DateTime)

[darkomenx]

In context of application that work with entities and logged creation or modification data, I have this 2 fields in each of my entities :

• createdAt
• updatedAt

I work with this parameter database drift : store_date_time_values_as_text: true

to avoid of declaring this fields in each entities I have created a Mixin like that :

import 'package:drift/drift.dart';

mixin WithModificationTimestamps on Table
{
    DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
    DateTimeColumn get updatedAt => dateTime().withDefault(currentDateAndTime)();
}

AND when I persist data without explicity fill this 2 fields, Drift persist this field like this format :
2023-11-06 15:39:23

---

NOW if I need to update an entity, I need to use update statement with entity fields filled like this :

  Association updateAssociation = Association(
      id: initialvalues['id'],
      name: name,
      labelOperation: label,
      category: categoryValueID,
      thirdParty: thirdpartyValueID,
      createdAt: DateTime.parse(initialvalues['createdAt']),
      updatedAt: DateTime.now().toUtc()
  );

the result into database is :
2023-11-06T16:24:28.374052Z

---

I would like to have this format : 2023-11-06 15:39:23 everywhere and everytime in my database.

But I don't understand :

• How define concrete and stable format for my DateTimeColumn ?
• I don't understand how run the const currentDateAndTime ?
• How can I format my DateTime to persist correct format that I want ?
• am I obliged to work with Expression for column type ?

Thank you for everyone that can help me...

[simolus3]

The main benefit of storing date times as text is that we get to know about a time zone offset, if there is any. For that, we use the toIso8601String() formatting of DateTime, which gets you something like 2023-11-06T16:24:28.374052Z. We support syntax like 2023-11-06 15:39:23 as well since it is returned by sqlite3 by default, but we prefer the explicit syntax.

I would like to have this format : 2023-11-06 15:39:23 everywhere and everytime in my database.

May I ask why? If it's purely for consistency, having 2023-11-06T16:24:28.374052Z everywhere is easier to achieve.

I don't understand how run the const currentDateAndTime ?

This expression is run by the database, it's not possible to run it in Dart alone without adding it to a SQL statement and executing it.

How can I format my DateTime to persist correct format that I want ?

This is not possible with the builtin date-time type. You could store them as a text column and apply a type converter only using the format you prefer.

am I obliged to work with Expression for column type ?

You can also use clientDefault(DateTime.now) as an alternative that doesn't require expressions directly.

[darkomenx]

My first important principle let me drive is that I need to be conform and ISO in all context of my app with DateTime for all the users into the world.

Thus, I follow your recommandations for using this UTC expressions of DateTime : 2023-11-07T19:27:57.282374Z (Zulu Time ?)

For that I define my mixins like that :

import 'package:drift/drift.dart';

mixin WithModificationTimestamps on Table
{
  DateTimeColumn get createdAt => dateTime().clientDefault(DateTime.now().toUtc)();
  DateTimeColumn get updatedAt => dateTime().clientDefault(DateTime.now().toUtc)();
}

And use the same way to define my DateTime into my companion class : DateTime.now().toUtc()

I confirm that is more simple because when I retrieve DateTime to string I can again convert it like that : DateTime.parse(initialvalues['createdAt'])

Thank you for your help and your recommandations Simolus3 👍

[simolus3]

DateTimeColumn get createdAt => dateTime().clientDefault(DateTime.now().toUtc)();

Be careful with that, this will give each new row the timestamp of approximately when the app was first opened (since you're only tearing off the toUtc() function). It should probably be () => DateTime.now().toUtc() instead (with the () =>).

Also note that updatedAt will only work when you're inserting data classes, for the associated "companion" classes it won't apply the new updatedValue by default.

[darkomenx]

Datetime

Thank you for your advice. I changed my declaration into my mixins like that : () => DateTime.now().toUtc()

UpdateAt

You are right ! I already be aware of that (without find solution). My updatedAt field run correctly ONLY if I explicity specified into my companion object otherwise it will not update.

It can be boring for me to always specified this field manually. But for now I don't know how automatically filled this field.
If you have last recommandations about this I would appreciate :)

[simolus3]

It's possible, but with the requirement of using consistently-formatted date times everywhere, it gets a little more tricky.

First, add a user-defined function to the sqlite3 database that returns the current date and time, but in the expected format. You can use the setup parameter of the NativeDatabase constructor for this:

    setup: (database) {
      database.createFunction(
        functionName: 'drift_now',
        function: (args) {
          return DateTime.now().toUtc().toIso8601String();
        },
        directOnly: false,
      );
    },

With that, the mixin can be defined as follows:

const formattedNow = FunctionCallExpression<DateTime>('drift_now', []);

mixin WithModificationTimestamps on Table {
  DateTimeColumn get createdAt => dateTime().withDefault(formattedNow)();
  DateTimeColumn get updatedAt => dateTime().withDefault(formattedNow)();
}

Note that clientDefault is not necessary anymore. When the database wants to insert a new row, it calls the drift_now() function added in setup, which returns the desired format. So we can use this in place of currentDateAndTime, which formats date times slightly differently (although the two formats would be interpreted in the exact same way by drift and sqlite3).

Finally, you can use triggers to have the database engine automatically run an UPDATE statement patching the updatedAt field for any other update. We can use the fact that generated tables inherit from WithModificationTimestamps and override the allSchemaEntities getter on the database class:

  Trigger _triggerFor(WithModificationTimestamps entity) {
    // Add a trigger automatically updating the `updatedAt` column after
    // updates.
    final schema = entity as TableInfo;

    final context = GenerationContext.fromDb(this);
    final schemaName = context.identifier(schema.entityName);
    final name = context.identifier('${schema.entityName}_updated_at');
    return Trigger(
      '''
            CREATE TRIGGER $name AFTER UPDATE ON $schemaName BEGIN
              UPDATE $schemaName SET updated_at = drift_now();
            END;
          ''',
      name,
    );
  }

  @override
  List<DatabaseSchemaEntity> get allSchemaEntities {
    final entities = <DatabaseSchemaEntity>[];

    for (final entity in super.allSchemaEntities) {
      entities.add(entity);

      if (entity is WithModificationTimestamps) {
        entities.add(_triggerFor(entity as WithModificationTimestamps));
      }
    }

    return entities;
  }

Then, if you delete the database file on the device and re-create it, a trigger automatically patching the updatedAt field will be created. If you don't want to delete the database, you could write a migration doing this:

onUpgrade: (from, to, m) {
  if (from == versionWhereTheTriggerHasNotBeenAddedYet) {
    await m.create(_triggerFor(associations));
  }
}