Support the `interval` type when appropriate Rust types are developed about rust-postgres HOT 19 OPEN

We can easily implement ToSql for Duration, but any FromSql implementation would be very wrong.

Is there any reason why we can't have the ToSql trait implemented even if FromSql isn't possible? It would be really nice to be able to use std::time::Duration for things like:

SELECT foo_id FROM foo WHERE foo.expire_ts > CURRENT_TIMESTAMP + INTERVAL $1

from rust-postgres.

This affects me as well. My current workaround is

conn.execute("DELETE FROM test WHERE ts < now() - $1::text::interval",
                    &[&format!("{}ms", retention)]);

The ::text part is required for the library to map the type and it is later converted into an interval on the postgres side.

The @TheServerAsterisk suggestion makes sense to me. Though I'd suggest having a new variant which takes in std::time::Duration. But this is not important.

from rust-postgres.

I believe I left it off because it doesn't match up 1:1 with an existing Rust type. Timespec is a significantly higher precision type, and I'm a bit leery of silently lossy conversions in the Rust -> Postgres direction. That being said, mapping it to Timespec isn't the end of the world, though I wouldn't be surprised if date had a different wire format than timestamp, which would require some changes to the ToSql and FromSql implementations.

from rust-postgres.

Another example is the interval Postgres type. I initially thought that it would be a straightforward 1-1 mappinng with the std::time::Duration Rust type. However, interval stores a number of microseconds and separately a number of days and months. The days and months aren't converted into microseconds until the interval is matched with a time, as the length of a day or month will vary. We can easily implement ToSql for Duration, but any FromSql implementation would be very wrong.

from rust-postgres.

I solved my immediate issue by using timestamp in the database.

I agree Timespec is the wrong rust type to map Postgres date to.

Yes, there is no Rust type that maps to Postgres interval yet. Someone was supposed to be working on a Jodatime-like / JSR-310 datetime system. Hopefully that will have a ReadablePeriod type, which maps to Postgres interval.

from rust-postgres.

Is rust-chrono a good choice?

from rust-postgres.

I've added support mapping TIMESTAMP WITH TIME ZONE to DateTime<UTC>, TIMESTAMP to NaiveDateTime, DATE to NaiveDate, and TIME to NaiveTime under the chrono feature. There's still no equivalent for INTERVAL as far as I know.

from rust-postgres.

@sfackler would there be interest if I wrote a "simple" mapping for the interval type? More specifically it would have the following interface and other additions if required:

pub fn new(microseconds: i64, days: i32, months: i32) -> Interval;
pub fn microseconds(&self) -> i64;
pub fn days(&self) -> i32;
pub fn months(&self) -> i32;
pub fn to_sql_standard(&self) -> String; 
pub fn to_postgres(&self) -> String; 
pub fn to_postgres_verbose(&self) -> String; 
pub fn to_iso_8601(&self) -> String; 

and will also implement the Clone, Copy, PartialEq, Eq, Debug, Add, and Sub traits.

Any input would be appreciated.

from rust-postgres.

I have a dedicated interval type here if anyone is interested.

from rust-postgres.

How foolish is it to wish for std::time::Duration to be converted to intervals?

from rust-postgres.

A Duration could be converted to a Postgres interval, but not vice versa: #60 (comment).

from rust-postgres.

There's still no equivalent for INTERVAL as far as I know.

chrono::Duration?

from rust-postgres.

As noted in the comment directly above yours, those types aren't equivalent: #60 (comment)

from rust-postgres.

@sanpii I wrote a crate that is direct binding for interval type see my comment above.

@sfackler if you would like the code I have so far added this crate please let me know.

from rust-postgres.

I attempted to add ToSql for Duration but I didn't get far, mostly because I was unable to find good documentation. I'm leaving some notes and questions in this thread in order to hopefully help the next one attempting this.

Above and in the docs, it is mentioned that intervals consists of months, days and microseconds. Further, interval is 16 bytes large and months & days are ints. So I assume it's something like this?

// Maybe?
struct Interval {
    months: i32,
    days: i32,
    microseconds: i64,
}

But I'm not sure and I couldn't find any information on how those are written to the binary buffer. I briefly tried to dig through the postgres code, but gosh, I haven't read C code in a long time. And I just couldn't find anything relevant.

Finally, how would one convert Duration into Interval? @sfackler you said:

We can easily implement ToSql for Duration

But how exactly? Especially since days and months vary in length (the whole point of this weird representation), one surely couldn't do any seconds % 24 * 60 * 60 math, right? And in that case the seconds: u64 from Duration cannot be represented by microseconds: i64, right?

from rust-postgres.

So I assume it's something like this?

Yes, if you prefer I did the job for my crate: https://github.com/elephantry/elephantry/blob/3.0.0-beta.2/core/src/sql/date/interval.rs

from rust-postgres.

I was running into the same limitation. Using chrono::Duration, is it possible to transform the duration into an ISO 8609 duration (like PT1S = 1 second), this can be fed into a statement using the type varchar and converting it to an interval in the statement. Like $1::interval.

Not sure if that is a recipe to implement this out-of-the-box or even the other way around (from postgres to Duration). But it worked for me.

from rust-postgres.

Sorry for pinging, but what is the best approach to solve this?
I see that there are crates referenced in this issue that attempt to fix this issue. Is this enough ?
If not, what is ? chrono::Duration support ? Official Rust type support ? If any of there, does issues about this exist ?
Thanks!

from rust-postgres.

There is a crate, chrono-intervals that provides Interval type.

from rust-postgres.