CODEWITHSUNDEEP
postgresqlrustquestdb
Brian Smith
Brian Smith

Reputation: 1315

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

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"]}

Upvotes: 2

Views: 955

Answers (2)

Brian Smith
Brian Smith

Reputation: 1315

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(())
}

Upvotes: 0

Masklinn
Masklinn

Reputation: 42492

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.

Upvotes: 1

Related Questions