How to migrate column type from Text to DateTime with a custom cast expression

[CalvinGonsalves]

Hello,

I'm trying to migrate a column of type Text to DateTime. I tried performing the migration like this:
dateColumn is the column I want to migrate.

migrator.alterTable(TableMigration(
    affectedTable,
    columnTransformer: {
      affectedTable.dateColumn:
         affectedTable.dateColumn.cast<DateTime>(),
    },
  ));

Doing it like this converts the Text "2021-05-27T14:19:58-04:00" to an integer 2021 which seems to be only the year. I know that seconds since epoch is being stored in moor.
So how would I be able to write a custom cast expression for this logic:
1622139598 is the seconds-since-epoch equivalent of "2021-05-27T14:19:58-04:00".

 DateTime date = DateTime.parse('2021-05-27T14:19:58-04:00').toLocal();
 print('${date.millisecondsSinceEpoch/1000}');  //1622139598

Thank you

[simolus3]

I already posted this in Gitter, but this has a better visibility :)

In sqlite3, you can convert a date time (stored as text) into UNIX seconds with strftime('%s', datetime(your date time)). The corresponding action in moor is FunctionCallExpression<DateTime>('strftime', [Constant('%s', FunctionCallExpression('datetime', table.yourDateColumn))]), which you can use in a column transformer.

[CalvinGonsalves]

Awesome. That worked with a few adjustments, missing a few brackets/parenthesis.

FunctionCallExpression<DateTime>(
          'strftime',
          [
            Constant('%s'),
            FunctionCallExpression(
              'datetime',
              [
                table.yourDateColumn,
              ],
            ),
          ],
        ),

Thanks for explanation and solution and the package as well 🙂

Edit: for some reason the "Mark as answer" button is not working