sql_cli/sql/functions/
date_time.rs

1use anyhow::{anyhow, Result};
2use chrono::{DateTime, Datelike, NaiveDate, NaiveDateTime, TimeZone, Utc};
3
4use super::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
5use crate::config::global::get_date_notation;
6use crate::data::datatable::DataValue;
7
8// Helper function for parsing dates with multiple format support
9pub fn parse_datetime(s: &str) -> Result<DateTime<Utc>> {
10    // Try parsing as ISO 8601 with timezone first (most unambiguous)
11    if let Ok(dt) = s.parse::<DateTime<Utc>>() {
12        return Ok(dt);
13    }
14
15    // ISO formats (most common and unambiguous)
16    // With T separator
17    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S") {
18        return Ok(Utc.from_utc_datetime(&dt));
19    }
20    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S%.3f") {
21        return Ok(Utc.from_utc_datetime(&dt));
22    }
23    // With space separator
24    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.3f") {
25        return Ok(Utc.from_utc_datetime(&dt));
26    }
27    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f") {
28        return Ok(Utc.from_utc_datetime(&dt));
29    }
30    // Without milliseconds
31    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S") {
32        return Ok(Utc.from_utc_datetime(&dt));
33    }
34    if let Ok(dt) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
35        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
36    }
37
38    // Get date notation preference
39    let date_notation = get_date_notation();
40
41    // Date notation preference for ambiguous formats like 04/09/2025
42    if date_notation == "european" {
43        // European formats (DD/MM/YYYY) - try first
44        // Date only formats
45        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
46            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
47        }
48        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
49            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
50        }
51        // With time formats (with milliseconds)
52        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S%.3f") {
53            return Ok(Utc.from_utc_datetime(&dt));
54        }
55        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
56            return Ok(Utc.from_utc_datetime(&dt));
57        }
58        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
59            return Ok(Utc.from_utc_datetime(&dt));
60        }
61
62        // US formats (MM/DD/YYYY) - fallback
63        // Date only formats
64        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
65            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
66        }
67        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
68            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
69        }
70        // With time formats (with milliseconds)
71        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S%.3f") {
72            return Ok(Utc.from_utc_datetime(&dt));
73        }
74        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
75            return Ok(Utc.from_utc_datetime(&dt));
76        }
77        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
78            return Ok(Utc.from_utc_datetime(&dt));
79        }
80    } else {
81        // US formats (MM/DD/YYYY) - default, try first
82        // Date only formats
83        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
84            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
85        }
86        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
87            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
88        }
89        // With time formats (with milliseconds)
90        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S%.3f") {
91            return Ok(Utc.from_utc_datetime(&dt));
92        }
93        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
94            return Ok(Utc.from_utc_datetime(&dt));
95        }
96        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
97            return Ok(Utc.from_utc_datetime(&dt));
98        }
99
100        // European formats (DD/MM/YYYY) - fallback
101        // Date only formats
102        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
103            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
104        }
105        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
106            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
107        }
108        // With time formats (with milliseconds)
109        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S%.3f") {
110            return Ok(Utc.from_utc_datetime(&dt));
111        }
112        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
113            return Ok(Utc.from_utc_datetime(&dt));
114        }
115        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
116            return Ok(Utc.from_utc_datetime(&dt));
117        }
118    }
119
120    // Excel/Windows format: DD-MMM-YYYY (e.g., 15-Jan-2024)
121    if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%b-%Y") {
122        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
123    }
124
125    // Full month names: January 15, 2024 or 15 January 2024
126    if let Ok(dt) = NaiveDate::parse_from_str(s, "%B %d, %Y") {
127        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
128    }
129    if let Ok(dt) = NaiveDate::parse_from_str(s, "%d %B %Y") {
130        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
131    }
132
133    // RFC3339 (e.g., 2024-01-15T10:30:00Z)
134    if let Ok(dt) = DateTime::parse_from_rfc3339(s) {
135        return Ok(dt.with_timezone(&Utc));
136    }
137
138    Err(anyhow!("Could not parse date: {}. Supported formats: YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY, DD-MMM-YYYY, Month DD YYYY", s))
139}
140
141/// NOW function - Returns current datetime
142pub struct NowFunction;
143
144impl SqlFunction for NowFunction {
145    fn signature(&self) -> FunctionSignature {
146        FunctionSignature {
147            name: "NOW",
148            category: FunctionCategory::Date,
149            arg_count: ArgCount::Fixed(0),
150            description: "Returns the current date and time",
151            returns: "DATETIME",
152            examples: vec![
153                "SELECT NOW()",
154                "SELECT * FROM orders WHERE created_at > NOW() - 7",
155            ],
156        }
157    }
158
159    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
160        self.validate_args(args)?;
161        let now = Utc::now();
162        Ok(DataValue::DateTime(
163            now.format("%Y-%m-%d %H:%M:%S").to_string(),
164        ))
165    }
166}
167
168/// TODAY function - Returns current date
169pub struct TodayFunction;
170
171impl SqlFunction for TodayFunction {
172    fn signature(&self) -> FunctionSignature {
173        FunctionSignature {
174            name: "TODAY",
175            category: FunctionCategory::Date,
176            arg_count: ArgCount::Fixed(0),
177            description: "Returns today's date",
178            returns: "DATE",
179            examples: vec![
180                "SELECT TODAY()",
181                "SELECT * FROM events WHERE event_date = TODAY()",
182            ],
183        }
184    }
185
186    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
187        self.validate_args(args)?;
188        let today = Utc::now().date_naive();
189        Ok(DataValue::String(today.format("%Y-%m-%d").to_string()))
190    }
191}
192
193/// DATEDIFF function - Calculate difference between dates
194pub struct DateDiffFunction;
195
196impl SqlFunction for DateDiffFunction {
197    fn signature(&self) -> FunctionSignature {
198        FunctionSignature {
199            name: "DATEDIFF",
200            category: FunctionCategory::Date,
201            arg_count: ArgCount::Fixed(3),
202            description: "Calculate the difference between two dates in the specified unit",
203            returns: "INTEGER",
204            examples: vec![
205                "SELECT DATEDIFF('day', '2024-01-01', '2024-01-15')",
206                "SELECT DATEDIFF('month', start_date, end_date) FROM projects",
207                "SELECT DATEDIFF('year', birth_date, TODAY()) as age",
208            ],
209        }
210    }
211
212    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
213        self.validate_args(args)?;
214
215        // First argument: unit
216        let unit = match &args[0] {
217            DataValue::String(s) => s.to_lowercase(),
218            DataValue::InternedString(s) => s.to_lowercase(),
219            _ => return Err(anyhow!("DATEDIFF unit must be a string")),
220        };
221
222        // Second argument: date1
223        let date1 = match &args[1] {
224            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
225            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
226            DataValue::Null => return Ok(DataValue::Null),
227            _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
228        };
229
230        // Third argument: date2
231        let date2 = match &args[2] {
232            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
233            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
234            DataValue::Null => return Ok(DataValue::Null),
235            _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
236        };
237
238        // Calculate difference based on unit
239        let diff = match unit.as_str() {
240            "day" | "days" => {
241                let duration = date2.signed_duration_since(date1);
242                duration.num_days()
243            }
244            "month" | "months" => {
245                // Approximate months as 30.44 days
246                let duration = date2.signed_duration_since(date1);
247                duration.num_days() / 30
248            }
249            "year" | "years" => {
250                // Approximate years as 365.25 days
251                let duration = date2.signed_duration_since(date1);
252                duration.num_days() / 365
253            }
254            "hour" | "hours" => {
255                let duration = date2.signed_duration_since(date1);
256                duration.num_hours()
257            }
258            "minute" | "minutes" => {
259                let duration = date2.signed_duration_since(date1);
260                duration.num_minutes()
261            }
262            "second" | "seconds" => {
263                let duration = date2.signed_duration_since(date1);
264                duration.num_seconds()
265            }
266            _ => {
267                return Err(anyhow!(
268                    "Unknown DATEDIFF unit: {}. Use: day, month, year, hour, minute, second",
269                    unit
270                ))
271            }
272        };
273
274        Ok(DataValue::Integer(diff))
275    }
276}
277
278/// DATEADD function - Add interval to date
279pub struct DateAddFunction;
280
281impl SqlFunction for DateAddFunction {
282    fn signature(&self) -> FunctionSignature {
283        FunctionSignature {
284            name: "DATEADD",
285            category: FunctionCategory::Date,
286            arg_count: ArgCount::Fixed(3),
287            description: "Add a specified interval to a date",
288            returns: "DATETIME",
289            examples: vec![
290                "SELECT DATEADD('day', 7, '2024-01-01')",
291                "SELECT DATEADD('month', -1, NOW())",
292                "SELECT DATEADD('year', 1, hire_date) FROM employees",
293            ],
294        }
295    }
296
297    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
298        self.validate_args(args)?;
299
300        // First argument: unit
301        let unit = match &args[0] {
302            DataValue::String(s) => s.to_lowercase(),
303            DataValue::InternedString(s) => s.to_lowercase(),
304            _ => return Err(anyhow!("DATEADD unit must be a string")),
305        };
306
307        // Second argument: amount to add
308        let amount = match &args[1] {
309            DataValue::Integer(i) => *i,
310            DataValue::Float(f) => *f as i64,
311            DataValue::Null => return Ok(DataValue::Null),
312            _ => return Err(anyhow!("DATEADD amount must be a number")),
313        };
314
315        // Third argument: base date
316        let base_date = match &args[2] {
317            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
318            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
319            DataValue::Null => return Ok(DataValue::Null),
320            _ => return Err(anyhow!("DATEADD requires date/datetime values")),
321        };
322
323        // Add the specified amount based on unit
324        let result_date = match unit.as_str() {
325            "day" | "days" => base_date + chrono::Duration::days(amount),
326            "month" | "months" => {
327                // For months, we need to be careful about month boundaries
328                let naive = base_date.naive_utc();
329                let mut year = naive.year();
330                let mut month = naive.month() as i32;
331                let day = naive.day();
332
333                month += amount as i32;
334
335                // Handle month overflow/underflow
336                while month > 12 {
337                    month -= 12;
338                    year += 1;
339                }
340                while month < 1 {
341                    month += 12;
342                    year -= 1;
343                }
344
345                // Create new date, handling day overflow (e.g., Jan 31 + 1 month = Feb 28/29)
346                let target_date =
347                    NaiveDate::from_ymd_opt(year, month as u32, day).unwrap_or_else(|| {
348                        // If day doesn't exist in target month, use the last day of that month
349                        // Try decreasing days until we find a valid one
350                        for test_day in (1..=day).rev() {
351                            if let Some(date) =
352                                NaiveDate::from_ymd_opt(year, month as u32, test_day)
353                            {
354                                return date;
355                            }
356                        }
357                        NaiveDate::from_ymd_opt(year, month as u32, 1).unwrap()
358                    });
359
360                Utc.from_utc_datetime(&target_date.and_time(base_date.naive_utc().time()))
361            }
362            "year" | "years" => {
363                let naive = base_date.naive_utc();
364                let new_year = naive.year() + amount as i32;
365                let month = naive.month();
366                let day = naive.day();
367
368                // Handle leap year edge case (Feb 29 -> Feb 28 in non-leap year)
369                let target_date =
370                    NaiveDate::from_ymd_opt(new_year, month, day).unwrap_or_else(|| {
371                        // If the date doesn't exist (e.g., Feb 29 in non-leap year), use Feb 28
372                        NaiveDate::from_ymd_opt(new_year, month, day - 1).unwrap()
373                    });
374
375                Utc.from_utc_datetime(&target_date.and_time(base_date.naive_utc().time()))
376            }
377            "hour" | "hours" => base_date + chrono::Duration::hours(amount),
378            "minute" | "minutes" => base_date + chrono::Duration::minutes(amount),
379            "second" | "seconds" => base_date + chrono::Duration::seconds(amount),
380            _ => {
381                return Err(anyhow!(
382                    "Unknown DATEADD unit: {}. Use: day, month, year, hour, minute, second",
383                    unit
384                ))
385            }
386        };
387
388        // Return as datetime string
389        Ok(DataValue::DateTime(
390            result_date.format("%Y-%m-%d %H:%M:%S").to_string(),
391        ))
392    }
393}
394
395/// Register all date/time functions
396pub fn register_date_time_functions(registry: &mut super::FunctionRegistry) {
397    registry.register(Box::new(NowFunction));
398    registry.register(Box::new(TodayFunction));
399    registry.register(Box::new(DateDiffFunction));
400    registry.register(Box::new(DateAddFunction));
401}
sql_cli/sql/functions/date_time.rs

sql_cli/sql/functions/
date_time.rs
