servers/postgres/types/
interval.rs

1// Copyright 2023 Greptime Team
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7//     http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15use std::fmt::Display;
16
17use bytes::{Buf, BufMut};
18use common_time::interval::IntervalFormat;
19use common_time::timestamp::TimeUnit;
20use common_time::{Duration, IntervalDayTime, IntervalMonthDayNano, IntervalYearMonth};
21use pgwire::types::ToSqlText;
22use postgres_types::{to_sql_checked, FromSql, IsNull, ToSql, Type};
23
24use crate::error;
25
26/// On average one month has 30.44 day, which is a common approximation.
27const SECONDS_PER_MONTH: i64 = 24 * 6 * 6 * 3044;
28const SECONDS_PER_DAY: i64 = 24 * 60 * 60;
29const MILLISECONDS_PER_MONTH: i64 = SECONDS_PER_MONTH * 1000;
30const MILLISECONDS_PER_DAY: i64 = SECONDS_PER_DAY * 1000;
31
32#[derive(Debug, Clone, Copy, Default)]
33pub struct PgInterval {
34    pub(crate) months: i32,
35    pub(crate) days: i32,
36    pub(crate) microseconds: i64,
37}
38
39impl From<IntervalYearMonth> for PgInterval {
40    fn from(interval: IntervalYearMonth) -> Self {
41        Self {
42            months: interval.months,
43            days: 0,
44            microseconds: 0,
45        }
46    }
47}
48
49impl From<IntervalDayTime> for PgInterval {
50    fn from(interval: IntervalDayTime) -> Self {
51        Self {
52            months: 0,
53            days: interval.days,
54            microseconds: interval.milliseconds as i64 * 1000,
55        }
56    }
57}
58
59impl From<IntervalMonthDayNano> for PgInterval {
60    fn from(interval: IntervalMonthDayNano) -> Self {
61        Self {
62            months: interval.months,
63            days: interval.days,
64            microseconds: interval.nanoseconds / 1000,
65        }
66    }
67}
68
69impl TryFrom<Duration> for PgInterval {
70    type Error = error::Error;
71
72    fn try_from(duration: Duration) -> error::Result<Self> {
73        let value = duration.value();
74        let unit = duration.unit();
75
76        // Convert the duration to microseconds
77        match unit {
78            TimeUnit::Second => {
79                let months = i32::try_from(value / SECONDS_PER_MONTH)
80                    .map_err(|_| error::DurationOverflowSnafu { val: duration }.build())?;
81                let days =
82                    i32::try_from((value - (months as i64) * SECONDS_PER_MONTH) / SECONDS_PER_DAY)
83                        .map_err(|_| error::DurationOverflowSnafu { val: duration }.build())?;
84                let microseconds =
85                    (value - (months as i64) * SECONDS_PER_MONTH - (days as i64) * SECONDS_PER_DAY)
86                        .checked_mul(1_000_000)
87                        .ok_or(error::DurationOverflowSnafu { val: duration }.build())?;
88
89                Ok(Self {
90                    months,
91                    days,
92                    microseconds,
93                })
94            }
95            TimeUnit::Millisecond => {
96                let months = i32::try_from(value / MILLISECONDS_PER_MONTH)
97                    .map_err(|_| error::DurationOverflowSnafu { val: duration }.build())?;
98                let days = i32::try_from(
99                    (value - (months as i64) * MILLISECONDS_PER_MONTH) / MILLISECONDS_PER_DAY,
100                )
101                .map_err(|_| error::DurationOverflowSnafu { val: duration }.build())?;
102                let microseconds = ((value - (months as i64) * MILLISECONDS_PER_MONTH)
103                    - (days as i64) * MILLISECONDS_PER_DAY)
104                    * 1_000;
105                Ok(Self {
106                    months,
107                    days,
108                    microseconds,
109                })
110            }
111            TimeUnit::Microsecond => Ok(Self {
112                months: 0,
113                days: 0,
114                microseconds: value,
115            }),
116            TimeUnit::Nanosecond => Ok(Self {
117                months: 0,
118                days: 0,
119                microseconds: value / 1000,
120            }),
121        }
122    }
123}
124
125impl From<PgInterval> for IntervalMonthDayNano {
126    fn from(interval: PgInterval) -> Self {
127        IntervalMonthDayNano::new(
128            interval.months,
129            interval.days,
130            // Maybe overflow, but most scenarios ok.
131            interval.microseconds.checked_mul(1000).unwrap_or_else(|| {
132                if interval.microseconds.is_negative() {
133                    i64::MIN
134                } else {
135                    i64::MAX
136                }
137            }),
138        )
139    }
140}
141
142impl Display for PgInterval {
143    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
144        write!(
145            f,
146            "{}",
147            IntervalFormat::from(IntervalMonthDayNano::from(*self)).to_postgres_string()
148        )
149    }
150}
151
152impl ToSql for PgInterval {
153    to_sql_checked!();
154
155    fn to_sql(
156        &self,
157        _: &Type,
158        out: &mut bytes::BytesMut,
159    ) -> std::result::Result<postgres_types::IsNull, Box<dyn snafu::Error + Sync + Send>>
160    where
161        Self: Sized,
162    {
163        // https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/timestamp.c#L989-L991
164        out.put_i64(self.microseconds);
165        out.put_i32(self.days);
166        out.put_i32(self.months);
167        Ok(postgres_types::IsNull::No)
168    }
169
170    fn accepts(ty: &Type) -> bool
171    where
172        Self: Sized,
173    {
174        matches!(ty, &Type::INTERVAL)
175    }
176}
177
178impl<'a> FromSql<'a> for PgInterval {
179    fn from_sql(
180        _: &Type,
181        mut raw: &'a [u8],
182    ) -> std::result::Result<Self, Box<dyn snafu::Error + Sync + Send>> {
183        // https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/timestamp.c#L1007-L1010
184        let microseconds = raw.get_i64();
185        let days = raw.get_i32();
186        let months = raw.get_i32();
187        Ok(PgInterval {
188            months,
189            days,
190            microseconds,
191        })
192    }
193
194    fn accepts(ty: &Type) -> bool {
195        matches!(ty, &Type::INTERVAL)
196    }
197}
198
199impl ToSqlText for PgInterval {
200    fn to_sql_text(
201        &self,
202        ty: &Type,
203        out: &mut bytes::BytesMut,
204    ) -> std::result::Result<postgres_types::IsNull, Box<dyn snafu::Error + Sync + Send>>
205    where
206        Self: Sized,
207    {
208        let fmt = match ty {
209            &Type::INTERVAL => self.to_string(),
210            _ => return Err("unsupported type".into()),
211        };
212
213        out.put_slice(fmt.as_bytes());
214        Ok(IsNull::No)
215    }
216}
217
218#[cfg(test)]
219mod tests {
220    use common_time::timestamp::TimeUnit;
221    use common_time::Duration;
222
223    use super::*;
224
225    #[test]
226    fn test_duration_to_pg_interval() {
227        // Test with seconds
228        let duration = Duration::new(86400, TimeUnit::Second); // 1 day
229        let interval = PgInterval::try_from(duration).unwrap();
230        assert_eq!(interval.months, 0);
231        assert_eq!(interval.days, 1);
232        assert_eq!(interval.microseconds, 0);
233
234        // Test with milliseconds
235        let duration = Duration::new(86400000, TimeUnit::Millisecond); // 1 day
236        let interval = PgInterval::try_from(duration).unwrap();
237        assert_eq!(interval.months, 0);
238        assert_eq!(interval.days, 1);
239        assert_eq!(interval.microseconds, 0);
240
241        // Test with microseconds
242        let duration = Duration::new(86400000000, TimeUnit::Microsecond); // 1 day
243        let interval = PgInterval::try_from(duration).unwrap();
244        assert_eq!(interval.months, 0);
245        assert_eq!(interval.days, 0);
246        assert_eq!(interval.microseconds, 86400000000);
247
248        // Test with nanoseconds
249        let duration = Duration::new(86400000000000, TimeUnit::Nanosecond); // 1 day
250        let interval = PgInterval::try_from(duration).unwrap();
251        assert_eq!(interval.months, 0);
252        assert_eq!(interval.days, 0);
253        assert_eq!(interval.microseconds, 86400000000);
254
255        // Test with partial day
256        let duration = Duration::new(43200, TimeUnit::Second); // 12 hours
257        let interval = PgInterval::try_from(duration).unwrap();
258        assert_eq!(interval.months, 0);
259        assert_eq!(interval.days, 0);
260        assert_eq!(interval.microseconds, 43_200_000_000); // 12 hours in microseconds
261
262        // Test with negative duration
263        let duration = Duration::new(-86400, TimeUnit::Second); // -1 day
264        let interval = PgInterval::try_from(duration).unwrap();
265        assert_eq!(interval.months, 0);
266        assert_eq!(interval.days, -1);
267        assert_eq!(interval.microseconds, 0);
268
269        // Test with multiple days
270        let duration = Duration::new(259200, TimeUnit::Second); // 3 days
271        let interval = PgInterval::try_from(duration).unwrap();
272        assert_eq!(interval.months, 0);
273        assert_eq!(interval.days, 3);
274        assert_eq!(interval.microseconds, 0);
275
276        // Test with small duration (less than a day)
277        let duration = Duration::new(3600, TimeUnit::Second); // 1 hour
278        let interval = PgInterval::try_from(duration).unwrap();
279        assert_eq!(interval.months, 0);
280        assert_eq!(interval.days, 0);
281        assert_eq!(interval.microseconds, 3600000000); // 1 hour in microseconds
282
283        // Test with very small duration
284        let duration = Duration::new(1, TimeUnit::Microsecond); // 1 microsecond
285        let interval = PgInterval::try_from(duration).unwrap();
286        assert_eq!(interval.months, 0);
287        assert_eq!(interval.days, 0);
288        assert_eq!(interval.microseconds, 1);
289
290        let duration = Duration::new(i64::MAX, TimeUnit::Second);
291        assert!(PgInterval::try_from(duration).is_err());
292
293        let duration = Duration::new(i64::MAX, TimeUnit::Millisecond);
294        assert!(PgInterval::try_from(duration).is_err());
295    }
296}
servers/postgres/types/interval.rs

servers/postgres/types/
interval.rs
