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/// UNIX_TIMESTAMP - Convert datetime to Unix epoch timestamp
396pub struct UnixTimestamp;
397
398impl SqlFunction for UnixTimestamp {
399    fn signature(&self) -> FunctionSignature {
400        FunctionSignature {
401            name: "UNIX_TIMESTAMP",
402            category: FunctionCategory::Date,
403            arg_count: ArgCount::Fixed(1),
404            description:
405                "Convert datetime to Unix epoch timestamp (seconds since 1970-01-01 00:00:00 UTC)",
406            returns: "INTEGER (seconds since epoch)",
407            examples: vec![
408                "SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00')",
409                "SELECT UNIX_TIMESTAMP('2024-01-01T12:30:45')",
410                "SELECT UNIX_TIMESTAMP(trade_time) FROM trades",
411            ],
412        }
413    }
414
415    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
416        if args.len() != 1 {
417            return Err(anyhow!("UNIX_TIMESTAMP expects exactly 1 argument"));
418        }
419
420        match &args[0] {
421            DataValue::DateTime(dt_str) | DataValue::String(dt_str) => {
422                let dt = parse_datetime(dt_str)?;
423                Ok(DataValue::Integer(dt.timestamp()))
424            }
425            DataValue::InternedString(dt_str) => {
426                let dt = parse_datetime(dt_str)?;
427                Ok(DataValue::Integer(dt.timestamp()))
428            }
429            DataValue::Null => Ok(DataValue::Null),
430            _ => Err(anyhow!(
431                "UNIX_TIMESTAMP expects a datetime or string argument"
432            )),
433        }
434    }
435}
436
437/// FROM_UNIXTIME - Convert Unix epoch timestamp to datetime
438pub struct FromUnixTime;
439
440impl SqlFunction for FromUnixTime {
441    fn signature(&self) -> FunctionSignature {
442        FunctionSignature {
443            name: "FROM_UNIXTIME",
444            category: FunctionCategory::Date,
445            arg_count: ArgCount::Fixed(1),
446            description: "Convert Unix epoch timestamp to datetime string",
447            returns: "DATETIME string in ISO format",
448            examples: vec![
449                "SELECT FROM_UNIXTIME(1704067200)",
450                "SELECT FROM_UNIXTIME(timestamp_col) FROM data",
451            ],
452        }
453    }
454
455    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
456        if args.len() != 1 {
457            return Err(anyhow!("FROM_UNIXTIME expects exactly 1 argument"));
458        }
459
460        match &args[0] {
461            DataValue::Integer(timestamp) => {
462                let dt = DateTime::<Utc>::from_timestamp(*timestamp, 0)
463                    .ok_or_else(|| anyhow!("Invalid timestamp: {}", timestamp))?;
464                Ok(DataValue::DateTime(
465                    dt.format("%Y-%m-%d %H:%M:%S").to_string(),
466                ))
467            }
468            DataValue::Float(timestamp) => {
469                let secs = timestamp.floor() as i64;
470                let nanos = ((timestamp - timestamp.floor()) * 1_000_000_000.0) as u32;
471                let dt = DateTime::<Utc>::from_timestamp(secs, nanos)
472                    .ok_or_else(|| anyhow!("Invalid timestamp: {}", timestamp))?;
473                Ok(DataValue::DateTime(
474                    dt.format("%Y-%m-%d %H:%M:%S%.f").to_string(),
475                ))
476            }
477            DataValue::Null => Ok(DataValue::Null),
478            _ => Err(anyhow!("FROM_UNIXTIME expects a numeric timestamp")),
479        }
480    }
481}
482
483/// TIME_BUCKET - Round timestamps down to bucket boundaries
484pub struct TimeBucket;
485
486impl SqlFunction for TimeBucket {
487    fn signature(&self) -> FunctionSignature {
488        FunctionSignature {
489            name: "TIME_BUCKET",
490            category: FunctionCategory::Date,
491            arg_count: ArgCount::Fixed(2),
492            description: "Round timestamp down to bucket boundary (for time-based grouping)",
493            returns: "INTEGER (bucket timestamp)",
494            examples: vec![
495                "SELECT TIME_BUCKET(300, UNIX_TIMESTAMP(trade_time)) as bucket FROM trades -- 5 minute buckets",
496                "SELECT TIME_BUCKET(3600, UNIX_TIMESTAMP(trade_time)) as hour FROM trades -- 1 hour buckets",
497                "SELECT TIME_BUCKET(60, timestamp_col) as minute FROM data -- 1 minute buckets",
498            ],
499        }
500    }
501
502    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
503        if args.len() != 2 {
504            return Err(anyhow!(
505                "TIME_BUCKET expects exactly 2 arguments: bucket_size, timestamp"
506            ));
507        }
508
509        let bucket_size = match &args[0] {
510            DataValue::Integer(size) => *size,
511            DataValue::Float(size) => *size as i64,
512            _ => return Err(anyhow!("TIME_BUCKET bucket_size must be numeric")),
513        };
514
515        if bucket_size <= 0 {
516            return Err(anyhow!("TIME_BUCKET bucket_size must be positive"));
517        }
518
519        match &args[1] {
520            DataValue::Integer(timestamp) => {
521                let bucket = (timestamp / bucket_size) * bucket_size;
522                Ok(DataValue::Integer(bucket))
523            }
524            DataValue::Float(timestamp) => {
525                let ts = *timestamp as i64;
526                let bucket = (ts / bucket_size) * bucket_size;
527                Ok(DataValue::Integer(bucket))
528            }
529            DataValue::Null => Ok(DataValue::Null),
530            _ => Err(anyhow!("TIME_BUCKET timestamp must be numeric")),
531        }
532    }
533}
534
535/// DAYOFWEEK function - returns day of week as number (0=Sunday, 6=Saturday)
536pub struct DayOfWeekFunction;
537
538impl SqlFunction for DayOfWeekFunction {
539    fn signature(&self) -> FunctionSignature {
540        FunctionSignature {
541            name: "DAYOFWEEK",
542            category: FunctionCategory::Date,
543            arg_count: ArgCount::Fixed(1),
544            description: "Returns day of week as number (0=Sunday, 6=Saturday)",
545            returns: "INTEGER",
546            examples: vec![
547                "SELECT DAYOFWEEK('2024-01-01')", // Returns 1 (Monday)
548                "SELECT DAYOFWEEK(NOW())",
549                "SELECT DAYOFWEEK(date_column) FROM table",
550            ],
551        }
552    }
553
554    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
555        if args.len() != 1 {
556            return Err(anyhow!("DAYOFWEEK expects exactly 1 argument"));
557        }
558
559        let date_str = match &args[0] {
560            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
561            DataValue::InternedString(s) => s.as_str(),
562            DataValue::Null => return Ok(DataValue::Null),
563            _ => return Err(anyhow!("DAYOFWEEK expects a date/datetime string")),
564        };
565
566        let dt = parse_datetime(date_str)?;
567        // chrono weekday: Mon=0, Tue=1, ..., Sun=6
568        // We want: Sun=0, Mon=1, ..., Sat=6
569        let chrono_weekday = dt.weekday().num_days_from_monday(); // 0=Mon, 6=Sun
570        let our_weekday = (chrono_weekday + 1) % 7; // Convert to 0=Sun, 6=Sat
571
572        Ok(DataValue::Integer(our_weekday as i64))
573    }
574}
575
576/// DAYNAME function - returns full day name (Monday, Tuesday, etc.)
577pub struct DayNameFunction;
578
579impl SqlFunction for DayNameFunction {
580    fn signature(&self) -> FunctionSignature {
581        FunctionSignature {
582            name: "DAYNAME",
583            category: FunctionCategory::Date,
584            arg_count: ArgCount::Range(1, 2),
585            description: "Returns day name. Optional second arg: 'full' (default) or 'short'",
586            returns: "STRING",
587            examples: vec![
588                "SELECT DAYNAME('2024-01-01')",          // Returns 'Monday'
589                "SELECT DAYNAME('2024-01-01', 'short')", // Returns 'Mon'
590                "SELECT DAYNAME(NOW(), 'full')",
591            ],
592        }
593    }
594
595    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
596        if args.is_empty() || args.len() > 2 {
597            return Err(anyhow!("DAYNAME expects 1 or 2 arguments"));
598        }
599
600        let date_str = match &args[0] {
601            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
602            DataValue::InternedString(s) => s.as_str(),
603            DataValue::Null => return Ok(DataValue::Null),
604            _ => return Err(anyhow!("DAYNAME expects a date/datetime string")),
605        };
606
607        let format = if args.len() == 2 {
608            match &args[1] {
609                DataValue::String(s) => s.as_str(),
610                DataValue::InternedString(s) => s.as_str(),
611                DataValue::Null => "full",
612                _ => return Err(anyhow!("DAYNAME format must be 'full' or 'short'")),
613            }
614        } else {
615            "full"
616        };
617
618        let dt = parse_datetime(date_str)?;
619
620        let day_name = match format {
621            "short" => dt.format("%a").to_string(), // Mon, Tue, Wed, etc.
622            "full" | _ => dt.format("%A").to_string(), // Monday, Tuesday, etc.
623        };
624
625        Ok(DataValue::String(day_name))
626    }
627}
628
629/// ISLEAPYEAR function - returns true if the year is a leap year
630pub struct IsLeapYearFunction;
631
632impl SqlFunction for IsLeapYearFunction {
633    fn signature(&self) -> FunctionSignature {
634        FunctionSignature {
635            name: "ISLEAPYEAR",
636            category: FunctionCategory::Date,
637            arg_count: ArgCount::Fixed(1),
638            description: "Returns true if the year is a leap year",
639            returns: "BOOLEAN",
640            examples: vec![
641                "SELECT ISLEAPYEAR('2024-01-01')", // Returns true
642                "SELECT ISLEAPYEAR(2024)",         // Returns true
643                "SELECT ISLEAPYEAR(2023)",         // Returns false
644            ],
645        }
646    }
647
648    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
649        if args.len() != 1 {
650            return Err(anyhow!("ISLEAPYEAR expects exactly 1 argument"));
651        }
652
653        let year = match &args[0] {
654            DataValue::Integer(y) => *y as i32,
655            DataValue::Float(f) => *f as i32,
656            DataValue::String(s) | DataValue::DateTime(s) => {
657                // Try to parse as a year number first
658                if let Ok(y) = s.parse::<i32>() {
659                    y
660                } else {
661                    // Try to parse as a date
662                    let dt = parse_datetime(s.as_str())?;
663                    dt.year()
664                }
665            }
666            DataValue::InternedString(s) => {
667                // Try to parse as a year number first
668                if let Ok(y) = s.parse::<i32>() {
669                    y
670                } else {
671                    // Try to parse as a date
672                    let dt = parse_datetime(s.as_str())?;
673                    dt.year()
674                }
675            }
676            DataValue::Null => return Ok(DataValue::Null),
677            _ => return Err(anyhow!("ISLEAPYEAR expects a year number or date")),
678        };
679
680        // Leap year calculation
681        let is_leap = (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0);
682
683        Ok(DataValue::Boolean(is_leap))
684    }
685}
686
687/// WEEKOFYEAR function - returns the ISO week number (1-53)
688pub struct WeekOfYearFunction;
689
690impl SqlFunction for WeekOfYearFunction {
691    fn signature(&self) -> FunctionSignature {
692        FunctionSignature {
693            name: "WEEKOFYEAR",
694            category: FunctionCategory::Date,
695            arg_count: ArgCount::Fixed(1),
696            description: "Returns the ISO week number of the year (1-53)",
697            returns: "INTEGER",
698            examples: vec![
699                "SELECT WEEKOFYEAR('2024-01-01')", // Returns week number
700                "SELECT WEEKOFYEAR(NOW())",
701            ],
702        }
703    }
704
705    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
706        if args.len() != 1 {
707            return Err(anyhow!("WEEKOFYEAR expects exactly 1 argument"));
708        }
709
710        let date_str = match &args[0] {
711            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
712            DataValue::InternedString(s) => s.as_str(),
713            DataValue::Null => return Ok(DataValue::Null),
714            _ => return Err(anyhow!("WEEKOFYEAR expects a date/datetime string")),
715        };
716
717        let dt = parse_datetime(date_str)?;
718        let week = dt.iso_week().week();
719
720        Ok(DataValue::Integer(week as i64))
721    }
722}
723
724/// QUARTER function - returns the quarter of the year (1-4)
725pub struct QuarterFunction;
726
727impl SqlFunction for QuarterFunction {
728    fn signature(&self) -> FunctionSignature {
729        FunctionSignature {
730            name: "QUARTER",
731            category: FunctionCategory::Date,
732            arg_count: ArgCount::Fixed(1),
733            description: "Returns the quarter of the year (1-4)",
734            returns: "INTEGER",
735            examples: vec![
736                "SELECT QUARTER('2024-01-15')", // Returns 1
737                "SELECT QUARTER('2024-07-01')", // Returns 3
738            ],
739        }
740    }
741
742    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
743        if args.len() != 1 {
744            return Err(anyhow!("QUARTER expects exactly 1 argument"));
745        }
746
747        let date_str = match &args[0] {
748            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
749            DataValue::InternedString(s) => s.as_str(),
750            DataValue::Null => return Ok(DataValue::Null),
751            _ => return Err(anyhow!("QUARTER expects a date/datetime string")),
752        };
753
754        let dt = parse_datetime(date_str)?;
755        let month = dt.month();
756        let quarter = (month - 1) / 3 + 1;
757
758        Ok(DataValue::Integer(quarter as i64))
759    }
760}
761
762/// YEAR function - extracts year from date
763pub struct YearFunction;
764
765impl SqlFunction for YearFunction {
766    fn signature(&self) -> FunctionSignature {
767        FunctionSignature {
768            name: "YEAR",
769            category: FunctionCategory::Date,
770            arg_count: ArgCount::Fixed(1),
771            description: "Returns the year from a date",
772            returns: "INTEGER",
773            examples: vec![
774                "SELECT YEAR('2024-03-15')", // Returns 2024
775                "SELECT YEAR(NOW())",        // Returns current year
776            ],
777        }
778    }
779
780    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
781        if args.len() != 1 {
782            return Err(anyhow!("YEAR expects exactly 1 argument"));
783        }
784
785        let date_str = match &args[0] {
786            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
787            DataValue::InternedString(s) => s.as_str(),
788            DataValue::Null => return Ok(DataValue::Null),
789            _ => return Err(anyhow!("YEAR expects a date/datetime string")),
790        };
791
792        let dt = parse_datetime(date_str)?;
793        Ok(DataValue::Float(dt.year() as f64))
794    }
795}
796
797/// MONTH function - extracts month from date (1-12)
798pub struct MonthFunction;
799
800impl SqlFunction for MonthFunction {
801    fn signature(&self) -> FunctionSignature {
802        FunctionSignature {
803            name: "MONTH",
804            category: FunctionCategory::Date,
805            arg_count: ArgCount::Fixed(1),
806            description: "Returns the month from a date (1-12)",
807            returns: "INTEGER",
808            examples: vec![
809                "SELECT MONTH('2024-03-15')", // Returns 3
810                "SELECT MONTH(NOW())",        // Returns current month
811            ],
812        }
813    }
814
815    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
816        if args.len() != 1 {
817            return Err(anyhow!("MONTH expects exactly 1 argument"));
818        }
819
820        let date_str = match &args[0] {
821            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
822            DataValue::InternedString(s) => s.as_str(),
823            DataValue::Null => return Ok(DataValue::Null),
824            _ => return Err(anyhow!("MONTH expects a date/datetime string")),
825        };
826
827        let dt = parse_datetime(date_str)?;
828        Ok(DataValue::Float(dt.month() as f64))
829    }
830}
831
832/// DAY function - extracts day of month from date (1-31)
833pub struct DayFunction;
834
835impl SqlFunction for DayFunction {
836    fn signature(&self) -> FunctionSignature {
837        FunctionSignature {
838            name: "DAY",
839            category: FunctionCategory::Date,
840            arg_count: ArgCount::Fixed(1),
841            description: "Returns the day of month from a date (1-31)",
842            returns: "INTEGER",
843            examples: vec![
844                "SELECT DAY('2024-03-15')", // Returns 15
845                "SELECT DAY(NOW())",        // Returns current day
846            ],
847        }
848    }
849
850    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
851        if args.len() != 1 {
852            return Err(anyhow!("DAY expects exactly 1 argument"));
853        }
854
855        let date_str = match &args[0] {
856            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
857            DataValue::InternedString(s) => s.as_str(),
858            DataValue::Null => return Ok(DataValue::Null),
859            _ => return Err(anyhow!("DAY expects a date/datetime string")),
860        };
861
862        let dt = parse_datetime(date_str)?;
863        Ok(DataValue::Float(dt.day() as f64))
864    }
865}
866
867/// MONTHNAME function - returns the month name
868pub struct MonthNameFunction;
869
870impl SqlFunction for MonthNameFunction {
871    fn signature(&self) -> FunctionSignature {
872        FunctionSignature {
873            name: "MONTHNAME",
874            category: FunctionCategory::Date,
875            arg_count: ArgCount::Range(1, 2),
876            description: "Returns month name. Optional second arg: 'full' (default) or 'short'",
877            returns: "STRING",
878            examples: vec![
879                "SELECT MONTHNAME('2024-01-15')",          // Returns 'January'
880                "SELECT MONTHNAME('2024-01-15', 'short')", // Returns 'Jan'
881            ],
882        }
883    }
884
885    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
886        if args.is_empty() || args.len() > 2 {
887            return Err(anyhow!("MONTHNAME expects 1 or 2 arguments"));
888        }
889
890        let date_str = match &args[0] {
891            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
892            DataValue::InternedString(s) => s.as_str(),
893            DataValue::Null => return Ok(DataValue::Null),
894            _ => return Err(anyhow!("MONTHNAME expects a date/datetime string")),
895        };
896
897        let format = if args.len() == 2 {
898            match &args[1] {
899                DataValue::String(s) => s.as_str(),
900                DataValue::InternedString(s) => s.as_str(),
901                DataValue::Null => "full",
902                _ => return Err(anyhow!("MONTHNAME format must be 'full' or 'short'")),
903            }
904        } else {
905            "full"
906        };
907
908        let dt = parse_datetime(date_str)?;
909
910        let month_name = match format {
911            "short" => dt.format("%b").to_string(), // Jan, Feb, Mar, etc.
912            "full" | _ => dt.format("%B").to_string(), // January, February, etc.
913        };
914
915        Ok(DataValue::String(month_name))
916    }
917}
918
919/// Register all date/time functions
920pub fn register_date_time_functions(registry: &mut super::FunctionRegistry) {
921    registry.register(Box::new(NowFunction));
922    registry.register(Box::new(TodayFunction));
923    registry.register(Box::new(DateDiffFunction));
924    registry.register(Box::new(DateAddFunction));
925    registry.register(Box::new(UnixTimestamp));
926    registry.register(Box::new(FromUnixTime));
927    registry.register(Box::new(TimeBucket));
928
929    // Date extraction functions
930    registry.register(Box::new(YearFunction));
931    registry.register(Box::new(MonthFunction));
932    registry.register(Box::new(DayFunction));
933    registry.register(Box::new(DayOfWeekFunction));
934    registry.register(Box::new(DayNameFunction));
935    registry.register(Box::new(MonthNameFunction));
936
937    // Date utility functions
938    registry.register(Box::new(IsLeapYearFunction));
939    registry.register(Box::new(WeekOfYearFunction));
940    registry.register(Box::new(QuarterFunction));
941}
sql_cli/sql/functions/date_time.rs

sql_cli/sql/functions/
date_time.rs
