adamlofts
DATETIME(fsp) has its fractional part set to zero
Hi, when there is a DATETIME (or DATETIME(0)) type column in the database, the 'select' command of this column returns the full date and time, accurate to the second. However, using the DATETIME(fsp) type, where fsp has a value of 1..6 is ignored by the mysql1 package. When reading this column, the fraction of a second (e.g. for fsp = 3 these are milliseconds) has a value of zero. For example, the value written in such a column '2025-01-12 14:45:13.774' when read has the value '2025-01-12 14:45:13.000'. And this is not an error in using this package, but probably an oversight in this package, because even during debugging, looking inside Results, you can see that the read value of this column has the fraction of a second set to zero.
results._rows[0].fields["creat"] = DateTime (2025-01-12 14:45:13.000Z)
results._rows[0].fields["creat"]._value = 1736693113000000
results._rows[0].fields["creat"].microsecondsSinceEpoch = 1736693113000000
results._rows[0].fields["creat"].millisecondsSinceEpoch = 1736693113000
To get around this, you have to deal with SQL, e.g. changing the value of this column to a double value by using the unix_timestamp function, multiplying by 1000 and then changing the type to signed. And in the program code, you have to change the received value of type int to a value of type DateTime by using the DateTime.fromMillisecondsSinceEpoch method.
SQL:
cast(unix_timestamp(creation)*1000 as signed)
Dart code:
DateTime dcreation = DateTime.fromMillisecondsSinceEpoch(row[5]);
You have to modify SQL and program code a bit, but it works. But it would be much better if mysql1 took into account values from columns of type DATETIME(fsp) :)
