Json, PostgreSQL, Diesel, Timestamp

help
1

I get a json timestamp and want to store that in a Postgresql database. I am drowning in all the types.

input (from google location export):

"timestamp": "2019-12-31T13:29:28.613Z"

my up.sql:

CREATE TABLE IF NOT EXISTS public.log_google
(
    time_visited timestamp with time zone NOT NULL,
    latitude double precision NOT NULL,
    longitude double precision NOT NULL,
    accuracy integer,
    CONSTRAINT log_google_pkey PRIMARY KEY (time_visited)
)

And then, I first parse it into a intermediate struct:

#[derive(Deserialize)]
#[allow(dead_code, non_snake_case)]
struct Location {
    latitudeE7: i64,
    longitudeE7: i64,
    accuracy: isize,
    source: String,
    deviceTag: i64,
    timestamp: DateTime<Utc>,
}

So far so good, but here is the first question is DateTime<Utc> the most convenient choice?

And then to the next struct for PostgreSQL storage:

#[table_name = "log_google"]
pub struct LogGoogle {
    pub time_visited: Timestamp,
    pub latitude: f64,
    pub longitude: f64,
    pub accuracy: isize,
}

Here again, is Timestamp the one to use?
Any suggestions for optimal route from JSON to DB are welcome :slight_smile:
And also how to convert between those types

2

diesel::sql_types::Timestamp (or any other type in that module) is a so called zero sized type. These are structs without fields, which means they cannot hold any value. Diesel uses them to verify that a certain rust type matches the sql side type of your database. You shouldn't use them as struct field at all. The API documentation of each of those types contains a list of compatible rust types, that are automatically mapped by diesel to the corresponding database type. Your struct should contain one of these types.

As an additional note: #[derive(Insertable)] is designed to be used on the same struct as #[derive(Deserialize)]. You just use the matching types there. Then you can just insert from this struct directly into the database. The All about Inserts guide on the diesel website has more information about how inserts work for diesel.

1 Like
3

As for the first question: yes, you should store dates as UTC in a database, so as to avoid confusion. Proper localization/formatting/conversion to a different time zone belongs in the presentation layer, not in the DB.

4

Side note:

Regarding PostgreSQL, "timestamp with time zone" or short TIMESTAMPTZ is the correct type to use in the database.

Despite the name, the time zone will not actually be stored as part of the value in PostgreSQL (but be part of input and output when storing or retrieving the timestamp). This can be a bit counterintuitive.

5

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.