Support the interval type when appropriate Rust types are developed

[mikedilger]

Is there a reason { name: date, oid: 1082 } is not included? I realize a timestamp is more precise, but (at the very least) existing databases may use date.

I tried to add support for it at https://github.com/mikedilger/rust-postgres/commit/7f4637956f65c9086705c444ba93d30c4f593177 but I'm not sure that's right. When I try using it, it triggers a RefCell already borrowed error (#34 or rust-lang/rust#13246)

[sfackler]

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.

[sfackler]

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.

[mikedilger]

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.

[cbruun]

Is rust-chrono a good choice?

[sfackler]

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.

[TheServerAsterisk]

@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.

[voronaam]

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.

[piperRyan]

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

[jcaesar]

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

[sfackler]

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

[sanpii]

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

chrono::Duration?

[sfackler]

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

[piperRyan]

@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.

[aloucks]

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

[LukasKalbertodt]

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?

[sanpii]

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

[ctron]

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.

[auyer]

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!

[hongquan]

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

[levkk]

I think going the way SQLx did it with PgInterval isn't a bad idea, just so we can use this data type in Rust code.