How to convert PgTimestamp to JSON?

[Boscop]

Whats a good way to convert a diesel::data_types::PgTimestamp to JSON, since it's not RustcEncodable?

[golddranks]

I used chrono::DateTime<UTC>, (you might have to use NaiveDateTime) in my model structs. It has the to_rfc3339() method, which outputs strings that is easily parseable in JavaScript. The pain in the ass is that chrono::DateTime<UTC> doesn't serialize to the rfc3339 format by itself, so you have to have some kind of workaround: for example a struct with chrono::DateTime<UTC> for fetching from the database, then convert that to struct that is identical to that but stores the timestamp as a String, and then serialize that to JSON.

[golddranks]

Btw. I've issued an issue in chrono repo some time ago to highlight the awkwardness of serialization to JSON. chronotope/chrono#115

[Boscop]

Thanks

[Boscop]

@golddranks Is this correct?

fn time_to_json(t: NaiveDateTime) -> String {
	DateTime::<UTC>::from_utc(t, UTC).to_rfc3339()
}

This results in a string like "2017-01-06T09:27:39.833109+00:00".
(I set the postgres timezone to UTC whenever my server starts (so that now() returns the UTC time), and I use NaiveDateTime in my ORM structs, and store all times as UTC with the timestamp postgres type in the db.)

And then in js:

moment(new Date(post.timeStr)).fromNow()

To show a post's relative time in the client's timezone using moment.js.

[golddranks]

I prefer using timestamptz in my schema, so I don't have to stick the timezone later on. That looks correct but don't take my word for it :D

[marcusball]

I don't know about using rustc_serialize, but if you use serde, I think you could use the #[serde(serialize_with = "path")] attribute to specify the function that should serialize the timestamp field. You can then use the aforementioned time_to_json function, or something similar, to create the string for serialization handler.

Edit: I'm dumb, this is not necessary at all. If you're using chrono, you can just add either of the features "serde", "rustc-serialize" to the chrono dependency.

[Boscop]

@marcusball Good to know, but right now I'm converting the row struct to an api view struct anyway, because not all fields should be exposed to the json api (such as users' email).

I want now() to be in UTC, so I do this at the start of my server:

database::connection().get().unwrap().execute("SET TIME ZONE 'UTC';").unwrap();

Would it be sufficient to do it once as a migration?
Or, should I do this more often than when the server starts?
Let's say the connection from server to db breaks down for some reason (db restarts) should I execute this upon all reconnections?

[sgrif]

Not sure if there's still a question that needs answering here, but the structs prefixed with Pg are always representations of how PG sends something over the wire, not structs intended for use in application code. Using chrono or SystemTime is the proper way to do this.

[golddranks]

I think that the question in a sense was: which types to use in application code models that are compatible with PgTimestamp and easily convert to JSON.

To that question the answer seems now clear: use chrono::DateTime and Swede for serializing, that works out of the box.

[golddranks]

Serde, not Swede. Damn you smartphones XD

[Boscop]

I now use NaiveDateTime and store all my times in UTC, and convert them to client time in the frontend.