servers/postgres/types/
datetime.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 bytes::BufMut;
16use chrono::{NaiveDate, NaiveDateTime};
17use pgwire::types::ToSqlText;
18use pgwire::types::format::FormatOptions;
19use postgres_types::{IsNull, ToSql, Type};
20use session::session_config::{PGDateOrder, PGDateTimeStyle};
21
22#[derive(Debug)]
23pub struct StylingDate(pub NaiveDate, pub PGDateTimeStyle, pub PGDateOrder);
24
25#[derive(Debug)]
26pub struct StylingDateTime(pub NaiveDateTime, pub PGDateTimeStyle, pub PGDateOrder);
27
28fn date_format_string(style: PGDateTimeStyle, order: PGDateOrder) -> &'static str {
29    match style {
30        PGDateTimeStyle::ISO => "%Y-%m-%d",
31        PGDateTimeStyle::German => "%d.%m.%Y",
32        PGDateTimeStyle::Postgres => match order {
33            PGDateOrder::MDY | PGDateOrder::YMD => "%m-%d-%Y",
34            PGDateOrder::DMY => "%d-%m-%Y",
35        },
36        PGDateTimeStyle::SQL => match order {
37            PGDateOrder::MDY | PGDateOrder::YMD => "%m/%d/%Y",
38            PGDateOrder::DMY => "%d/%m/%Y",
39        },
40    }
41}
42
43fn datetime_format_string(style: PGDateTimeStyle, order: PGDateOrder) -> &'static str {
44    match style {
45        PGDateTimeStyle::ISO => "%Y-%m-%d %H:%M:%S%.6f",
46        PGDateTimeStyle::German => "%d.%m.%Y %H:%M:%S%.6f",
47        PGDateTimeStyle::Postgres => match order {
48            PGDateOrder::MDY | PGDateOrder::YMD => "%a %b %d %H:%M:%S%.6f %Y",
49            PGDateOrder::DMY => "%a %d %b %H:%M:%S%.6f %Y",
50        },
51        PGDateTimeStyle::SQL => match order {
52            PGDateOrder::MDY | PGDateOrder::YMD => "%m/%d/%Y %H:%M:%S%.6f",
53            PGDateOrder::DMY => "%d/%m/%Y %H:%M:%S%.6f",
54        },
55    }
56}
57impl ToSqlText for StylingDate {
58    fn to_sql_text(
59        &self,
60        ty: &Type,
61        out: &mut bytes::BytesMut,
62        format_options: &FormatOptions,
63    ) -> std::result::Result<IsNull, Box<dyn std::error::Error + Sync + Send>>
64    where
65        Self: Sized,
66    {
67        match *ty {
68            Type::DATE => {
69                let fmt = self
70                    .0
71                    .format(date_format_string(self.1, self.2))
72                    .to_string();
73                out.put_slice(fmt.as_bytes());
74            }
75            _ => {
76                self.0.to_sql_text(ty, out, format_options)?;
77            }
78        }
79        Ok(IsNull::No)
80    }
81}
82
83impl ToSqlText for StylingDateTime {
84    fn to_sql_text(
85        &self,
86        ty: &Type,
87        out: &mut bytes::BytesMut,
88        format_options: &FormatOptions,
89    ) -> Result<IsNull, Box<dyn std::error::Error + Sync + Send>>
90    where
91        Self: Sized,
92    {
93        match *ty {
94            Type::TIMESTAMP => {
95                let fmt = self
96                    .0
97                    .format(datetime_format_string(self.1, self.2))
98                    .to_string();
99                out.put_slice(fmt.as_bytes());
100            }
101            Type::DATE => {
102                let fmt = self
103                    .0
104                    .format(date_format_string(self.1, self.2))
105                    .to_string();
106                out.put_slice(fmt.as_bytes());
107            }
108            _ => {
109                self.0.to_sql_text(ty, out, format_options)?;
110            }
111        }
112        Ok(IsNull::No)
113    }
114}
115
116macro_rules! delegate_to_sql {
117    ($delegator:ident, $delegatee:ident) => {
118        impl ToSql for $delegator {
119            fn to_sql(
120                &self,
121                ty: &Type,
122                out: &mut bytes::BytesMut,
123            ) -> std::result::Result<IsNull, Box<dyn std::error::Error + Sync + Send>> {
124                self.0.to_sql(ty, out)
125            }
126
127            fn accepts(ty: &Type) -> bool {
128                <$delegatee as ToSql>::accepts(ty)
129            }
130
131            fn to_sql_checked(
132                &self,
133                ty: &Type,
134                out: &mut bytes::BytesMut,
135            ) -> Result<IsNull, Box<dyn std::error::Error + Sync + Send>> {
136                self.0.to_sql_checked(ty, out)
137            }
138        }
139    };
140}
141
142delegate_to_sql!(StylingDate, NaiveDate);
143delegate_to_sql!(StylingDateTime, NaiveDateTime);
144
145#[cfg(test)]
146mod tests {
147    use super::*;
148
149    #[test]
150    fn test_styling_date() {
151        let naive_date = NaiveDate::from_ymd_opt(1997, 12, 17).unwrap();
152
153        {
154            let styling_date = StylingDate(naive_date, PGDateTimeStyle::ISO, PGDateOrder::MDY);
155            let expected = "1997-12-17";
156            let mut out = bytes::BytesMut::new();
157            let is_null = styling_date
158                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
159                .unwrap();
160            assert!(matches!(is_null, IsNull::No));
161            assert_eq!(out, expected.as_bytes());
162        }
163
164        {
165            let styling_date = StylingDate(naive_date, PGDateTimeStyle::ISO, PGDateOrder::YMD);
166            let expected = "1997-12-17";
167            let mut out = bytes::BytesMut::new();
168            let is_null = styling_date
169                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
170                .unwrap();
171            assert!(matches!(is_null, IsNull::No));
172            assert_eq!(out, expected.as_bytes());
173        }
174
175        {
176            let styling_date = StylingDate(naive_date, PGDateTimeStyle::ISO, PGDateOrder::DMY);
177            let expected = "1997-12-17";
178            let mut out = bytes::BytesMut::new();
179            let is_null = styling_date
180                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
181                .unwrap();
182            assert!(matches!(is_null, IsNull::No));
183            assert_eq!(out, expected.as_bytes());
184        }
185
186        {
187            let styling_date = StylingDate(naive_date, PGDateTimeStyle::German, PGDateOrder::MDY);
188            let expected = "17.12.1997";
189            let mut out = bytes::BytesMut::new();
190            let is_null = styling_date
191                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
192                .unwrap();
193            assert!(matches!(is_null, IsNull::No));
194            assert_eq!(out, expected.as_bytes());
195        }
196
197        {
198            let styling_date = StylingDate(naive_date, PGDateTimeStyle::German, PGDateOrder::YMD);
199            let expected = "17.12.1997";
200            let mut out = bytes::BytesMut::new();
201            let is_null = styling_date
202                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
203                .unwrap();
204            assert!(matches!(is_null, IsNull::No));
205            assert_eq!(out, expected.as_bytes());
206        }
207
208        {
209            let styling_date = StylingDate(naive_date, PGDateTimeStyle::German, PGDateOrder::DMY);
210            let expected = "17.12.1997";
211            let mut out = bytes::BytesMut::new();
212            let is_null = styling_date
213                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
214                .unwrap();
215            assert!(matches!(is_null, IsNull::No));
216            assert_eq!(out, expected.as_bytes());
217        }
218
219        {
220            let styling_date = StylingDate(naive_date, PGDateTimeStyle::Postgres, PGDateOrder::MDY);
221            let expected = "12-17-1997";
222            let mut out = bytes::BytesMut::new();
223            let is_null = styling_date
224                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
225                .unwrap();
226            assert!(matches!(is_null, IsNull::No));
227            assert_eq!(out, expected.as_bytes());
228        }
229
230        {
231            let styling_date = StylingDate(naive_date, PGDateTimeStyle::Postgres, PGDateOrder::YMD);
232            let expected = "12-17-1997";
233            let mut out = bytes::BytesMut::new();
234            let is_null = styling_date
235                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
236                .unwrap();
237            assert!(matches!(is_null, IsNull::No));
238            assert_eq!(out, expected.as_bytes());
239        }
240
241        {
242            let styling_date = StylingDate(naive_date, PGDateTimeStyle::Postgres, PGDateOrder::DMY);
243            let expected = "17-12-1997";
244            let mut out = bytes::BytesMut::new();
245            let is_null = styling_date
246                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
247                .unwrap();
248            assert!(matches!(is_null, IsNull::No));
249            assert_eq!(out, expected.as_bytes());
250        }
251
252        {
253            let styling_date = StylingDate(naive_date, PGDateTimeStyle::SQL, PGDateOrder::MDY);
254            let expected = "12/17/1997";
255            let mut out = bytes::BytesMut::new();
256            let is_null = styling_date
257                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
258                .unwrap();
259            assert!(matches!(is_null, IsNull::No));
260            assert_eq!(out, expected.as_bytes());
261        }
262
263        {
264            let styling_date = StylingDate(naive_date, PGDateTimeStyle::SQL, PGDateOrder::YMD);
265            let expected = "12/17/1997";
266            let mut out = bytes::BytesMut::new();
267            let is_null = styling_date
268                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
269                .unwrap();
270            assert!(matches!(is_null, IsNull::No));
271            assert_eq!(out, expected.as_bytes());
272        }
273
274        {
275            let styling_date = StylingDate(naive_date, PGDateTimeStyle::SQL, PGDateOrder::DMY);
276            let expected = "17/12/1997";
277            let mut out = bytes::BytesMut::new();
278            let is_null = styling_date
279                .to_sql_text(&Type::DATE, &mut out, &FormatOptions::default())
280                .unwrap();
281            assert!(matches!(is_null, IsNull::No));
282            assert_eq!(out, expected.as_bytes());
283        }
284    }
285
286    #[test]
287    fn test_styling_datetime() {
288        let input =
289            NaiveDateTime::parse_from_str("2021-09-01 12:34:56.789012", "%Y-%m-%d %H:%M:%S%.f")
290                .unwrap();
291
292        {
293            let styling_datetime = StylingDateTime(input, PGDateTimeStyle::ISO, PGDateOrder::MDY);
294            let expected = "2021-09-01 12:34:56.789012";
295            let mut out = bytes::BytesMut::new();
296            let is_null = styling_datetime
297                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
298                .unwrap();
299            assert!(matches!(is_null, IsNull::No));
300            assert_eq!(out, expected.as_bytes());
301        }
302
303        {
304            let styling_datetime = StylingDateTime(input, PGDateTimeStyle::ISO, PGDateOrder::YMD);
305            let expected = "2021-09-01 12:34:56.789012";
306            let mut out = bytes::BytesMut::new();
307            let is_null = styling_datetime
308                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
309                .unwrap();
310            assert!(matches!(is_null, IsNull::No));
311            assert_eq!(out, expected.as_bytes());
312        }
313
314        {
315            let styling_datetime = StylingDateTime(input, PGDateTimeStyle::ISO, PGDateOrder::DMY);
316            let expected = "2021-09-01 12:34:56.789012";
317            let mut out = bytes::BytesMut::new();
318            let is_null = styling_datetime
319                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
320                .unwrap();
321            assert!(matches!(is_null, IsNull::No));
322            assert_eq!(out, expected.as_bytes());
323        }
324
325        {
326            let styling_datetime =
327                StylingDateTime(input, PGDateTimeStyle::German, PGDateOrder::MDY);
328            let expected = "01.09.2021 12:34:56.789012";
329            let mut out = bytes::BytesMut::new();
330            let is_null = styling_datetime
331                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
332                .unwrap();
333            assert!(matches!(is_null, IsNull::No));
334            assert_eq!(out, expected.as_bytes());
335        }
336
337        {
338            let styling_datetime =
339                StylingDateTime(input, PGDateTimeStyle::German, PGDateOrder::YMD);
340            let expected = "01.09.2021 12:34:56.789012";
341            let mut out = bytes::BytesMut::new();
342            let is_null = styling_datetime
343                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
344                .unwrap();
345            assert!(matches!(is_null, IsNull::No));
346            assert_eq!(out, expected.as_bytes());
347        }
348
349        {
350            let styling_datetime =
351                StylingDateTime(input, PGDateTimeStyle::German, PGDateOrder::DMY);
352            let expected = "01.09.2021 12:34:56.789012";
353            let mut out = bytes::BytesMut::new();
354            let is_null = styling_datetime
355                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
356                .unwrap();
357            assert!(matches!(is_null, IsNull::No));
358            assert_eq!(out, expected.as_bytes());
359        }
360
361        {
362            let styling_datetime =
363                StylingDateTime(input, PGDateTimeStyle::Postgres, PGDateOrder::MDY);
364            let expected = "Wed Sep 01 12:34:56.789012 2021";
365            let mut out = bytes::BytesMut::new();
366            let is_null = styling_datetime
367                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
368                .unwrap();
369            assert!(matches!(is_null, IsNull::No));
370            assert_eq!(out, expected.as_bytes());
371        }
372
373        {
374            let styling_datetime =
375                StylingDateTime(input, PGDateTimeStyle::Postgres, PGDateOrder::YMD);
376            let expected = "Wed Sep 01 12:34:56.789012 2021";
377            let mut out = bytes::BytesMut::new();
378            let is_null = styling_datetime
379                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
380                .unwrap();
381            assert!(matches!(is_null, IsNull::No));
382            assert_eq!(out, expected.as_bytes());
383        }
384
385        {
386            let styling_datetime =
387                StylingDateTime(input, PGDateTimeStyle::Postgres, PGDateOrder::DMY);
388            let expected = "Wed 01 Sep 12:34:56.789012 2021";
389            let mut out = bytes::BytesMut::new();
390            let is_null = styling_datetime
391                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
392                .unwrap();
393            assert!(matches!(is_null, IsNull::No));
394            assert_eq!(out, expected.as_bytes());
395        }
396
397        {
398            let styling_datetime = StylingDateTime(input, PGDateTimeStyle::SQL, PGDateOrder::MDY);
399            let expected = "09/01/2021 12:34:56.789012";
400            let mut out = bytes::BytesMut::new();
401            let is_null = styling_datetime
402                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
403                .unwrap();
404            assert!(matches!(is_null, IsNull::No));
405            assert_eq!(out, expected.as_bytes());
406        }
407
408        {
409            let styling_datetime = StylingDateTime(input, PGDateTimeStyle::SQL, PGDateOrder::YMD);
410            let expected = "09/01/2021 12:34:56.789012";
411            let mut out = bytes::BytesMut::new();
412            let is_null = styling_datetime
413                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
414                .unwrap();
415            assert!(matches!(is_null, IsNull::No));
416            assert_eq!(out, expected.as_bytes());
417        }
418
419        {
420            let styling_datetime = StylingDateTime(input, PGDateTimeStyle::SQL, PGDateOrder::DMY);
421            let expected = "01/09/2021 12:34:56.789012";
422            let mut out = bytes::BytesMut::new();
423            let is_null = styling_datetime
424                .to_sql_text(&Type::TIMESTAMP, &mut out, &FormatOptions::default())
425                .unwrap();
426            assert!(matches!(is_null, IsNull::No));
427            assert_eq!(out, expected.as_bytes());
428        }
429    }
430}
servers/postgres/types/datetime.rs

servers/postgres/types/
datetime.rs
