qail_pg/types/
temporal.rs

1//! Timestamp type conversions for PostgreSQL.
2//!
3//! PostgreSQL timestamps are stored as microseconds since 2000-01-01 00:00:00 UTC.
4
5use super::{FromPg, ToPg, TypeError};
6use crate::protocol::types::oid;
7
8/// PostgreSQL epoch: 2000-01-01 00:00:00 UTC
9/// Difference from Unix epoch (1970-01-01) in microseconds
10const PG_EPOCH_OFFSET_USEC: i64 = 946_684_800_000_000;
11
12/// Timestamp without timezone (microseconds since 2000-01-01)
13#[derive(Debug, Clone, Copy, PartialEq, Eq)]
14pub struct Timestamp {
15    /// Microseconds since PostgreSQL epoch (2000-01-01 00:00:00)
16    pub usec: i64,
17}
18
19impl Timestamp {
20    /// Create from microseconds since PostgreSQL epoch
21    pub fn from_pg_usec(usec: i64) -> Self {
22        Self { usec }
23    }
24
25    /// Create from Unix timestamp (seconds since 1970-01-01)
26    pub fn from_unix_secs(secs: i64) -> Self {
27        Self {
28            usec: secs * 1_000_000 - PG_EPOCH_OFFSET_USEC,
29        }
30    }
31
32    /// Convert to Unix timestamp (seconds since 1970-01-01)
33    pub fn to_unix_secs(&self) -> i64 {
34        (self.usec + PG_EPOCH_OFFSET_USEC) / 1_000_000
35    }
36
37    /// Convert to Unix timestamp with microseconds
38    pub fn to_unix_usec(&self) -> i64 {
39        self.usec + PG_EPOCH_OFFSET_USEC
40    }
41}
42
43impl FromPg for Timestamp {
44    fn from_pg(bytes: &[u8], oid_val: u32, format: i16) -> Result<Self, TypeError> {
45        if oid_val != oid::TIMESTAMP && oid_val != oid::TIMESTAMPTZ {
46            return Err(TypeError::UnexpectedOid {
47                expected: "timestamp",
48                got: oid_val,
49            });
50        }
51
52        if format == 1 {
53            // Binary: 8 bytes, microseconds since 2000-01-01
54            if bytes.len() != 8 {
55                return Err(TypeError::InvalidData(
56                    "Expected 8 bytes for timestamp".to_string(),
57                ));
58            }
59            let usec = i64::from_be_bytes([
60                bytes[0], bytes[1], bytes[2], bytes[3], bytes[4], bytes[5], bytes[6], bytes[7],
61            ]);
62            Ok(Timestamp::from_pg_usec(usec))
63        } else {
64            // Text format: parse ISO 8601
65            let s =
66                std::str::from_utf8(bytes).map_err(|e| TypeError::InvalidData(e.to_string()))?;
67            parse_timestamp_text(s)
68        }
69    }
70}
71
72impl ToPg for Timestamp {
73    fn to_pg(&self) -> (Vec<u8>, u32, i16) {
74        (self.usec.to_be_bytes().to_vec(), oid::TIMESTAMP, 1)
75    }
76}
77
78#[cfg(feature = "chrono")]
79impl FromPg for chrono::DateTime<chrono::Utc> {
80    fn from_pg(bytes: &[u8], oid_val: u32, format: i16) -> Result<Self, TypeError> {
81        if oid_val != oid::TIMESTAMP && oid_val != oid::TIMESTAMPTZ {
82            return Err(TypeError::UnexpectedOid {
83                expected: "timestamp",
84                got: oid_val,
85            });
86        }
87
88        if format == 1 {
89            if bytes.len() != 8 {
90                return Err(TypeError::InvalidData(
91                    "Expected 8 bytes for timestamp".to_string(),
92                ));
93            }
94            let pg_usec = i64::from_be_bytes([
95                bytes[0], bytes[1], bytes[2], bytes[3], bytes[4], bytes[5], bytes[6], bytes[7],
96            ]);
97            let unix_usec = pg_usec.saturating_add(PG_EPOCH_OFFSET_USEC);
98            chrono::DateTime::<chrono::Utc>::from_timestamp_micros(unix_usec).ok_or_else(|| {
99                TypeError::InvalidData(format!("Timestamp out of range: {}", unix_usec))
100            })
101        } else {
102            let s =
103                std::str::from_utf8(bytes).map_err(|e| TypeError::InvalidData(e.to_string()))?;
104
105            if oid_val == oid::TIMESTAMPTZ {
106                chrono::DateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f%#z")
107                    .or_else(|_| chrono::DateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S%.f%#z"))
108                    .or_else(|_| chrono::DateTime::parse_from_rfc3339(s))
109                    .map(|dt| dt.with_timezone(&chrono::Utc))
110                    .map_err(|e| TypeError::InvalidData(format!("Invalid timestamptz: {}", e)))
111            } else {
112                chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f")
113                    .or_else(|_| chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S%.f"))
114                    .map(|naive| {
115                        chrono::DateTime::<chrono::Utc>::from_naive_utc_and_offset(
116                            naive,
117                            chrono::Utc,
118                        )
119                    })
120                    .map_err(|e| TypeError::InvalidData(format!("Invalid timestamp: {}", e)))
121            }
122        }
123    }
124}
125
126#[cfg(feature = "chrono")]
127impl ToPg for chrono::DateTime<chrono::Utc> {
128    fn to_pg(&self) -> (Vec<u8>, u32, i16) {
129        let unix_usec = self.timestamp_micros();
130        let pg_usec = unix_usec.saturating_sub(PG_EPOCH_OFFSET_USEC);
131        (pg_usec.to_be_bytes().to_vec(), oid::TIMESTAMPTZ, 1)
132    }
133}
134
135/// Parse PostgreSQL text timestamp format
136fn parse_timestamp_text(s: &str) -> Result<Timestamp, TypeError> {
137    // Format: "2024-12-25 17:30:00" or "2024-12-25 17:30:00.123456"
138    // This is a simplified parser - production would use chrono or time crate
139
140    let parts: Vec<&str> = s.split(&[' ', 'T'][..]).collect();
141    if parts.len() < 2 {
142        return Err(TypeError::InvalidData(format!("Invalid timestamp: {}", s)));
143    }
144
145    let date_parts: Vec<i32> = parts[0].split('-').filter_map(|p| p.parse().ok()).collect();
146
147    if date_parts.len() != 3 {
148        return Err(TypeError::InvalidData(format!(
149            "Invalid date: {}",
150            parts[0]
151        )));
152    }
153
154    let time_str =
155        parts[1].trim_end_matches(|c: char| c == '+' || c == '-' || c.is_ascii_digit() || c == ':');
156    let time_parts: Vec<&str> = time_str.split(':').collect();
157
158    let hour: i32 = time_parts.first().and_then(|s| s.parse().ok()).unwrap_or(0);
159    let minute: i32 = time_parts.get(1).and_then(|s| s.parse().ok()).unwrap_or(0);
160    let second_str = time_parts.get(2).unwrap_or(&"0");
161    let sec_parts: Vec<&str> = second_str.split('.').collect();
162    let second: i32 = sec_parts.first().and_then(|s| s.parse().ok()).unwrap_or(0);
163    let usec: i64 = sec_parts
164        .get(1)
165        .map(|s| {
166            let padded = format!("{:0<6}", s);
167            padded[..6].parse::<i64>().unwrap_or(0)
168        })
169        .unwrap_or(0);
170
171    // Calculate days since 2000-01-01
172    let year = date_parts[0];
173    let month = date_parts[1];
174    let day = date_parts[2];
175
176    // Simplified calculation (not accounting for all leap years correctly)
177    let days_since_epoch = days_from_ymd(year, month, day);
178
179    let total_usec = days_since_epoch as i64 * 86_400_000_000
180        + hour as i64 * 3_600_000_000
181        + minute as i64 * 60_000_000
182        + second as i64 * 1_000_000
183        + usec;
184
185    Ok(Timestamp::from_pg_usec(total_usec))
186}
187
188/// Calculate days since 2000-01-01
189fn days_from_ymd(year: i32, month: i32, day: i32) -> i32 {
190    // Days from 2000-01-01 to given date
191    let mut days = 0;
192
193    // Years
194    for y in 2000..year {
195        days += if is_leap_year(y) { 366 } else { 365 };
196    }
197    for y in year..2000 {
198        days -= if is_leap_year(y) { 366 } else { 365 };
199    }
200
201    // Months
202    let days_in_month = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
203    for m in 1..month {
204        days += days_in_month[(m - 1) as usize];
205        if m == 2 && is_leap_year(year) {
206            days += 1;
207        }
208    }
209
210    // Days
211    days += day - 1;
212
213    days
214}
215
216fn is_leap_year(year: i32) -> bool {
217    (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0)
218}
219
220/// Date type (days since 2000-01-01)
221#[derive(Debug, Clone, Copy, PartialEq, Eq)]
222pub struct Date {
223    /// Days since PostgreSQL epoch (2000-01-01). Negative values represent dates before the epoch.
224    pub days: i32,
225}
226
227impl FromPg for Date {
228    fn from_pg(bytes: &[u8], oid_val: u32, format: i16) -> Result<Self, TypeError> {
229        if oid_val != oid::DATE {
230            return Err(TypeError::UnexpectedOid {
231                expected: "date",
232                got: oid_val,
233            });
234        }
235
236        if format == 1 {
237            // Binary: 4 bytes, days since 2000-01-01
238            if bytes.len() != 4 {
239                return Err(TypeError::InvalidData(
240                    "Expected 4 bytes for date".to_string(),
241                ));
242            }
243            let days = i32::from_be_bytes([bytes[0], bytes[1], bytes[2], bytes[3]]);
244            Ok(Date { days })
245        } else {
246            // Text format: YYYY-MM-DD
247            let s =
248                std::str::from_utf8(bytes).map_err(|e| TypeError::InvalidData(e.to_string()))?;
249            let parts: Vec<i32> = s.split('-').filter_map(|p| p.parse().ok()).collect();
250            if parts.len() != 3 {
251                return Err(TypeError::InvalidData(format!("Invalid date: {}", s)));
252            }
253            Ok(Date {
254                days: days_from_ymd(parts[0], parts[1], parts[2]),
255            })
256        }
257    }
258}
259
260impl ToPg for Date {
261    fn to_pg(&self) -> (Vec<u8>, u32, i16) {
262        (self.days.to_be_bytes().to_vec(), oid::DATE, 1)
263    }
264}
265
266/// Time type (microseconds since midnight)
267#[derive(Debug, Clone, Copy, PartialEq, Eq)]
268pub struct Time {
269    /// Microseconds since midnight
270    pub usec: i64,
271}
272
273impl Time {
274    /// Create from hours, minutes, seconds, microseconds.
275    ///
276    /// # Arguments
277    ///
278    /// * `hour` — Hour component (0–23).
279    /// * `minute` — Minute component (0–59).
280    /// * `second` — Second component (0–59).
281    /// * `usec` — Microseconds within the current second.
282    pub fn new(hour: u8, minute: u8, second: u8, usec: u32) -> Self {
283        Self {
284            usec: hour as i64 * 3_600_000_000
285                + minute as i64 * 60_000_000
286                + second as i64 * 1_000_000
287                + usec as i64,
288        }
289    }
290
291    /// Get hours component (0-23)
292    pub fn hour(&self) -> u8 {
293        ((self.usec / 3_600_000_000) % 24) as u8
294    }
295
296    /// Get minutes component (0-59)
297    pub fn minute(&self) -> u8 {
298        ((self.usec / 60_000_000) % 60) as u8
299    }
300
301    /// Get seconds component (0-59)
302    pub fn second(&self) -> u8 {
303        ((self.usec / 1_000_000) % 60) as u8
304    }
305
306    /// Get microseconds component (0-999999)
307    pub fn microsecond(&self) -> u32 {
308        (self.usec % 1_000_000) as u32
309    }
310}
311
312impl FromPg for Time {
313    fn from_pg(bytes: &[u8], oid_val: u32, format: i16) -> Result<Self, TypeError> {
314        if oid_val != oid::TIME {
315            return Err(TypeError::UnexpectedOid {
316                expected: "time",
317                got: oid_val,
318            });
319        }
320
321        if format == 1 {
322            // Binary: 8 bytes, microseconds since midnight
323            if bytes.len() != 8 {
324                return Err(TypeError::InvalidData(
325                    "Expected 8 bytes for time".to_string(),
326                ));
327            }
328            let usec = i64::from_be_bytes([
329                bytes[0], bytes[1], bytes[2], bytes[3], bytes[4], bytes[5], bytes[6], bytes[7],
330            ]);
331            Ok(Time { usec })
332        } else {
333            // Text format: HH:MM:SS or HH:MM:SS.ffffff
334            let s =
335                std::str::from_utf8(bytes).map_err(|e| TypeError::InvalidData(e.to_string()))?;
336            parse_time_text(s)
337        }
338    }
339}
340
341impl ToPg for Time {
342    fn to_pg(&self) -> (Vec<u8>, u32, i16) {
343        (self.usec.to_be_bytes().to_vec(), oid::TIME, 1)
344    }
345}
346
347/// Parse PostgreSQL text time format
348fn parse_time_text(s: &str) -> Result<Time, TypeError> {
349    let parts: Vec<&str> = s.split(':').collect();
350    if parts.len() < 2 {
351        return Err(TypeError::InvalidData(format!("Invalid time: {}", s)));
352    }
353
354    let hour: i64 = parts[0]
355        .parse()
356        .map_err(|_| TypeError::InvalidData("Invalid hour".to_string()))?;
357    let minute: i64 = parts[1]
358        .parse()
359        .map_err(|_| TypeError::InvalidData("Invalid minute".to_string()))?;
360
361    let (second, usec) = if parts.len() > 2 {
362        let sec_parts: Vec<&str> = parts[2].split('.').collect();
363        let sec: i64 = sec_parts[0].parse().unwrap_or(0);
364        let us: i64 = sec_parts
365            .get(1)
366            .map(|s| {
367                let padded = format!("{:0<6}", s);
368                padded[..6].parse::<i64>().unwrap_or(0)
369            })
370            .unwrap_or(0);
371        (sec, us)
372    } else {
373        (0, 0)
374    };
375
376    Ok(Time {
377        usec: hour * 3_600_000_000 + minute * 60_000_000 + second * 1_000_000 + usec,
378    })
379}
380
381#[cfg(test)]
382mod tests {
383    use super::*;
384    #[cfg(feature = "chrono")]
385    use chrono::{Datelike, Timelike};
386
387    #[test]
388    fn test_timestamp_unix_conversion() {
389        // 2024-01-01 00:00:00 UTC
390        let ts = Timestamp::from_unix_secs(1704067200);
391        let back = ts.to_unix_secs();
392        assert_eq!(back, 1704067200);
393    }
394
395    #[test]
396    fn test_timestamp_from_pg_binary() {
397        // Some arbitrary timestamp in binary
398        let usec: i64 = 789_012_345_678_900; // ~25 years after 2000
399        let bytes = usec.to_be_bytes();
400        let ts = Timestamp::from_pg(&bytes, oid::TIMESTAMP, 1).unwrap();
401        assert_eq!(ts.usec, usec);
402    }
403
404    #[test]
405    fn test_date_from_pg_binary() {
406        // 2024-01-01 = 8766 days since 2000-01-01
407        let days: i32 = 8766;
408        let bytes = days.to_be_bytes();
409        let date = Date::from_pg(&bytes, oid::DATE, 1).unwrap();
410        assert_eq!(date.days, days);
411    }
412
413    #[test]
414    fn test_time_from_pg_binary() {
415        // 12:30:45.123456 = 45045123456 microseconds
416        let usec: i64 = 12 * 3_600_000_000 + 30 * 60_000_000 + 45 * 1_000_000 + 123456;
417        let bytes = usec.to_be_bytes();
418        let time = Time::from_pg(&bytes, oid::TIME, 1).unwrap();
419        assert_eq!(time.hour(), 12);
420        assert_eq!(time.minute(), 30);
421        assert_eq!(time.second(), 45);
422        assert_eq!(time.microsecond(), 123456);
423    }
424
425    #[test]
426    fn test_time_from_pg_text() {
427        let time = parse_time_text("14:30:00").unwrap();
428        assert_eq!(time.hour(), 14);
429        assert_eq!(time.minute(), 30);
430        assert_eq!(time.second(), 0);
431    }
432
433    #[cfg(feature = "chrono")]
434    #[test]
435    fn test_chrono_datetime_from_pg_binary() {
436        // PostgreSQL binary timestamp at Unix epoch.
437        let pg_usec = -PG_EPOCH_OFFSET_USEC;
438        let bytes = pg_usec.to_be_bytes();
439        let dt = chrono::DateTime::<chrono::Utc>::from_pg(&bytes, oid::TIMESTAMPTZ, 1).unwrap();
440        assert_eq!(dt.timestamp(), 0);
441    }
442
443    #[cfg(feature = "chrono")]
444    #[test]
445    fn test_chrono_datetime_from_pg_text_timestamptz() {
446        let dt = chrono::DateTime::<chrono::Utc>::from_pg(
447            b"2024-12-25 17:30:00+00",
448            oid::TIMESTAMPTZ,
449            0,
450        )
451        .unwrap();
452        assert_eq!(dt.year(), 2024);
453        assert_eq!(dt.month(), 12);
454        assert_eq!(dt.day(), 25);
455        assert_eq!(dt.hour(), 17);
456        assert_eq!(dt.minute(), 30);
457    }
458
459    #[cfg(feature = "chrono")]
460    #[test]
461    fn test_chrono_datetime_to_pg_binary() {
462        let dt =
463            chrono::DateTime::<chrono::Utc>::from_timestamp(1_704_067_200, 123_456_000).unwrap();
464        let (bytes, oid_val, format) = dt.to_pg();
465        assert_eq!(oid_val, oid::TIMESTAMPTZ);
466        assert_eq!(format, 1);
467        assert_eq!(bytes.len(), 8);
468    }
469}
qail_pg/types/temporal.rs

qail_pg/types/
temporal.rs
