sql_cli/sql/functions/
date_time.rs

1use anyhow::{anyhow, Result};
2use chrono::{DateTime, Datelike, NaiveDate, NaiveDateTime, TimeZone, Timelike, 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    // FIX Protocol timestamp format: YYYYMMDD-HH:MM:SS.sss (UTC)
16    // This is a standard financial messaging format (tag 60, 52, etc.)
17    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y%m%d-%H:%M:%S%.3f") {
18        return Ok(Utc.from_utc_datetime(&dt));
19    }
20    // FIX format without milliseconds
21    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y%m%d-%H:%M:%S") {
22        return Ok(Utc.from_utc_datetime(&dt));
23    }
24
25    // ISO formats (most common and unambiguous)
26    // With T separator
27    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S") {
28        return Ok(Utc.from_utc_datetime(&dt));
29    }
30    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S%.3f") {
31        return Ok(Utc.from_utc_datetime(&dt));
32    }
33    // With space separator
34    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.3f") {
35        return Ok(Utc.from_utc_datetime(&dt));
36    }
37    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f") {
38        return Ok(Utc.from_utc_datetime(&dt));
39    }
40    // Without milliseconds
41    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S") {
42        return Ok(Utc.from_utc_datetime(&dt));
43    }
44    if let Ok(dt) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
45        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
46    }
47
48    // Get date notation preference
49    let date_notation = get_date_notation();
50
51    // Date notation preference for ambiguous formats like 04/09/2025
52    if date_notation == "european" {
53        // European formats (DD/MM/YYYY) - try first
54        // Date only formats
55        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
56            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
57        }
58        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
59            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
60        }
61        // With time formats (with milliseconds)
62        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S%.3f") {
63            return Ok(Utc.from_utc_datetime(&dt));
64        }
65        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
66            return Ok(Utc.from_utc_datetime(&dt));
67        }
68        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
69            return Ok(Utc.from_utc_datetime(&dt));
70        }
71
72        // US formats (MM/DD/YYYY) - fallback
73        // Date only formats
74        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
75            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
76        }
77        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
78            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
79        }
80        // With time formats (with milliseconds)
81        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S%.3f") {
82            return Ok(Utc.from_utc_datetime(&dt));
83        }
84        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
85            return Ok(Utc.from_utc_datetime(&dt));
86        }
87        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
88            return Ok(Utc.from_utc_datetime(&dt));
89        }
90    } else {
91        // US formats (MM/DD/YYYY) - default, try first
92        // Date only formats
93        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
94            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
95        }
96        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
97            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
98        }
99        // With time formats (with milliseconds)
100        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S%.3f") {
101            return Ok(Utc.from_utc_datetime(&dt));
102        }
103        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
104            return Ok(Utc.from_utc_datetime(&dt));
105        }
106        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
107            return Ok(Utc.from_utc_datetime(&dt));
108        }
109
110        // European formats (DD/MM/YYYY) - fallback
111        // Date only formats
112        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
113            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
114        }
115        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
116            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
117        }
118        // With time formats (with milliseconds)
119        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S%.3f") {
120            return Ok(Utc.from_utc_datetime(&dt));
121        }
122        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
123            return Ok(Utc.from_utc_datetime(&dt));
124        }
125        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
126            return Ok(Utc.from_utc_datetime(&dt));
127        }
128    }
129
130    // Excel/Windows format: DD-MMM-YYYY (e.g., 15-Jan-2024)
131    if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%b-%Y") {
132        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
133    }
134
135    // Full month names: January 15, 2024 or 15 January 2024
136    if let Ok(dt) = NaiveDate::parse_from_str(s, "%B %d, %Y") {
137        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
138    }
139    if let Ok(dt) = NaiveDate::parse_from_str(s, "%d %B %Y") {
140        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
141    }
142
143    // RFC3339 (e.g., 2024-01-15T10:30:00Z)
144    if let Ok(dt) = DateTime::parse_from_rfc3339(s) {
145        return Ok(dt.with_timezone(&Utc));
146    }
147
148    Err(anyhow!("Could not parse date: {}. Supported formats: YYYYMMDD-HH:MM:SS.sss (FIX), YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY, DD-MMM-YYYY, Month DD YYYY", s))
149}
150
151/// NOW function - Returns current datetime
152pub struct NowFunction;
153
154impl SqlFunction for NowFunction {
155    fn signature(&self) -> FunctionSignature {
156        FunctionSignature {
157            name: "NOW",
158            category: FunctionCategory::Date,
159            arg_count: ArgCount::Fixed(0),
160            description: "Returns the current date and time",
161            returns: "DATETIME",
162            examples: vec![
163                "SELECT NOW()",
164                "SELECT * FROM orders WHERE created_at > NOW() - 7",
165            ],
166        }
167    }
168
169    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
170        self.validate_args(args)?;
171        let now = Utc::now();
172        Ok(DataValue::DateTime(
173            now.format("%Y-%m-%d %H:%M:%S").to_string(),
174        ))
175    }
176}
177
178/// TODAY function - Returns current date
179pub struct TodayFunction;
180
181impl SqlFunction for TodayFunction {
182    fn signature(&self) -> FunctionSignature {
183        FunctionSignature {
184            name: "TODAY",
185            category: FunctionCategory::Date,
186            arg_count: ArgCount::Fixed(0),
187            description: "Returns today's date",
188            returns: "DATE",
189            examples: vec![
190                "SELECT TODAY()",
191                "SELECT * FROM events WHERE event_date = TODAY()",
192            ],
193        }
194    }
195
196    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
197        self.validate_args(args)?;
198        let today = Utc::now().date_naive();
199        Ok(DataValue::String(today.format("%Y-%m-%d").to_string()))
200    }
201}
202
203/// DATEDIFF function - Calculate difference between dates
204pub struct DateDiffFunction;
205
206impl SqlFunction for DateDiffFunction {
207    fn signature(&self) -> FunctionSignature {
208        FunctionSignature {
209            name: "DATEDIFF",
210            category: FunctionCategory::Date,
211            arg_count: ArgCount::Range(2, 3),
212            description: "Days between two dates (MySQL style, 2 args) or difference in a specified unit (3 args)",
213            returns: "INTEGER",
214            examples: vec![
215                "SELECT DATEDIFF('2024-01-15', '2024-01-01')",         // 14 (MySQL style)
216                "SELECT DATEDIFF('day', '2024-01-01', '2024-01-15')",  // 14 (with unit)
217                "SELECT DATEDIFF('month', start_date, end_date) FROM projects",
218                "SELECT DATEDIFF('year', birth_date, TODAY()) as age",
219            ],
220        }
221    }
222
223    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
224        self.validate_args(args)?;
225
226        // MySQL-style 2-arg form: DATEDIFF(date1, date2) -> days between
227        // Returns date1 - date2, matching MySQL behaviour
228        if args.len() == 2 {
229            let date1 = match &args[0] {
230                DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
231                DataValue::InternedString(s) => parse_datetime(s.as_str())?,
232                DataValue::Null => return Ok(DataValue::Null),
233                _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
234            };
235            let date2 = match &args[1] {
236                DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
237                DataValue::InternedString(s) => parse_datetime(s.as_str())?,
238                DataValue::Null => return Ok(DataValue::Null),
239                _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
240            };
241            let duration = date1.signed_duration_since(date2);
242            return Ok(DataValue::Integer(duration.num_days()));
243        }
244
245        // 3-arg form: DATEDIFF(unit, date1, date2)
246        // First argument: unit
247        let unit = match &args[0] {
248            DataValue::String(s) => s.to_lowercase(),
249            DataValue::InternedString(s) => s.to_lowercase(),
250            _ => return Err(anyhow!("DATEDIFF unit must be a string")),
251        };
252
253        // Second argument: date1
254        let date1 = match &args[1] {
255            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
256            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
257            DataValue::Null => return Ok(DataValue::Null),
258            _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
259        };
260
261        // Third argument: date2
262        let date2 = match &args[2] {
263            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
264            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
265            DataValue::Null => return Ok(DataValue::Null),
266            _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
267        };
268
269        // Calculate difference based on unit
270        let diff = match unit.as_str() {
271            "day" | "days" => {
272                let duration = date2.signed_duration_since(date1);
273                duration.num_days()
274            }
275            "month" | "months" => {
276                // Approximate months as 30.44 days
277                let duration = date2.signed_duration_since(date1);
278                duration.num_days() / 30
279            }
280            "year" | "years" => {
281                // Approximate years as 365.25 days
282                let duration = date2.signed_duration_since(date1);
283                duration.num_days() / 365
284            }
285            "hour" | "hours" => {
286                let duration = date2.signed_duration_since(date1);
287                duration.num_hours()
288            }
289            "minute" | "minutes" => {
290                let duration = date2.signed_duration_since(date1);
291                duration.num_minutes()
292            }
293            "second" | "seconds" => {
294                let duration = date2.signed_duration_since(date1);
295                duration.num_seconds()
296            }
297            "millisecond" | "milliseconds" | "ms" => {
298                let duration = date2.signed_duration_since(date1);
299                duration.num_milliseconds()
300            }
301            "microsecond" | "microseconds" | "us" => {
302                let duration = date2.signed_duration_since(date1);
303                duration.num_microseconds().unwrap_or(0)
304            }
305            _ => {
306                return Err(anyhow!(
307                    "Unknown DATEDIFF unit: {}. Use: day, month, year, hour, minute, second, millisecond, microsecond",
308                    unit
309                ))
310            }
311        };
312
313        Ok(DataValue::Integer(diff))
314    }
315}
316
317/// DATEADD function - Add interval to date
318pub struct DateAddFunction;
319
320impl SqlFunction for DateAddFunction {
321    fn signature(&self) -> FunctionSignature {
322        FunctionSignature {
323            name: "DATEADD",
324            category: FunctionCategory::Date,
325            arg_count: ArgCount::Fixed(3),
326            description: "Add a specified interval to a date",
327            returns: "DATETIME",
328            examples: vec![
329                "SELECT DATEADD('day', 7, '2024-01-01')",
330                "SELECT DATEADD('month', -1, NOW())",
331                "SELECT DATEADD('year', 1, hire_date) FROM employees",
332            ],
333        }
334    }
335
336    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
337        self.validate_args(args)?;
338
339        // First argument: unit
340        let unit = match &args[0] {
341            DataValue::String(s) => s.to_lowercase(),
342            DataValue::InternedString(s) => s.to_lowercase(),
343            _ => return Err(anyhow!("DATEADD unit must be a string")),
344        };
345
346        // Second argument: amount to add
347        let amount = match &args[1] {
348            DataValue::Integer(i) => *i,
349            DataValue::Float(f) => *f as i64,
350            DataValue::Null => return Ok(DataValue::Null),
351            _ => return Err(anyhow!("DATEADD amount must be a number")),
352        };
353
354        // Third argument: base date
355        let base_date = match &args[2] {
356            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
357            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
358            DataValue::Null => return Ok(DataValue::Null),
359            _ => return Err(anyhow!("DATEADD requires date/datetime values")),
360        };
361
362        // Add the specified amount based on unit
363        let result_date = match unit.as_str() {
364            "day" | "days" => base_date + chrono::Duration::days(amount),
365            "month" | "months" => {
366                // For months, we need to be careful about month boundaries
367                let naive = base_date.naive_utc();
368                let mut year = naive.year();
369                let mut month = naive.month() as i32;
370                let day = naive.day();
371
372                month += amount as i32;
373
374                // Handle month overflow/underflow
375                while month > 12 {
376                    month -= 12;
377                    year += 1;
378                }
379                while month < 1 {
380                    month += 12;
381                    year -= 1;
382                }
383
384                // Create new date, handling day overflow (e.g., Jan 31 + 1 month = Feb 28/29)
385                let target_date =
386                    NaiveDate::from_ymd_opt(year, month as u32, day).unwrap_or_else(|| {
387                        // If day doesn't exist in target month, use the last day of that month
388                        // Try decreasing days until we find a valid one
389                        for test_day in (1..=day).rev() {
390                            if let Some(date) =
391                                NaiveDate::from_ymd_opt(year, month as u32, test_day)
392                            {
393                                return date;
394                            }
395                        }
396                        NaiveDate::from_ymd_opt(year, month as u32, 1).unwrap()
397                    });
398
399                Utc.from_utc_datetime(&target_date.and_time(base_date.naive_utc().time()))
400            }
401            "year" | "years" => {
402                let naive = base_date.naive_utc();
403                let new_year = naive.year() + amount as i32;
404                let month = naive.month();
405                let day = naive.day();
406
407                // Handle leap year edge case (Feb 29 -> Feb 28 in non-leap year)
408                let target_date =
409                    NaiveDate::from_ymd_opt(new_year, month, day).unwrap_or_else(|| {
410                        // If the date doesn't exist (e.g., Feb 29 in non-leap year), use Feb 28
411                        NaiveDate::from_ymd_opt(new_year, month, day - 1).unwrap()
412                    });
413
414                Utc.from_utc_datetime(&target_date.and_time(base_date.naive_utc().time()))
415            }
416            "hour" | "hours" => base_date + chrono::Duration::hours(amount),
417            "minute" | "minutes" => base_date + chrono::Duration::minutes(amount),
418            "second" | "seconds" => base_date + chrono::Duration::seconds(amount),
419            _ => {
420                return Err(anyhow!(
421                    "Unknown DATEADD unit: {}. Use: day, month, year, hour, minute, second",
422                    unit
423                ))
424            }
425        };
426
427        // Return as datetime string
428        Ok(DataValue::DateTime(
429            result_date.format("%Y-%m-%d %H:%M:%S").to_string(),
430        ))
431    }
432}
433
434/// UNIX_TIMESTAMP - Convert datetime to Unix epoch timestamp
435pub struct UnixTimestamp;
436
437impl SqlFunction for UnixTimestamp {
438    fn signature(&self) -> FunctionSignature {
439        FunctionSignature {
440            name: "UNIX_TIMESTAMP",
441            category: FunctionCategory::Date,
442            arg_count: ArgCount::Fixed(1),
443            description:
444                "Convert datetime to Unix epoch timestamp (seconds since 1970-01-01 00:00:00 UTC)",
445            returns: "INTEGER (seconds since epoch)",
446            examples: vec![
447                "SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00')",
448                "SELECT UNIX_TIMESTAMP('2024-01-01T12:30:45')",
449                "SELECT UNIX_TIMESTAMP(trade_time) FROM trades",
450            ],
451        }
452    }
453
454    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
455        if args.len() != 1 {
456            return Err(anyhow!("UNIX_TIMESTAMP expects exactly 1 argument"));
457        }
458
459        match &args[0] {
460            DataValue::DateTime(dt_str) | DataValue::String(dt_str) => {
461                let dt = parse_datetime(dt_str)?;
462                Ok(DataValue::Integer(dt.timestamp()))
463            }
464            DataValue::InternedString(dt_str) => {
465                let dt = parse_datetime(dt_str)?;
466                Ok(DataValue::Integer(dt.timestamp()))
467            }
468            DataValue::Null => Ok(DataValue::Null),
469            _ => Err(anyhow!(
470                "UNIX_TIMESTAMP expects a datetime or string argument"
471            )),
472        }
473    }
474}
475
476/// FROM_UNIXTIME - Convert Unix epoch timestamp to datetime
477pub struct FromUnixTime;
478
479impl SqlFunction for FromUnixTime {
480    fn signature(&self) -> FunctionSignature {
481        FunctionSignature {
482            name: "FROM_UNIXTIME",
483            category: FunctionCategory::Date,
484            arg_count: ArgCount::Fixed(1),
485            description: "Convert Unix epoch timestamp to datetime string",
486            returns: "DATETIME string in ISO format",
487            examples: vec![
488                "SELECT FROM_UNIXTIME(1704067200)",
489                "SELECT FROM_UNIXTIME(timestamp_col) FROM data",
490            ],
491        }
492    }
493
494    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
495        if args.len() != 1 {
496            return Err(anyhow!("FROM_UNIXTIME expects exactly 1 argument"));
497        }
498
499        match &args[0] {
500            DataValue::Integer(timestamp) => {
501                let dt = DateTime::<Utc>::from_timestamp(*timestamp, 0)
502                    .ok_or_else(|| anyhow!("Invalid timestamp: {}", timestamp))?;
503                Ok(DataValue::DateTime(
504                    dt.format("%Y-%m-%d %H:%M:%S").to_string(),
505                ))
506            }
507            DataValue::Float(timestamp) => {
508                let secs = timestamp.floor() as i64;
509                let nanos = ((timestamp - timestamp.floor()) * 1_000_000_000.0) as u32;
510                let dt = DateTime::<Utc>::from_timestamp(secs, nanos)
511                    .ok_or_else(|| anyhow!("Invalid timestamp: {}", timestamp))?;
512                Ok(DataValue::DateTime(
513                    dt.format("%Y-%m-%d %H:%M:%S%.f").to_string(),
514                ))
515            }
516            DataValue::Null => Ok(DataValue::Null),
517            _ => Err(anyhow!("FROM_UNIXTIME expects a numeric timestamp")),
518        }
519    }
520}
521
522/// TIME_BUCKET - Round timestamps down to bucket boundaries
523pub struct TimeBucket;
524
525impl SqlFunction for TimeBucket {
526    fn signature(&self) -> FunctionSignature {
527        FunctionSignature {
528            name: "TIME_BUCKET",
529            category: FunctionCategory::Date,
530            arg_count: ArgCount::Fixed(2),
531            description: "Round timestamp down to bucket boundary (for time-based grouping)",
532            returns: "INTEGER (bucket timestamp)",
533            examples: vec![
534                "SELECT TIME_BUCKET(300, UNIX_TIMESTAMP(trade_time)) as bucket FROM trades -- 5 minute buckets",
535                "SELECT TIME_BUCKET(3600, UNIX_TIMESTAMP(trade_time)) as hour FROM trades -- 1 hour buckets",
536                "SELECT TIME_BUCKET(60, timestamp_col) as minute FROM data -- 1 minute buckets",
537            ],
538        }
539    }
540
541    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
542        if args.len() != 2 {
543            return Err(anyhow!(
544                "TIME_BUCKET expects exactly 2 arguments: bucket_size, timestamp"
545            ));
546        }
547
548        let bucket_size = match &args[0] {
549            DataValue::Integer(size) => *size,
550            DataValue::Float(size) => *size as i64,
551            _ => return Err(anyhow!("TIME_BUCKET bucket_size must be numeric")),
552        };
553
554        if bucket_size <= 0 {
555            return Err(anyhow!("TIME_BUCKET bucket_size must be positive"));
556        }
557
558        match &args[1] {
559            DataValue::Integer(timestamp) => {
560                let bucket = (timestamp / bucket_size) * bucket_size;
561                Ok(DataValue::Integer(bucket))
562            }
563            DataValue::Float(timestamp) => {
564                let ts = *timestamp as i64;
565                let bucket = (ts / bucket_size) * bucket_size;
566                Ok(DataValue::Integer(bucket))
567            }
568            DataValue::Null => Ok(DataValue::Null),
569            _ => Err(anyhow!("TIME_BUCKET timestamp must be numeric")),
570        }
571    }
572}
573
574/// DAYOFWEEK function - returns day of week as number (0=Sunday, 6=Saturday)
575pub struct DayOfWeekFunction;
576
577impl SqlFunction for DayOfWeekFunction {
578    fn signature(&self) -> FunctionSignature {
579        FunctionSignature {
580            name: "DAYOFWEEK",
581            category: FunctionCategory::Date,
582            arg_count: ArgCount::Fixed(1),
583            description: "Returns day of week as number (0=Sunday, 6=Saturday)",
584            returns: "INTEGER",
585            examples: vec![
586                "SELECT DAYOFWEEK('2024-01-01')", // Returns 1 (Monday)
587                "SELECT DAYOFWEEK(NOW())",
588                "SELECT DAYOFWEEK(date_column) FROM table",
589            ],
590        }
591    }
592
593    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
594        if args.len() != 1 {
595            return Err(anyhow!("DAYOFWEEK expects exactly 1 argument"));
596        }
597
598        let date_str = match &args[0] {
599            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
600            DataValue::InternedString(s) => s.as_str(),
601            DataValue::Null => return Ok(DataValue::Null),
602            _ => return Err(anyhow!("DAYOFWEEK expects a date/datetime string")),
603        };
604
605        let dt = parse_datetime(date_str)?;
606        // chrono weekday: Mon=0, Tue=1, ..., Sun=6
607        // We want: Sun=0, Mon=1, ..., Sat=6
608        let chrono_weekday = dt.weekday().num_days_from_monday(); // 0=Mon, 6=Sun
609        let our_weekday = (chrono_weekday + 1) % 7; // Convert to 0=Sun, 6=Sat
610
611        Ok(DataValue::Integer(our_weekday as i64))
612    }
613}
614
615/// DAYNAME function - returns full day name (Monday, Tuesday, etc.)
616pub struct DayNameFunction;
617
618impl SqlFunction for DayNameFunction {
619    fn signature(&self) -> FunctionSignature {
620        FunctionSignature {
621            name: "DAYNAME",
622            category: FunctionCategory::Date,
623            arg_count: ArgCount::Range(1, 2),
624            description: "Returns day name. Optional second arg: 'full' (default) or 'short'",
625            returns: "STRING",
626            examples: vec![
627                "SELECT DAYNAME('2024-01-01')",          // Returns 'Monday'
628                "SELECT DAYNAME('2024-01-01', 'short')", // Returns 'Mon'
629                "SELECT DAYNAME(NOW(), 'full')",
630            ],
631        }
632    }
633
634    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
635        if args.is_empty() || args.len() > 2 {
636            return Err(anyhow!("DAYNAME expects 1 or 2 arguments"));
637        }
638
639        let date_str = match &args[0] {
640            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
641            DataValue::InternedString(s) => s.as_str(),
642            DataValue::Null => return Ok(DataValue::Null),
643            _ => return Err(anyhow!("DAYNAME expects a date/datetime string")),
644        };
645
646        let format = if args.len() == 2 {
647            match &args[1] {
648                DataValue::String(s) => s.as_str(),
649                DataValue::InternedString(s) => s.as_str(),
650                DataValue::Null => "full",
651                _ => return Err(anyhow!("DAYNAME format must be 'full' or 'short'")),
652            }
653        } else {
654            "full"
655        };
656
657        let dt = parse_datetime(date_str)?;
658
659        let day_name = match format {
660            "short" => dt.format("%a").to_string(), // Mon, Tue, Wed, etc.
661            "full" | _ => dt.format("%A").to_string(), // Monday, Tuesday, etc.
662        };
663
664        Ok(DataValue::String(day_name))
665    }
666}
667
668/// ISLEAPYEAR function - returns true if the year is a leap year
669pub struct IsLeapYearFunction;
670
671impl SqlFunction for IsLeapYearFunction {
672    fn signature(&self) -> FunctionSignature {
673        FunctionSignature {
674            name: "ISLEAPYEAR",
675            category: FunctionCategory::Date,
676            arg_count: ArgCount::Fixed(1),
677            description: "Returns true if the year is a leap year",
678            returns: "BOOLEAN",
679            examples: vec![
680                "SELECT ISLEAPYEAR('2024-01-01')", // Returns true
681                "SELECT ISLEAPYEAR(2024)",         // Returns true
682                "SELECT ISLEAPYEAR(2023)",         // Returns false
683            ],
684        }
685    }
686
687    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
688        if args.len() != 1 {
689            return Err(anyhow!("ISLEAPYEAR expects exactly 1 argument"));
690        }
691
692        let year = match &args[0] {
693            DataValue::Integer(y) => *y as i32,
694            DataValue::Float(f) => *f as i32,
695            DataValue::String(s) | DataValue::DateTime(s) => {
696                // Try to parse as a year number first
697                if let Ok(y) = s.parse::<i32>() {
698                    y
699                } else {
700                    // Try to parse as a date
701                    let dt = parse_datetime(s.as_str())?;
702                    dt.year()
703                }
704            }
705            DataValue::InternedString(s) => {
706                // Try to parse as a year number first
707                if let Ok(y) = s.parse::<i32>() {
708                    y
709                } else {
710                    // Try to parse as a date
711                    let dt = parse_datetime(s.as_str())?;
712                    dt.year()
713                }
714            }
715            DataValue::Null => return Ok(DataValue::Null),
716            _ => return Err(anyhow!("ISLEAPYEAR expects a year number or date")),
717        };
718
719        // Leap year calculation
720        let is_leap = (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0);
721
722        Ok(DataValue::Boolean(is_leap))
723    }
724}
725
726/// WEEKOFYEAR function - returns the ISO week number (1-53)
727pub struct WeekOfYearFunction;
728
729impl SqlFunction for WeekOfYearFunction {
730    fn signature(&self) -> FunctionSignature {
731        FunctionSignature {
732            name: "WEEKOFYEAR",
733            category: FunctionCategory::Date,
734            arg_count: ArgCount::Fixed(1),
735            description: "Returns the ISO week number of the year (1-53)",
736            returns: "INTEGER",
737            examples: vec![
738                "SELECT WEEKOFYEAR('2024-01-01')", // Returns week number
739                "SELECT WEEKOFYEAR(NOW())",
740            ],
741        }
742    }
743
744    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
745        if args.len() != 1 {
746            return Err(anyhow!("WEEKOFYEAR expects exactly 1 argument"));
747        }
748
749        let date_str = match &args[0] {
750            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
751            DataValue::InternedString(s) => s.as_str(),
752            DataValue::Null => return Ok(DataValue::Null),
753            _ => return Err(anyhow!("WEEKOFYEAR expects a date/datetime string")),
754        };
755
756        let dt = parse_datetime(date_str)?;
757        let week = dt.iso_week().week();
758
759        Ok(DataValue::Integer(week as i64))
760    }
761}
762
763/// QUARTER function - returns the quarter of the year (1-4)
764pub struct QuarterFunction;
765
766impl SqlFunction for QuarterFunction {
767    fn signature(&self) -> FunctionSignature {
768        FunctionSignature {
769            name: "QUARTER",
770            category: FunctionCategory::Date,
771            arg_count: ArgCount::Fixed(1),
772            description: "Returns the quarter of the year (1-4)",
773            returns: "INTEGER",
774            examples: vec![
775                "SELECT QUARTER('2024-01-15')", // Returns 1
776                "SELECT QUARTER('2024-07-01')", // Returns 3
777            ],
778        }
779    }
780
781    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
782        if args.len() != 1 {
783            return Err(anyhow!("QUARTER expects exactly 1 argument"));
784        }
785
786        let date_str = match &args[0] {
787            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
788            DataValue::InternedString(s) => s.as_str(),
789            DataValue::Null => return Ok(DataValue::Null),
790            _ => return Err(anyhow!("QUARTER expects a date/datetime string")),
791        };
792
793        let dt = parse_datetime(date_str)?;
794        let month = dt.month();
795        let quarter = (month - 1) / 3 + 1;
796
797        Ok(DataValue::Integer(quarter as i64))
798    }
799}
800
801/// YEAR function - extracts year from date
802pub struct YearFunction;
803
804impl SqlFunction for YearFunction {
805    fn signature(&self) -> FunctionSignature {
806        FunctionSignature {
807            name: "YEAR",
808            category: FunctionCategory::Date,
809            arg_count: ArgCount::Fixed(1),
810            description: "Returns the year from a date",
811            returns: "INTEGER",
812            examples: vec![
813                "SELECT YEAR('2024-03-15')", // Returns 2024
814                "SELECT YEAR(NOW())",        // Returns current year
815            ],
816        }
817    }
818
819    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
820        if args.len() != 1 {
821            return Err(anyhow!("YEAR expects exactly 1 argument"));
822        }
823
824        let date_str = match &args[0] {
825            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
826            DataValue::InternedString(s) => s.as_str(),
827            DataValue::Null => return Ok(DataValue::Null),
828            _ => return Err(anyhow!("YEAR expects a date/datetime string")),
829        };
830
831        let dt = parse_datetime(date_str)?;
832        Ok(DataValue::Float(dt.year() as f64))
833    }
834}
835
836/// MONTH function - extracts month from date (1-12)
837pub struct MonthFunction;
838
839impl SqlFunction for MonthFunction {
840    fn signature(&self) -> FunctionSignature {
841        FunctionSignature {
842            name: "MONTH",
843            category: FunctionCategory::Date,
844            arg_count: ArgCount::Fixed(1),
845            description: "Returns the month from a date (1-12)",
846            returns: "INTEGER",
847            examples: vec![
848                "SELECT MONTH('2024-03-15')", // Returns 3
849                "SELECT MONTH(NOW())",        // Returns current month
850            ],
851        }
852    }
853
854    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
855        if args.len() != 1 {
856            return Err(anyhow!("MONTH expects exactly 1 argument"));
857        }
858
859        let date_str = match &args[0] {
860            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
861            DataValue::InternedString(s) => s.as_str(),
862            DataValue::Null => return Ok(DataValue::Null),
863            _ => return Err(anyhow!("MONTH expects a date/datetime string")),
864        };
865
866        let dt = parse_datetime(date_str)?;
867        Ok(DataValue::Float(dt.month() as f64))
868    }
869}
870
871/// DAY function - extracts day of month from date (1-31)
872pub struct DayFunction;
873
874impl SqlFunction for DayFunction {
875    fn signature(&self) -> FunctionSignature {
876        FunctionSignature {
877            name: "DAY",
878            category: FunctionCategory::Date,
879            arg_count: ArgCount::Fixed(1),
880            description: "Returns the day of month from a date (1-31)",
881            returns: "INTEGER",
882            examples: vec![
883                "SELECT DAY('2024-03-15')", // Returns 15
884                "SELECT DAY(NOW())",        // Returns current day
885            ],
886        }
887    }
888
889    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
890        if args.len() != 1 {
891            return Err(anyhow!("DAY expects exactly 1 argument"));
892        }
893
894        let date_str = match &args[0] {
895            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
896            DataValue::InternedString(s) => s.as_str(),
897            DataValue::Null => return Ok(DataValue::Null),
898            _ => return Err(anyhow!("DAY expects a date/datetime string")),
899        };
900
901        let dt = parse_datetime(date_str)?;
902        Ok(DataValue::Float(dt.day() as f64))
903    }
904}
905
906/// HOUR function - extracts hour of day from datetime (0-23)
907pub struct HourFunction;
908
909impl SqlFunction for HourFunction {
910    fn signature(&self) -> FunctionSignature {
911        FunctionSignature {
912            name: "HOUR",
913            category: FunctionCategory::Date,
914            arg_count: ArgCount::Fixed(1),
915            description: "Returns the hour of day from a datetime (0-23)",
916            returns: "INTEGER",
917            examples: vec![
918                "SELECT HOUR('2024-03-15 14:30:00')", // Returns 14
919                "SELECT HOUR(NOW())",
920                "SELECT HOUR(order_timestamp) FROM orders",
921            ],
922        }
923    }
924
925    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
926        if args.len() != 1 {
927            return Err(anyhow!("HOUR expects exactly 1 argument"));
928        }
929
930        let date_str = match &args[0] {
931            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
932            DataValue::InternedString(s) => s.as_str(),
933            DataValue::Null => return Ok(DataValue::Null),
934            _ => return Err(anyhow!("HOUR expects a date/datetime string")),
935        };
936
937        let dt = parse_datetime(date_str)?;
938        Ok(DataValue::Integer(dt.hour() as i64))
939    }
940}
941
942/// MINUTE function - extracts minute from datetime (0-59)
943pub struct MinuteFunction;
944
945impl SqlFunction for MinuteFunction {
946    fn signature(&self) -> FunctionSignature {
947        FunctionSignature {
948            name: "MINUTE",
949            category: FunctionCategory::Date,
950            arg_count: ArgCount::Fixed(1),
951            description: "Returns the minute from a datetime (0-59)",
952            returns: "INTEGER",
953            examples: vec![
954                "SELECT MINUTE('2024-03-15 14:30:00')", // Returns 30
955                "SELECT MINUTE(NOW())",
956            ],
957        }
958    }
959
960    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
961        if args.len() != 1 {
962            return Err(anyhow!("MINUTE expects exactly 1 argument"));
963        }
964
965        let date_str = match &args[0] {
966            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
967            DataValue::InternedString(s) => s.as_str(),
968            DataValue::Null => return Ok(DataValue::Null),
969            _ => return Err(anyhow!("MINUTE expects a date/datetime string")),
970        };
971
972        let dt = parse_datetime(date_str)?;
973        Ok(DataValue::Integer(dt.minute() as i64))
974    }
975}
976
977/// SECOND function - extracts second from datetime (0-59)
978pub struct SecondFunction;
979
980impl SqlFunction for SecondFunction {
981    fn signature(&self) -> FunctionSignature {
982        FunctionSignature {
983            name: "SECOND",
984            category: FunctionCategory::Date,
985            arg_count: ArgCount::Fixed(1),
986            description: "Returns the second from a datetime (0-59)",
987            returns: "INTEGER",
988            examples: vec![
989                "SELECT SECOND('2024-03-15 14:30:45')", // Returns 45
990                "SELECT SECOND(NOW())",
991            ],
992        }
993    }
994
995    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
996        if args.len() != 1 {
997            return Err(anyhow!("SECOND expects exactly 1 argument"));
998        }
999
1000        let date_str = match &args[0] {
1001            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
1002            DataValue::InternedString(s) => s.as_str(),
1003            DataValue::Null => return Ok(DataValue::Null),
1004            _ => return Err(anyhow!("SECOND expects a date/datetime string")),
1005        };
1006
1007        let dt = parse_datetime(date_str)?;
1008        Ok(DataValue::Integer(dt.second() as i64))
1009    }
1010}
1011
1012/// YEARWEEK function - returns year and week as YYYYWW integer (MySQL-compatible)
1013///
1014/// Mode argument (default 0):
1015///   0: Week starts Sunday, week 1 is the one containing January 1
1016///   1 / 3: ISO week — week starts Monday, week 1 has 4+ days in new year
1017///   2: Week starts Sunday, week 1 has 4+ days in new year
1018///
1019/// Modes 1/3 use chrono's ISO week (which may return prior year for early-January dates,
1020/// matching MySQL's behaviour).
1021pub struct YearWeekFunction;
1022
1023impl SqlFunction for YearWeekFunction {
1024    fn signature(&self) -> FunctionSignature {
1025        FunctionSignature {
1026            name: "YEARWEEK",
1027            category: FunctionCategory::Date,
1028            arg_count: ArgCount::Range(1, 2),
1029            description: "Returns year+week as YYYYWW integer. Optional mode arg: 0 (default, Sunday-start) or 1/3 (ISO, Monday-start)",
1030            returns: "INTEGER",
1031            examples: vec![
1032                "SELECT YEARWEEK('2024-01-15')",    // 202403 (mode 0)
1033                "SELECT YEARWEEK('2024-01-15', 1)", // 202403 (ISO)
1034                "SELECT YEARWEEK(meeting_date, 1) FROM meetings",
1035            ],
1036        }
1037    }
1038
1039    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
1040        if args.is_empty() || args.len() > 2 {
1041            return Err(anyhow!("YEARWEEK expects 1 or 2 arguments"));
1042        }
1043
1044        let date_str = match &args[0] {
1045            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
1046            DataValue::InternedString(s) => s.as_str(),
1047            DataValue::Null => return Ok(DataValue::Null),
1048            _ => return Err(anyhow!("YEARWEEK expects a date/datetime string")),
1049        };
1050
1051        let mode = if args.len() == 2 {
1052            match &args[1] {
1053                DataValue::Integer(i) => *i,
1054                DataValue::Float(f) => *f as i64,
1055                DataValue::Null => 0,
1056                _ => return Err(anyhow!("YEARWEEK mode must be an integer")),
1057            }
1058        } else {
1059            0
1060        };
1061
1062        let dt = parse_datetime(date_str)?;
1063
1064        let (year, week) = match mode {
1065            // ISO week: Monday-start, week 1 has 4+ days in the new year.
1066            // iso_week().year() correctly returns the ISO year (which may be year-1 for
1067            // early-January dates that belong to the prior year's last ISO week).
1068            1 | 3 => {
1069                let iso = dt.iso_week();
1070                (iso.year(), iso.week())
1071            }
1072            // Sunday-start, week 1 is the one containing January 1.
1073            // Counted as: ((day_of_year - 1) + (weekday_of_jan1 as Sun-based)) / 7 + 1
1074            0 | 2 => {
1075                let year = dt.year();
1076                let jan1 = NaiveDate::from_ymd_opt(year, 1, 1)
1077                    .ok_or_else(|| anyhow!("Invalid year: {}", year))?;
1078                // Sun=0, Mon=1, ..., Sat=6
1079                let jan1_sun_offset = (jan1.weekday().num_days_from_monday() + 1) % 7;
1080                let doy = dt.ordinal(); // 1-based day of year
1081                let week = (doy - 1 + jan1_sun_offset) / 7 + 1;
1082                (year, week)
1083            }
1084            _ => {
1085                return Err(anyhow!(
1086                    "YEARWEEK mode {} not supported. Use 0 (default) or 1/3 (ISO)",
1087                    mode
1088                ))
1089            }
1090        };
1091
1092        let yearweek = (year as i64) * 100 + (week as i64);
1093        Ok(DataValue::Integer(yearweek))
1094    }
1095}
1096
1097/// MONTHNAME function - returns the month name
1098pub struct MonthNameFunction;
1099
1100impl SqlFunction for MonthNameFunction {
1101    fn signature(&self) -> FunctionSignature {
1102        FunctionSignature {
1103            name: "MONTHNAME",
1104            category: FunctionCategory::Date,
1105            arg_count: ArgCount::Range(1, 2),
1106            description: "Returns month name. Optional second arg: 'full' (default) or 'short'",
1107            returns: "STRING",
1108            examples: vec![
1109                "SELECT MONTHNAME('2024-01-15')",          // Returns 'January'
1110                "SELECT MONTHNAME('2024-01-15', 'short')", // Returns 'Jan'
1111            ],
1112        }
1113    }
1114
1115    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
1116        if args.is_empty() || args.len() > 2 {
1117            return Err(anyhow!("MONTHNAME expects 1 or 2 arguments"));
1118        }
1119
1120        let date_str = match &args[0] {
1121            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
1122            DataValue::InternedString(s) => s.as_str(),
1123            DataValue::Null => return Ok(DataValue::Null),
1124            _ => return Err(anyhow!("MONTHNAME expects a date/datetime string")),
1125        };
1126
1127        let format = if args.len() == 2 {
1128            match &args[1] {
1129                DataValue::String(s) => s.as_str(),
1130                DataValue::InternedString(s) => s.as_str(),
1131                DataValue::Null => "full",
1132                _ => return Err(anyhow!("MONTHNAME format must be 'full' or 'short'")),
1133            }
1134        } else {
1135            "full"
1136        };
1137
1138        let dt = parse_datetime(date_str)?;
1139
1140        let month_name = match format {
1141            "short" => dt.format("%b").to_string(), // Jan, Feb, Mar, etc.
1142            "full" | _ => dt.format("%B").to_string(), // January, February, etc.
1143        };
1144
1145        Ok(DataValue::String(month_name))
1146    }
1147}
1148
1149/// PARSE_DATETIME - Parse datetime with custom format string
1150pub struct ParseDateTimeFunction;
1151
1152impl SqlFunction for ParseDateTimeFunction {
1153    fn signature(&self) -> FunctionSignature {
1154        FunctionSignature {
1155            name: "PARSE_DATETIME",
1156            category: FunctionCategory::Date,
1157            arg_count: ArgCount::Fixed(2),
1158            description: "Parse datetime string with custom format (uses chrono strftime format)",
1159            returns: "DATETIME",
1160            examples: vec![
1161                "SELECT PARSE_DATETIME('15/01/2024', '%d/%m/%Y')",
1162                "SELECT PARSE_DATETIME('Jan 15, 2024 14:30', '%b %d, %Y %H:%M')",
1163                "SELECT PARSE_DATETIME('2024-01-15T14:30:00', '%Y-%m-%dT%H:%M:%S')",
1164                "SELECT PARSE_DATETIME(date_string, '%Y%m%d-%H:%M:%S%.3f') FROM data -- FIX format",
1165            ],
1166        }
1167    }
1168
1169    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
1170        self.validate_args(args)?;
1171
1172        let date_str = match &args[0] {
1173            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
1174            DataValue::InternedString(s) => s.as_str(),
1175            DataValue::Null => return Ok(DataValue::Null),
1176            _ => return Err(anyhow!("PARSE_DATETIME expects a string as first argument")),
1177        };
1178
1179        let format_str = match &args[1] {
1180            DataValue::String(s) => s.as_str(),
1181            DataValue::InternedString(s) => s.as_str(),
1182            DataValue::Null => return Ok(DataValue::Null),
1183            _ => {
1184                return Err(anyhow!(
1185                    "PARSE_DATETIME expects a format string as second argument"
1186                ))
1187            }
1188        };
1189
1190        // Try parsing with time component first
1191        if let Ok(dt) = NaiveDateTime::parse_from_str(date_str, format_str) {
1192            return Ok(DataValue::DateTime(
1193                Utc.from_utc_datetime(&dt)
1194                    .format("%Y-%m-%d %H:%M:%S%.3f")
1195                    .to_string(),
1196            ));
1197        }
1198
1199        // Try parsing as date only
1200        if let Ok(d) = NaiveDate::parse_from_str(date_str, format_str) {
1201            return Ok(DataValue::DateTime(
1202                Utc.from_utc_datetime(&d.and_hms_opt(0, 0, 0).unwrap())
1203                    .format("%Y-%m-%d %H:%M:%S%.3f")
1204                    .to_string(),
1205            ));
1206        }
1207
1208        Err(anyhow!(
1209            "Failed to parse '{}' with format '{}'. See https://docs.rs/chrono/latest/chrono/format/strftime/index.html",
1210            date_str,
1211            format_str
1212        ))
1213    }
1214}
1215
1216/// PARSE_DATETIME_UTC - Parse datetime and explicitly interpret as UTC
1217pub struct ParseDateTimeUtcFunction;
1218
1219impl SqlFunction for ParseDateTimeUtcFunction {
1220    fn signature(&self) -> FunctionSignature {
1221        FunctionSignature {
1222            name: "PARSE_DATETIME_UTC",
1223            category: FunctionCategory::Date,
1224            arg_count: ArgCount::Range(1, 2),
1225            description: "Parse datetime as UTC. With 1 arg: auto-detect format. With 2 args: use custom format",
1226            returns: "DATETIME (UTC)",
1227            examples: vec![
1228                "SELECT PARSE_DATETIME_UTC('2024-01-15 14:30:00')",
1229                "SELECT PARSE_DATETIME_UTC('20250925-14:52:15.567') -- FIX format auto-detected",
1230                "SELECT PARSE_DATETIME_UTC('15/01/2024 14:30', '%d/%m/%Y %H:%M')",
1231            ],
1232        }
1233    }
1234
1235    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
1236        if args.is_empty() || args.len() > 2 {
1237            return Err(anyhow!("PARSE_DATETIME_UTC expects 1 or 2 arguments"));
1238        }
1239
1240        let date_str = match &args[0] {
1241            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
1242            DataValue::InternedString(s) => s.as_str(),
1243            DataValue::Null => return Ok(DataValue::Null),
1244            _ => {
1245                return Err(anyhow!(
1246                    "PARSE_DATETIME_UTC expects a string as first argument"
1247                ))
1248            }
1249        };
1250
1251        // If format string provided, use it
1252        if args.len() == 2 {
1253            let format_str = match &args[1] {
1254                DataValue::String(s) => s.as_str(),
1255                DataValue::InternedString(s) => s.as_str(),
1256                DataValue::Null => return Ok(DataValue::Null),
1257                _ => {
1258                    return Err(anyhow!(
1259                        "PARSE_DATETIME_UTC expects a format string as second argument"
1260                    ))
1261                }
1262            };
1263
1264            // Try parsing with time component first
1265            if let Ok(dt) = NaiveDateTime::parse_from_str(date_str, format_str) {
1266                return Ok(DataValue::DateTime(
1267                    Utc.from_utc_datetime(&dt)
1268                        .format("%Y-%m-%d %H:%M:%S%.3f")
1269                        .to_string(),
1270                ));
1271            }
1272
1273            // Try parsing as date only
1274            if let Ok(d) = NaiveDate::parse_from_str(date_str, format_str) {
1275                return Ok(DataValue::DateTime(
1276                    Utc.from_utc_datetime(&d.and_hms_opt(0, 0, 0).unwrap())
1277                        .format("%Y-%m-%d %H:%M:%S%.3f")
1278                        .to_string(),
1279                ));
1280            }
1281
1282            return Err(anyhow!(
1283                "Failed to parse '{}' with format '{}'",
1284                date_str,
1285                format_str
1286            ));
1287        }
1288
1289        // No format string - use auto-detection (existing parse_datetime function)
1290        let dt = parse_datetime(date_str)?;
1291        Ok(DataValue::DateTime(
1292            dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string(),
1293        ))
1294    }
1295}
1296
1297/// DATETIME constructor - Create datetime from components
1298pub struct DateTimeConstructor;
1299
1300impl SqlFunction for DateTimeConstructor {
1301    fn signature(&self) -> FunctionSignature {
1302        FunctionSignature {
1303            name: "DATETIME",
1304            category: FunctionCategory::Date,
1305            arg_count: ArgCount::Range(3, 7),
1306            description: "Create datetime from components: (year, month, day, [hour], [minute], [second], [is_utc])",
1307            returns: "DATETIME",
1308            examples: vec![
1309                "SELECT DATETIME(2024, 1, 15)",
1310                "SELECT DATETIME(2024, 1, 15, 14, 30, 0)",
1311                "SELECT DATETIME(2024, 12, 31, 23, 59, 59)",
1312                "-- Note: All times are interpreted as UTC",
1313            ],
1314        }
1315    }
1316
1317    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
1318        if args.len() < 3 || args.len() > 7 {
1319            return Err(anyhow!("DATETIME expects 3-7 arguments: year, month, day, [hour], [minute], [second], [is_utc]"));
1320        }
1321
1322        // Extract components
1323        let year = match &args[0] {
1324            DataValue::Integer(i) => *i as i32,
1325            DataValue::Float(f) => *f as i32,
1326            DataValue::Null => return Ok(DataValue::Null),
1327            _ => return Err(anyhow!("DATETIME year must be numeric")),
1328        };
1329
1330        let month = match &args[1] {
1331            DataValue::Integer(i) => *i as u32,
1332            DataValue::Float(f) => *f as u32,
1333            DataValue::Null => return Ok(DataValue::Null),
1334            _ => return Err(anyhow!("DATETIME month must be numeric")),
1335        };
1336
1337        let day = match &args[2] {
1338            DataValue::Integer(i) => *i as u32,
1339            DataValue::Float(f) => *f as u32,
1340            DataValue::Null => return Ok(DataValue::Null),
1341            _ => return Err(anyhow!("DATETIME day must be numeric")),
1342        };
1343
1344        let hour = if args.len() > 3 {
1345            match &args[3] {
1346                DataValue::Integer(i) => *i as u32,
1347                DataValue::Float(f) => *f as u32,
1348                DataValue::Null => return Ok(DataValue::Null),
1349                _ => return Err(anyhow!("DATETIME hour must be numeric")),
1350            }
1351        } else {
1352            0
1353        };
1354
1355        let minute = if args.len() > 4 {
1356            match &args[4] {
1357                DataValue::Integer(i) => *i as u32,
1358                DataValue::Float(f) => *f as u32,
1359                DataValue::Null => return Ok(DataValue::Null),
1360                _ => return Err(anyhow!("DATETIME minute must be numeric")),
1361            }
1362        } else {
1363            0
1364        };
1365
1366        let second = if args.len() > 5 {
1367            match &args[5] {
1368                DataValue::Integer(i) => *i as u32,
1369                DataValue::Float(f) => *f as u32,
1370                DataValue::Null => return Ok(DataValue::Null),
1371                _ => return Err(anyhow!("DATETIME second must be numeric")),
1372            }
1373        } else {
1374            0
1375        };
1376
1377        // is_utc parameter (default true)
1378        let _is_utc = if args.len() > 6 {
1379            match &args[6] {
1380                DataValue::Boolean(b) => *b,
1381                DataValue::Integer(i) => *i != 0,
1382                DataValue::Null => true,
1383                _ => return Err(anyhow!("DATETIME is_utc must be boolean")),
1384            }
1385        } else {
1386            true
1387        };
1388
1389        // Create date
1390        let date = NaiveDate::from_ymd_opt(year, month, day)
1391            .ok_or_else(|| anyhow!("Invalid date: {}-{}-{}", year, month, day))?;
1392
1393        // Create datetime
1394        let dt = date
1395            .and_hms_opt(hour, minute, second)
1396            .ok_or_else(|| anyhow!("Invalid time: {}:{}:{}", hour, minute, second))?;
1397
1398        // For now, always interpret as UTC (local timezone support would require additional dependencies)
1399        let utc_dt = Utc.from_utc_datetime(&dt);
1400
1401        Ok(DataValue::DateTime(
1402            utc_dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string(),
1403        ))
1404    }
1405}
1406
1407/// Register all date/time functions
1408pub fn register_date_time_functions(registry: &mut super::FunctionRegistry) {
1409    registry.register(Box::new(NowFunction));
1410    registry.register(Box::new(TodayFunction));
1411    registry.register(Box::new(DateDiffFunction));
1412    registry.register(Box::new(DateAddFunction));
1413    registry.register(Box::new(UnixTimestamp));
1414    registry.register(Box::new(FromUnixTime));
1415    registry.register(Box::new(TimeBucket));
1416
1417    // Date extraction functions
1418    registry.register(Box::new(YearFunction));
1419    registry.register(Box::new(MonthFunction));
1420    registry.register(Box::new(DayFunction));
1421    registry.register(Box::new(HourFunction));
1422    registry.register(Box::new(MinuteFunction));
1423    registry.register(Box::new(SecondFunction));
1424    registry.register(Box::new(DayOfWeekFunction));
1425    registry.register(Box::new(DayNameFunction));
1426    registry.register(Box::new(MonthNameFunction));
1427
1428    // Date utility functions
1429    registry.register(Box::new(IsLeapYearFunction));
1430    registry.register(Box::new(WeekOfYearFunction));
1431    registry.register(Box::new(YearWeekFunction));
1432    registry.register(Box::new(QuarterFunction));
1433
1434    // Flexible parsing functions
1435    registry.register(Box::new(ParseDateTimeFunction));
1436    registry.register(Box::new(ParseDateTimeUtcFunction));
1437    registry.register(Box::new(DateTimeConstructor));
1438}
1439
1440#[cfg(test)]
1441mod tests {
1442    use super::*;
1443
1444    fn s(v: &str) -> DataValue {
1445        DataValue::String(v.to_string())
1446    }
1447
1448    #[test]
1449    fn test_hour_extracts_hour() {
1450        let func = HourFunction;
1451        assert_eq!(
1452            func.evaluate(&[s("2024-03-15 14:30:45")]).unwrap(),
1453            DataValue::Integer(14)
1454        );
1455        assert_eq!(
1456            func.evaluate(&[s("2024-03-15 00:00:00")]).unwrap(),
1457            DataValue::Integer(0)
1458        );
1459        assert_eq!(
1460            func.evaluate(&[s("2024-03-15 23:59:59")]).unwrap(),
1461            DataValue::Integer(23)
1462        );
1463    }
1464
1465    #[test]
1466    fn test_hour_null_passthrough() {
1467        assert_eq!(
1468            HourFunction.evaluate(&[DataValue::Null]).unwrap(),
1469            DataValue::Null
1470        );
1471    }
1472
1473    #[test]
1474    fn test_minute_extracts_minute() {
1475        assert_eq!(
1476            MinuteFunction
1477                .evaluate(&[s("2024-03-15 14:30:45")])
1478                .unwrap(),
1479            DataValue::Integer(30)
1480        );
1481    }
1482
1483    #[test]
1484    fn test_second_extracts_second() {
1485        assert_eq!(
1486            SecondFunction
1487                .evaluate(&[s("2024-03-15 14:30:45")])
1488                .unwrap(),
1489            DataValue::Integer(45)
1490        );
1491    }
1492
1493    #[test]
1494    fn test_yearweek_default_mode_sunday_start() {
1495        // Mode 0: Sunday-start, week 1 contains Jan 1.
1496        // 2024-01-01 is Monday, so Jan 1 is in week 1 (Sunday Dec 31 is part of previous year).
1497        assert_eq!(
1498            YearWeekFunction.evaluate(&[s("2024-01-01")]).unwrap(),
1499            DataValue::Integer(202401)
1500        );
1501        // 2024-01-15 is in week 3 under mode 0.
1502        assert_eq!(
1503            YearWeekFunction.evaluate(&[s("2024-01-15")]).unwrap(),
1504            DataValue::Integer(202403)
1505        );
1506    }
1507
1508    #[test]
1509    fn test_yearweek_iso_mode() {
1510        // Mode 1: ISO week, Monday-start, week 1 has 4+ days in new year.
1511        // 2024-01-01 was Monday → ISO week 1 of 2024.
1512        assert_eq!(
1513            YearWeekFunction
1514                .evaluate(&[s("2024-01-01"), DataValue::Integer(1)])
1515                .unwrap(),
1516            DataValue::Integer(202401)
1517        );
1518        // 2023-01-01 was Sunday → belongs to ISO week 52 of 2022.
1519        assert_eq!(
1520            YearWeekFunction
1521                .evaluate(&[s("2023-01-01"), DataValue::Integer(1)])
1522                .unwrap(),
1523            DataValue::Integer(202252)
1524        );
1525        // 2021-01-01 was Friday → ISO week 53 of 2020.
1526        assert_eq!(
1527            YearWeekFunction
1528                .evaluate(&[s("2021-01-01"), DataValue::Integer(1)])
1529                .unwrap(),
1530            DataValue::Integer(202053)
1531        );
1532    }
1533
1534    #[test]
1535    fn test_yearweek_mode_3_equals_mode_1() {
1536        let a = YearWeekFunction
1537            .evaluate(&[s("2023-06-05"), DataValue::Integer(1)])
1538            .unwrap();
1539        let b = YearWeekFunction
1540            .evaluate(&[s("2023-06-05"), DataValue::Integer(3)])
1541            .unwrap();
1542        assert_eq!(a, b);
1543    }
1544
1545    #[test]
1546    fn test_yearweek_rejects_unsupported_mode() {
1547        assert!(YearWeekFunction
1548            .evaluate(&[s("2024-01-15"), DataValue::Integer(5)])
1549            .is_err());
1550    }
1551}
sql_cli/sql/functions/date_time.rs

sql_cli/sql/functions/
date_time.rs
