vibesql_executor/evaluator/
date_format.rs

1//! Date and time format string parsing utilities
2//!
3//! Converts between SQL format strings (YYYY-MM-DD, Mon DD YYYY, etc.)
4//! and chrono format strings (%Y-%m-%d, %b %d %Y, etc.)
5
6use chrono::{NaiveDate, NaiveDateTime, NaiveTime};
7
8use crate::errors::ExecutorError;
9
10/// Convert SQL format string to chrono format string
11///
12/// Supports common SQL format codes:
13/// - Date: YYYY (4-digit year), YY (2-digit year), MM (month), DD (day), Mon (abbreviated month
14///   name)
15/// - Time: HH24 (24-hour), HH12 (12-hour), MI (minute), SS (second), AM/PM
16///
17/// # Examples
18/// ```
19/// use vibesql_executor::evaluator::date_format::sql_to_chrono_format;
20/// assert_eq!(sql_to_chrono_format("YYYY-MM-DD"), Ok("%Y-%m-%d".to_string()));
21/// assert_eq!(sql_to_chrono_format("Mon DD, YYYY"), Ok("%b %d, %Y".to_string()));
22/// ```
23pub fn sql_to_chrono_format(sql_format: &str) -> Result<String, ExecutorError> {
24    let mut result = sql_format.to_string();
25
26    // Date components (order matters - replace longer patterns first)
27    result = result.replace("YYYY", "%Y"); // 4-digit year
28    result = result.replace("YY", "%y"); // 2-digit year
29    result = result.replace("Month", "%B"); // Full month name (January, February, etc.) - must come before "Mon"
30    result = result.replace("Mon", "%b"); // Abbreviated month name (Jan, Feb, etc.)
31    result = result.replace("MM", "%m"); // Month as number (01-12)
32    result = result.replace("DD", "%d"); // Day of month (01-31)
33    result = result.replace("Day", "%A"); // Full day name (Monday, Tuesday, etc.)
34    result = result.replace("Dy", "%a"); // Abbreviated day name (Mon, Tue, etc.)
35
36    // Time components
37    result = result.replace("HH24", "%H"); // 24-hour (00-23)
38    result = result.replace("HH12", "%I"); // 12-hour (01-12)
39    result = result.replace("HH", "%H"); // Default to 24-hour
40    result = result.replace("MI", "%M"); // Minute (00-59)
41    result = result.replace("SS", "%S"); // Second (00-59)
42    result = result.replace("AM", "%p"); // AM/PM
43    result = result.replace("PM", "%p"); // AM/PM
44    result = result.replace("am", "%P"); // am/pm (lowercase)
45    result = result.replace("pm", "%P"); // am/pm (lowercase)
46
47    Ok(result)
48}
49
50/// Format a date using SQL format string
51///
52/// # Examples
53/// ```
54/// use chrono::NaiveDate;
55/// use vibesql_executor::evaluator::date_format::format_date;
56/// let date = NaiveDate::from_ymd_opt(2024, 3, 15).unwrap();
57/// assert_eq!(format_date(&date, "YYYY-MM-DD"), Ok("2024-03-15".to_string()));
58/// assert_eq!(format_date(&date, "Mon DD, YYYY"), Ok("Mar 15, 2024".to_string()));
59/// ```
60pub fn format_date(date: &NaiveDate, sql_format: &str) -> Result<String, ExecutorError> {
61    let chrono_format = sql_to_chrono_format(sql_format)?;
62    Ok(date.format(&chrono_format).to_string())
63}
64
65/// Format a timestamp using SQL format string
66///
67/// # Examples
68/// ```
69/// use chrono::{DateTime, NaiveDateTime};
70/// use vibesql_executor::evaluator::date_format::format_timestamp;
71/// let timestamp = DateTime::from_timestamp(1700000000, 0).unwrap().naive_utc();
72/// assert_eq!(
73///     format_timestamp(&timestamp, "YYYY-MM-DD HH24:MI:SS"),
74///     Ok("2023-11-14 22:13:20".to_string())
75/// );
76/// ```
77pub fn format_timestamp(
78    timestamp: &NaiveDateTime,
79    sql_format: &str,
80) -> Result<String, ExecutorError> {
81    let chrono_format = sql_to_chrono_format(sql_format)?;
82    Ok(timestamp.format(&chrono_format).to_string())
83}
84
85/// Format a time using SQL format string
86pub fn format_time(time: &NaiveTime, sql_format: &str) -> Result<String, ExecutorError> {
87    let chrono_format = sql_to_chrono_format(sql_format)?;
88    Ok(time.format(&chrono_format).to_string())
89}
90
91/// Parse a date string using SQL format string
92///
93/// # Examples
94/// ```
95/// use chrono::NaiveDate;
96/// use vibesql_executor::evaluator::date_format::parse_date;
97/// assert_eq!(
98///     parse_date("2024-03-15", "YYYY-MM-DD"),
99///     Ok(NaiveDate::from_ymd_opt(2024, 3, 15).unwrap())
100/// );
101/// assert_eq!(
102///     parse_date("15/03/2024", "DD/MM/YYYY"),
103///     Ok(NaiveDate::from_ymd_opt(2024, 3, 15).unwrap())
104/// );
105/// ```
106pub fn parse_date(input: &str, sql_format: &str) -> Result<NaiveDate, ExecutorError> {
107    let chrono_format = sql_to_chrono_format(sql_format)?;
108    NaiveDate::parse_from_str(input, &chrono_format).map_err(|e| {
109        ExecutorError::UnsupportedFeature(format!(
110            "Failed to parse date '{}' with format '{}': {}",
111            input, sql_format, e
112        ))
113    })
114}
115
116/// Parse a timestamp string using SQL format string
117///
118/// # Examples
119/// ```
120/// use chrono::{DateTime, NaiveDateTime};
121/// use vibesql_executor::evaluator::date_format::parse_timestamp;
122/// let expected = DateTime::from_timestamp(1710513045, 0).unwrap().naive_utc();
123/// assert_eq!(parse_timestamp("2024-03-15 14:30:45", "YYYY-MM-DD HH24:MI:SS"), Ok(expected));
124/// ```
125pub fn parse_timestamp(input: &str, sql_format: &str) -> Result<NaiveDateTime, ExecutorError> {
126    let chrono_format = sql_to_chrono_format(sql_format)?;
127    NaiveDateTime::parse_from_str(input, &chrono_format).map_err(|e| {
128        ExecutorError::UnsupportedFeature(format!(
129            "Failed to parse timestamp '{}' with format '{}': {}",
130            input, sql_format, e
131        ))
132    })
133}
134
135/// Parse a time string using SQL format string
136pub fn parse_time(input: &str, sql_format: &str) -> Result<NaiveTime, ExecutorError> {
137    let chrono_format = sql_to_chrono_format(sql_format)?;
138    NaiveTime::parse_from_str(input, &chrono_format).map_err(|e| {
139        ExecutorError::UnsupportedFeature(format!(
140            "Failed to parse time '{}' with format '{}': {}",
141            input, sql_format, e
142        ))
143    })
144}
145
146/// Format a number using SQL number format string
147///
148/// Supports format codes:
149/// - 9: digit position (no leading zeros)
150/// - 0: digit position (with leading zeros)
151/// - .: decimal point
152/// - ,: thousand separator
153/// - $: dollar sign prefix
154/// - %: percentage suffix (multiplies by 100)
155///
156/// # Examples
157/// ```
158/// use vibesql_executor::evaluator::date_format::format_number;
159/// assert_eq!(format_number(1234.5, "9999.99"), Ok("1234.50".to_string()));
160/// assert_eq!(format_number(1234.5, "$9,999.99"), Ok("$1,234.50".to_string()));
161/// assert_eq!(format_number(0.75, "99.99%"), Ok("75.00%".to_string()));
162/// ```
163pub fn format_number(number: f64, sql_format: &str) -> Result<String, ExecutorError> {
164    // Check for special prefixes/suffixes
165    let has_dollar = sql_format.starts_with('$');
166    let has_percent = sql_format.ends_with('%');
167
168    // Apply percentage conversion
169    let mut value = if has_percent { number * 100.0 } else { number };
170
171    // Extract format pattern (remove $ and %)
172    let pattern = sql_format.trim_start_matches('$').trim_end_matches('%').trim();
173
174    // Determine if we need thousand separators
175    let has_comma = pattern.contains(',');
176
177    // Find decimal point position
178    let decimal_pos = pattern.rfind('.');
179    let decimal_places = if let Some(pos) = decimal_pos { pattern.len() - pos - 1 } else { 0 };
180
181    // Round to decimal places
182    let multiplier = 10_f64.powi(decimal_places as i32);
183    value = (value * multiplier).round() / multiplier;
184
185    // Split into integer and decimal parts
186    let value_str = value.abs().to_string();
187    let parts: Vec<&str> = value_str.split('.').collect();
188    let int_part = parts[0].parse::<i64>().unwrap_or(0);
189    let dec_part = if decimal_places > 0 {
190        if parts.len() > 1 {
191            // Pad or truncate decimal part to required decimal places
192            let mut dec = parts[1].to_string();
193            while dec.len() < decimal_places {
194                dec.push('0');
195            }
196            dec.truncate(decimal_places);
197            format!(".{}", dec)
198        } else {
199            format!(".{:0width$}", 0, width = decimal_places)
200        }
201    } else {
202        String::new()
203    };
204
205    // Format integer part with thousand separators if needed
206    let formatted_int = if has_comma { format_with_commas(int_part) } else { int_part.to_string() };
207
208    // Combine parts
209    let mut result = String::new();
210    if value < 0.0 {
211        result.push('-');
212    }
213    if has_dollar {
214        result.push('$');
215    }
216    result.push_str(&formatted_int);
217    result.push_str(&dec_part);
218    if has_percent {
219        result.push('%');
220    }
221
222    Ok(result)
223}
224
225/// Helper: Format integer with thousand separators
226fn format_with_commas(num: i64) -> String {
227    let s = num.abs().to_string();
228    let mut result = String::new();
229    for (i, ch) in s.chars().rev().enumerate() {
230        if i > 0 && i % 3 == 0 {
231            result.insert(0, ',');
232        }
233        result.insert(0, ch);
234    }
235    result
236}
237
238#[cfg(test)]
239mod tests {
240    use super::*;
241
242    #[test]
243    fn test_sql_to_chrono_format_basic() {
244        assert_eq!(sql_to_chrono_format("YYYY-MM-DD").unwrap(), "%Y-%m-%d");
245        assert_eq!(sql_to_chrono_format("DD/MM/YYYY").unwrap(), "%d/%m/%Y");
246    }
247
248    #[test]
249    fn test_sql_to_chrono_format_with_names() {
250        assert_eq!(sql_to_chrono_format("Mon DD, YYYY").unwrap(), "%b %d, %Y");
251        assert_eq!(sql_to_chrono_format("Month DD, YYYY").unwrap(), "%B %d, %Y");
252    }
253
254    #[test]
255    fn test_sql_to_chrono_format_timestamp() {
256        assert_eq!(sql_to_chrono_format("YYYY-MM-DD HH24:MI:SS").unwrap(), "%Y-%m-%d %H:%M:%S");
257        assert_eq!(
258            sql_to_chrono_format("DD/MM/YYYY HH12:MI:SS AM").unwrap(),
259            "%d/%m/%Y %I:%M:%S %p"
260        );
261    }
262
263    #[test]
264    fn test_format_date() {
265        let date = NaiveDate::from_ymd_opt(2024, 3, 15).unwrap();
266        assert_eq!(format_date(&date, "YYYY-MM-DD").unwrap(), "2024-03-15");
267        assert_eq!(format_date(&date, "DD/MM/YYYY").unwrap(), "15/03/2024");
268        assert_eq!(format_date(&date, "Mon DD, YYYY").unwrap(), "Mar 15, 2024");
269    }
270
271    #[test]
272    fn test_parse_date() {
273        assert_eq!(
274            parse_date("2024-03-15", "YYYY-MM-DD").unwrap(),
275            NaiveDate::from_ymd_opt(2024, 3, 15).unwrap()
276        );
277        assert_eq!(
278            parse_date("15/03/2024", "DD/MM/YYYY").unwrap(),
279            NaiveDate::from_ymd_opt(2024, 3, 15).unwrap()
280        );
281    }
282
283    #[test]
284    fn test_format_number_basic() {
285        assert_eq!(format_number(1234.5, "9999.99").unwrap(), "1234.50");
286        assert_eq!(format_number(123.456, "999.99").unwrap(), "123.46"); // Rounds
287    }
288
289    #[test]
290    fn test_format_number_with_comma() {
291        assert_eq!(format_number(1234.5, "9,999.99").unwrap(), "1,234.50");
292        assert_eq!(format_number(1234567.89, "9,999,999.99").unwrap(), "1,234,567.89");
293    }
294
295    #[test]
296    fn test_format_number_with_dollar() {
297        assert_eq!(format_number(1234.5, "$9,999.99").unwrap(), "$1,234.50");
298    }
299
300    #[test]
301    fn test_format_number_with_percent() {
302        assert_eq!(format_number(0.75, "99.99%").unwrap(), "75.00%");
303        assert_eq!(format_number(1.5, "999%").unwrap(), "150%");
304    }
305
306    #[test]
307    fn test_format_number_negative() {
308        assert_eq!(format_number(-1234.5, "9999.99").unwrap(), "-1234.50");
309        assert_eq!(format_number(-1234.5, "$9,999.99").unwrap(), "-$1,234.50");
310    }
311}
vibesql_executor/evaluator/date_format.rs

vibesql_executor/evaluator/
date_format.rs
