Convert all timestamps to TIMESTAMP WITH TIMEZONE

[jyn514]

This came up while working on #874, but it's good practice anyway. We store them in Rust as DateTime<Utc>, it should be the same in the database.

[Nemo157]

timestamp without timezone is easier to work with. Postgres doesn’t actually store timezones, it just converts between UTC and the connections timezone when reading.

[jyn514]

timestamp without timezone is easier to work with

Hmm, I'm curious why you say that. The first thing we always do fetching them from the database is to add the timezone: https://github.com/rust-lang/docs.rs/blob/master/src/web/releases.rs#L104

[Nemo157]

Yep, because we're explicitly storing UTC timestamps in the database. Using timestamp with timezone doesn't get you any advantage over that (other than confusion when you have a connection with a non-UTC timezone on it).

[Nemo157]

It looks like sqlx should be fine with converting between timestamp without timezone and DateTime<Utc>, it literally does the same Naive conversion when encoding and decoding as we do manually (though maybe the type checker won't like that).

[jyn514]

Unfortunately it does not:

error[E0308]: mismatched types
  --> src/web/builds.rs:52:9
   |
52 | /         query_as!(
53 | |             Build,
54 | |             "SELECT
55 | |                 builds.id,
...  |
67 | |             version,
68 | |         )
   | |_________^ expected struct `chrono::DateTime`, found struct `chrono::NaiveDateTime`
   |
   = note: expected struct `chrono::DateTime<chrono::Utc>`
              found struct `chrono::NaiveDateTime`
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

[Nemo157]

Given that launchbadge/sqlx#703 is not possible to hit in practice since the binary protocol is always used, and it not being possible to tell sqlx that our timestamp values are stored at UTC, it's probably fine to switch to timestamptz and just hope we don't run into any weirdness.

[syphar]

I had some time on my hands and created a first PR in #1223

[syphar]

we can close that one, right?