How to add current timestamp in SQL?

[JosephKorel]

I have this table

CREATE TABLE subjects (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(24) NOT NULL,
    author_id INT REFERENCES users (id),
    code VARCHAR(12) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

But my when Drift parse created_at it is parsing as double when it's actually coming as a String. How can I add a default timestamp here?
Thanks

[simolus3]

sqlite only supports a few column types and has special rules on how type names are mapped to them, which drift is following too. There is no builtin date time in sqlite3, you'd instead use TEXT for that.

Inside of drift files, you can use the special DATETIME type name to indicate that the column stores date time values as described here. Drift will then create the column as TEXT or INTEGER depending on how you want drift to store date times. I suggest enabling the store_date_time_values_as_text builder option as described in that link, then you can use CURRENT_TIMESTAMP as a default value.

[JosephKorel]

@simolus3
Hi, sorry for the late reply. So, I changed the table's created_at to created_at DATETIME DEFAULT CURRENT_TIMESTAMP, and set store_date_time_values_as_text = true. And now it's trying to parse as .dateTime, but I'm getting this error:
Invalid radix-10 number

The date is being created as the following:
2024-06-14 14:47:06

[simolus3]

That error indicates that the date times as text option was not properly applied. store_date_time_values_as_text = true is not valid YAML syntax, did you re-run build_runner after making the change? Drift should have put this snippet in the .g.dart file on your database, can you check that it's there?

  @override
  DriftDatabaseOptions get options =>
      const DriftDatabaseOptions(storeDateTimeAsText: true);

[JosephKorel]

Not sure if this makes any difference, but my build.yaml file was actually build.yml, so after correcting it and running build_runner again, the snippet you mentioned appeared in the .g.dart file. But still, I'm still getting the same error.
If you want, this is the repo. You can check that behavior in the file lib\pages\auth\data\impl.dart method in line 43, in the createUser method.

[simolus3]

I can't reproduce the issue from a fresh checkout (well I couldn't reach the method you've mentioned but I've copied the relevant code into main and it created + returned the user fine).

I think the problem might be that you already had the database file on your device so the current table definition as persisted in your schema might be broken. You can write a migration for that, but especially if you haven't pushed a public release of your app yet, you can also just delete the local database file and restart the app to fix it.

[JosephKorel]

Thank you so much for your quik replies! Now it's working as expected.