Allow sqlite DATETIME to be mapped with time::DateTime<Utc>

[nstinus]

Context

I have a column with a DATETIME type and a default value of CURRENT_TIMESTAMP. As discussed on different issues already 1617 and 598, CURRENT_TIMESTAMP does not hold the timezone although it is how the date is taken.

As such, a time::PrimitiveDateTime can be built but not a time::DateTime.

Possible solution

Allow fields of type time::DateTime to be maped to DATETIME columns by assuming the UTC tz if everything else fails.

[nstinus]

@abonander any thoughts? I am happy to amend this as required.

[tgecho]

Is there any appetite for the non-standard DATETIME UTC thing @mehcode mentioned here? #598 (comment)

I did a rough first implementation: tgecho@2ed96e2

I don't love this overall (I'd rather it was just the default, since I have no interest in storing anything but UTC timestamps), but it's at least better than manually annotating every single query that touches a timestamp. The annotation is especially annoying when trying to do a SELECT * FROM that should just go right into a matching struct.

[d4h0]

I'd love support for DATETIME UTC, or defaulting to UTC.

What would be a disadvantage of defaulting to UTC? I'm assuming that UTC is by far the most used TZ for storing date/time data in SQLite, so why shouldn't this be the default?

This would be a breaking change, however it seems sqlx 7.0 will be released soon. So now would probably be a good time to switch to UTC by default.

[MrFoxPro]

@d4h0 could you please elaborate how does non-standard type DATETIME UTCsuppose to be used?
When I'm setting paid_until datetime utc not null default (datetime('now')) I'm getting

Error: create table "users": sqlite: unsupported type: "datetime utc"