Moor: UTC is returned in local time

[Ufuk94]

First of all, thanks for this amazing package and the hard work you put into it!

I noticed that DateTime objects in UTC are returned in local time. Storing the date in Iso8601 format instead of MillisecondsSinceEpoch should solve this.

[simolus3]

Storing the date in Iso8601 format instead of MillisecondsSinceEpoch should solve this.

That would have been a good idea, but unfortunately it'd be breaking for existing users now :(

As a starting point, you could use a custom type converter.

class IsoDateTimeConverter extends TypeConverter<DateTime, String> {
  const IsoDateTimeConverter();
  @override
  DateTime mapToDart(String fromDb) {
    if (fromDb == null) {
      return null;
    } else {
      return DateTime.parse(fromDb);
    }
  }

  @override
  String mapToSql(DateTime value) {
    return value?.toIso8601String();
  }
}

In your tables, you can now do:

// replace this
DateTimeColumn foo => dateTime()();
// with this:
TextColumn foo => text().map(const IsoDateTimeConverter())();

Arguably, moor should be able to do this out of the box. I'll think about how that could be done without breaking existing databases.

[kuhnroyal]

Maybe add a new ColumnType and moor file mapping?

[NightOwlCoder]

The problem I see with this approach (converting to a string) is that we lose DateTime where and orderBy operations.

[JCKodel]

+1 for this.

Suggestion: In MSSQL, there is a special DateTime type called DateTimeOffset (it will record the date with timezone info and all queries will work as intended, no matter if comparing different timezones).

Moor could have a special type called DateTimeOffset or IsoDateTime that always convert the date to UTC while saving (so queries through different timezones will have no issues) and it always returns UTC date (so the developer knows that he must call value.toLocal() before using the date in the UI).

For instance: TableCompanion.insert(DateTime.now() should insert 2020-05-14T19:34:00.0Z on the database (notice that this is UTC, not my local GMT-03:00).

[fgblomqvist]

Wouldn't it make more sense if Moor just added a "storeAsString" option to the existing DateTime column rather than having a new type?

Regardless, I think @JCKodel is right in that the best way to avoid timezone issues is just for Moor to always convert to UTC and for the user to convert to the correct timezone when fetching. Doesn't make any sense whatsoever to rely on what timezone the object was stored in imo.

[simolus3]

Moor only stores timestamps, and toLocal or toUtc don't change the the timestamp, only the interpretation. The getters to extract individual fields from sql dates also return their values assuming the date was in GMT.

One might be able to use a type converter from DateTime to DateTime that just calls toUtc in both directions - it's doesn't change existing data and allows using the existing comparisons for DateTime. When using something like column.day.equals(someDateTime.day) one would still have to make sure that someDateTime.isUtc since it matters there.

But all of this is just workarounds, moor should have an option to store time with proper timezone information.

[JCKodel]

This is what I did:

import 'package:flutter/foundation.dart';
import 'package:moor/moor.dart';

@immutable
class DateTimeConverter extends TypeConverter<DateTime, int> {
  @override
  DateTime mapToDart(int fromDb) {
    if (fromDb == null) {
      return null;
    }

    final year = (fromDb / 10000000000000).floor();
    fromDb -= year * 10000000000000;

    final month = (fromDb / 100000000000).floor();
    fromDb -= month * 100000000000;

    final day = (fromDb / 1000000000).floor();
    fromDb -= day * 1000000000;

    final hour = (fromDb / 10000000).floor();
    fromDb -= hour * 10000000;

    final minute = (fromDb / 100000).floor();
    fromDb -= minute * 100000;

    final second = (fromDb / 1000).floor();
    fromDb -= second * 1000;

    final millisecond = fromDb;

    return DateTime.utc(year, month, day, hour, minute, second, millisecond).toLocal();
  }

  @override
  int mapToSql(DateTime value) {
    if (value == null) {
      return null;
    }

    final date = value.toUtc();

    return date.millisecond +
        (date.second * 1000) +
        (date.minute * 100000) +
        (date.hour * 10000000) +
        (date.day * 1000000000) +
        (date.month * 100000000000) +
        (date.year * 10000000000000);
  }
}

Using int to store the uncompressed date is 55 bits (both Flutter and SQLite are 64 bits - but DartJS is only 53, so this is not compatible with Flutter Web, I guess (didn't test)).

This will use a int (which is faster than string and easier to compare and order) and the date will be readable (for instance 2020-05-14T20:39:40.567-03:00 will be saved as 20200514233940567).

The converter ensures that all data in database are UTC and all data in Dart are local.

It should be easy to do queries such as:

-- Get all sales from 2020
SELECT * FROM Sales WHERE date BETWEEN 20200000000000000 AND 20210000000000000;

-- Get all sales this month
SELECT * FROM Sales WHERE date BETWEEN 20200500000000000 AND 20200600000000000;

[FLovison]

Very good job, @simolus3 , I love this package!
My cent on this question: I agree with store datetime in utc, so if I want filter with local datetime, I'm using this extensions,
that use sqlite function _DATE_ (or _DATETIME_) with 3th parameter _localtime_

extension LocalDateTimeExpressions on Expression<DateTime> {
  Expression<String> get dateLocal {
    return FunctionCallExpression(
      "DATE", // or DATETIME for date + time
      [
        this,
        const Constant<String>("unixepoch"),
        const Constant<String>("localtime")
      ],
    );
  }
}

extension LocalCompareDateDB on GeneratedDateTimeColumn {
  Expression<bool> dateLocalEquals(DateTime value) {
    return this.dateLocal.equals(value.toIso8601String().substring(0, 10));
  }
}

So I can use this method into where SQL:

query.where(tableName.fieldDate.dateLocalEquals(DateTime.now()));