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

sql_cli/sql/functions/
date_time.rs
