Rust Postgres Interval DataType Insert Problem

[QuasiMoto]

Anyone can shed some light on how to insert a postgres datatype of interval. I am using Rust-Postgres driver.

[2e71828]

This is really more of a postgres question than a rust question— the answer will be the same regardless of which language you’re using to talk to the database. Using the technique from this StackOverflow question, you should be able to do something like this (untested):

client.execute(
    "INSERT INTO months (name, len) VALUES ($1, $2 * interval '1 day')",
    &["January", 31],
)?;

[Cocalus]

Unreleased versions of sqlx should support converting Duration to Interval directly. But you probably want to just use make_interval from PostgreSQL: Documentation: 11: 9.9. Date/Time Functions and Operators. Simplest is probably to use make_interval(secs => $1) where $1 from Duration::as_secs_f64

[QuasiMoto]

Thank you for your replies. However, I still can't seem to figure it out. I am new to Rust, so it's a learning curve for me. This is the statement that I am having issues with.

    let duration: &str = "00:55:00";

    println!("{:?}", duration);
    let rows_updated = ctx
        .db_connect()
        .execute(
            "update activity_instance set duration = $2 where id = $1",
            &[&activity_instance.id, &duration],
        )
        .unwrap();

$1 is of type UUID and $2 is string. This is the exception i am getting when trying to execute the code above:

"thread '' panicked at 'called Result::unwrap() on an Err value: Error { kind: ToSql(1), cause: Some(WrongType { postgres: Interval, rust: "&str" }) }',"

I also tried using pg_Interval::Interval crate to convert to postgres Interval type, but with no avail.

    let duration: Interval = match Interval::from_postgres("1 hours 55 minutes 0 seconds") {
        Ok(a) => a,
        _ => unreachable!(),
    };
   
    let rows_updated = ctx
        .db_connect()
        .execute(
            "update activity_instance set duration = $2 where id = $1",
            &[&activity_instance.id, &duration],
        )
        .unwrap();

But it can't compile... Exception:

"the trait bound pg_interval::Interval: activity_instance::repository::postgres::types::ToSql is not satisfied
required for the cast to the object type dyn activity_instance::repository::postgres::types::ToSql + std::marker::Sync"

Any help would be much appreciated.

[2e71828]

I suspect you’ll need to send a number or string across and have Postgres do the translation. Something like (untested):

let duration: usize = 55;

println!("{:?}", duration);
let rows_updated = ctx
    .db_connect()
    .execute(
        "update activity_instance set duration = make_interval(minutes => $2) where id = $1",
        &[&activity_instance.id, &duration],
    )
    .unwrap();

[QuasiMoto]

Thanks a lot, it worked like a charm.

Here is the snipped that worked...

    let duration_string: String = match activity_instance.duration {
        Some(a) => a,
        _ => unreachable!(),
    };
    let duration_array: Vec<&str> = duration_string.split(":").collect();
    let (hours, mins, secs) = deconstruct_duration(duration_array);
    let rows_updated: u64 = ctx
        .db_connect()
        .execute(
            "update activity_instance set duration = make_interval(hours := $2, mins := $3, secs := $4) where id = $1",
            &[&activity_instance.id, &hours, &mins, &secs],
        )
        .unwrap();

And function:

fn deconstruct_duration(duration_array: Vec<&str>) -> (i32, i32, f64) {
    let hours: i32 = match duration_array[0].parse::<i32>() {
        Ok(a) => a,
        _ => unreachable!(),
    };

    let mins: i32 = match duration_array[1].parse::<i32>() {
        Ok(a) => a,
        _ => unreachable!(),
    };

    let secs: f64 = match duration_array[2].parse::<f64>() {
        Ok(a) => a,
        _ => unreachable!(),
    };

    (hours, mins, secs)
}

[TomP]

Please read Forum Code Formatting and Syntax Highlighting and edit your prior two posts (#4 and #6 in this thread) by using the edit button under each of those posts.

Many readers of this forum will ignore long code snippets that do not follow the posting guidelines for new contributors that are pinned to the top of this forum. Even those who do respond may feel that the lack of following the forum posting guidelines is disrespectful of their time. Thanks.

[QuasiMoto]

Fixed...