How can I avoid string conversions when ingesting timestamps to postgres in Rust?

Question

I'm using the rust-postgres crate to ingest data. This is a working example adding rows successfully:

    let name: &str = "hello from rust";
    let val: i32 = 123;
    let now: DateTime<Utc> = Utc::now();
    let timestamp = now.format("%Y-%m-%dT%H:%M:%S%.6f").to_string();
    client.execute(
        "INSERT INTO trades VALUES(to_timestamp($1, 'yyyy-MM-ddTHH:mm:ss.SSSUUU'),$2,$3)",
        &[&timestamp, &name, &val],
    )?;

This doesn't look so nice as I have to do this forward and back string conversion, I would like to be able to write something like

    let name: &str = "hello from rust";
    let val: i32 = 123;
    let now: DateTime<Utc> = Utc::now();
    client.execute(
        "INSERT INTO trades VALUES($1,$2,$3)",
        &[&now, &name, &val],
    )?;

What's the most performant way of ingesting timestamps in this way?

Edit:

Here's the returned error from the second example above

Error: Error { kind: ToSql(0), cause: Some(WrongType { postgres: Timestamp, rust: "chrono::datetime::DateTime<chrono::offset::utc::Utc>" }) }

And my cargo.toml looks like this (which has the chrono feature enabled for the rust postgres crate):

[dependencies]
chrono = "0.4.19"
postgres={version="0.19.0", features=["with-serde_json-1", "with-bit-vec-0_6", "with-chrono-0_4"]}

That depends on your DB schema, but maybe you can just store the unix timestamp of that (u64?). — Netwave
– Netwave, Commented Jan 21, 2021 at 11:07
This won't be a huge optimization though, DBMS itself would cause by far bigger latency than 2 extra string conversions — Alexey S. Larionov
– Alexey S. Larionov, Commented Jan 21, 2021 at 12:21

Masklinn · Accepted Answer · 2021-01-21 14:05:43Z

1

I think the problem is a mismatch between your postgres schema and your Rust type: the error seems to say that your postgres type is timestamp, while your rust type is DateTime<Utc>.

If you check the conversion table, DateTime<Utc> converts to a TIMESTAMP WITH TIME ZONE. The only types which convert to TIMESTAMP are NaiveDateTime and PrimitiveDateTime.

edited Jan 21, 2021 at 14:05

answered Jan 21, 2021 at 13:14

Masklinn

43.7k4 gold badges58 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Smith Over a year ago

So something along these lines should work where utc = Utc::now(); and I'm inserting &[&utc.naive_local(), &_name, &_val]?

Masklinn Over a year ago

I haven't use chrono much but I'd guess, or change the schema to store a timestamptz.

Brian Smith · Accepted Answer · 2021-02-10 11:35:54Z

As per Masklinn's response, I needed to pass a NaiveDateTime type for this to work, the full example with naive_local looks like:

use postgres::{Client, NoTls, Error};
use chrono::{Utc};
use std::time::SystemTime;

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://admin:quest@localhost:8812/qdb", NoTls)?;

    // Basic query
    client.batch_execute("CREATE TABLE IF NOT EXISTS trades (ts TIMESTAMP, date DATE, name STRING, value INT) timestamp(ts);")?;

    // Parameterized query
    let name: &str = "rust example";
    let val: i32 = 123;
    let utc = Utc::now();
    let sys_time = SystemTime::now();
    client.execute(
        "INSERT INTO trades VALUES($1,$2,$3,$4)",
        &[&utc.naive_local(), &sys_time, &name, &val],
    )?;

    // Prepared statement
    let mut txn = client.transaction()?;
    let statement = txn.prepare("insert into trades values ($1,$2,$3,$4)")?;
    for value in 0..10 {
        let utc = Utc::now();
        let sys_time = SystemTime::now();
        txn.execute(&statement, &[&utc.naive_local(), &sys_time, &name, &value])?;
    }
    txn.commit()?;

    println!("import finished");
    Ok(())
}

Collectives™ on Stack Overflow

How can I avoid string conversions when ingesting timestamps to postgres in Rust?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related