ddb_core/functions/
datetime.rs

1// Date/Time functions
2use super::Value;
3use crate::error::{DdbError, Result};
4use chrono::{Datelike, Local, NaiveDate, NaiveDateTime, NaiveTime, Timelike};
5
6/// NOW() / CURRENT_TIMESTAMP() - Current date and time
7pub fn now() -> Value {
8    Value::DateTime(Local::now().naive_local())
9}
10
11/// CURDATE() / CURRENT_DATE() - Current date
12pub fn curdate() -> Value {
13    Value::Date(Local::now().date_naive())
14}
15
16/// CURTIME() / CURRENT_TIME() - Current time
17pub fn curtime() -> Value {
18    Value::Time(Local::now().time())
19}
20
21/// DATE(datetime) - Extract date part
22pub fn date(value: &Value) -> Result<Value> {
23    match value {
24        Value::Date(d) => Ok(Value::Date(*d)),
25        Value::DateTime(dt) => Ok(Value::Date(dt.date())),
26        Value::String(s) => {
27            let dt = NaiveDate::parse_from_str(s, "%Y-%m-%d")
28                .or_else(|_| {
29                    NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
30                        .map(|dt| dt.date())
31                })
32                .map_err(|e| DdbError::FunctionError(format!("Invalid date: {}", e)))?;
33            Ok(Value::Date(dt))
34        }
35        _ => Err(DdbError::FunctionError(format!(
36            "Cannot extract date from {:?}",
37            value
38        ))),
39    }
40}
41
42/// TIME(datetime) - Extract time part
43pub fn time(value: &Value) -> Result<Value> {
44    match value {
45        Value::Time(t) => Ok(Value::Time(*t)),
46        Value::DateTime(dt) => Ok(Value::Time(dt.time())),
47        Value::String(s) => {
48            let t = NaiveTime::parse_from_str(s, "%H:%M:%S")
49                .or_else(|_| {
50                    NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
51                        .map(|dt| dt.time())
52                })
53                .map_err(|e| DdbError::FunctionError(format!("Invalid time: {}", e)))?;
54            Ok(Value::Time(t))
55        }
56        _ => Err(DdbError::FunctionError(format!(
57            "Cannot extract time from {:?}",
58            value
59        ))),
60    }
61}
62
63/// YEAR(date) - Extract year
64pub fn year(value: &Value) -> Result<Value> {
65    let date_val = date(value)?;
66    match date_val {
67        Value::Date(d) => Ok(Value::Integer(d.year() as i64)),
68        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
69    }
70}
71
72/// MONTH(date) - Extract month (1-12)
73pub fn month(value: &Value) -> Result<Value> {
74    let date_val = date(value)?;
75    match date_val {
76        Value::Date(d) => Ok(Value::Integer(d.month() as i64)),
77        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
78    }
79}
80
81/// DAY(date) / DAYOFMONTH(date) - Extract day of month (1-31)
82pub fn day(value: &Value) -> Result<Value> {
83    let date_val = date(value)?;
84    match date_val {
85        Value::Date(d) => Ok(Value::Integer(d.day() as i64)),
86        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
87    }
88}
89
90/// HOUR(time) - Extract hour (0-23)
91pub fn hour(value: &Value) -> Result<Value> {
92    let time_val = time(value)?;
93    match time_val {
94        Value::Time(t) => Ok(Value::Integer(t.hour() as i64)),
95        _ => Err(DdbError::FunctionError("Invalid time".to_string())),
96    }
97}
98
99/// MINUTE(time) - Extract minute (0-59)
100pub fn minute(value: &Value) -> Result<Value> {
101    let time_val = time(value)?;
102    match time_val {
103        Value::Time(t) => Ok(Value::Integer(t.minute() as i64)),
104        _ => Err(DdbError::FunctionError("Invalid time".to_string())),
105    }
106}
107
108/// SECOND(time) - Extract second (0-59)
109pub fn second(value: &Value) -> Result<Value> {
110    let time_val = time(value)?;
111    match time_val {
112        Value::Time(t) => Ok(Value::Integer(t.second() as i64)),
113        _ => Err(DdbError::FunctionError("Invalid time".to_string())),
114    }
115}
116
117/// DAYOFWEEK(date) - Day of week (1=Sunday, 7=Saturday)
118pub fn dayofweek(value: &Value) -> Result<Value> {
119    let date_val = date(value)?;
120    match date_val {
121        Value::Date(d) => {
122            let weekday = d.weekday().num_days_from_sunday() + 1;
123            Ok(Value::Integer(weekday as i64))
124        }
125        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
126    }
127}
128
129/// DAYNAME(date) - Name of day (Monday, Tuesday, etc.)
130pub fn dayname(value: &Value) -> Result<Value> {
131    let date_val = date(value)?;
132    match date_val {
133        Value::Date(d) => Ok(Value::String(d.format("%A").to_string())),
134        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
135    }
136}
137
138/// MONTHNAME(date) - Name of month (January, February, etc.)
139pub fn monthname(value: &Value) -> Result<Value> {
140    let date_val = date(value)?;
141    match date_val {
142        Value::Date(d) => Ok(Value::String(d.format("%B").to_string())),
143        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
144    }
145}
146
147/// DAYOFYEAR(date) - Day of year (1-366)
148pub fn dayofyear(value: &Value) -> Result<Value> {
149    let date_val = date(value)?;
150    match date_val {
151        Value::Date(d) => Ok(Value::Integer(d.ordinal() as i64)),
152        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
153    }
154}
155
156/// WEEK(date) - Week number of year (0-53)
157pub fn week(value: &Value) -> Result<Value> {
158    let date_val = date(value)?;
159    match date_val {
160        Value::Date(d) => Ok(Value::Integer(d.iso_week().week() as i64)),
161        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
162    }
163}
164
165/// QUARTER(date) - Quarter of year (1-4)
166pub fn quarter(value: &Value) -> Result<Value> {
167    let month_val = month(value)?;
168    match month_val {
169        Value::Integer(m) => Ok(Value::Integer(((m - 1) / 3) + 1)),
170        _ => Err(DdbError::FunctionError("Invalid date".to_string())),
171    }
172}
173
174/// DATE_FORMAT(date, format) - Format date/time
175pub fn date_format(value: &Value, format: &Value) -> Result<Value> {
176    let fmt = format.to_string();
177    let formatted = match value {
178        Value::Date(d) => d.format(&fmt).to_string(),
179        Value::DateTime(dt) => dt.format(&fmt).to_string(),
180        Value::Time(t) => t.format(&fmt).to_string(),
181        _ => {
182            let date_val = date(value)?;
183            match date_val {
184                Value::Date(d) => d.format(&fmt).to_string(),
185                _ => return Err(DdbError::FunctionError("Invalid date".to_string())),
186            }
187        }
188    };
189    Ok(Value::String(formatted))
190}
191
192/// UNIX_TIMESTAMP([date]) - Seconds since Unix epoch
193pub fn unix_timestamp(value: Option<&Value>) -> Result<Value> {
194    if let Some(v) = value {
195        match v {
196            Value::DateTime(dt) => Ok(Value::Integer(dt.and_utc().timestamp())),
197            _ => {
198                let date_val = date(v)?;
199                match date_val {
200                    Value::Date(d) => {
201                        let dt = d.and_hms_opt(0, 0, 0).unwrap();
202                        Ok(Value::Integer(dt.and_utc().timestamp()))
203                    }
204                    _ => Err(DdbError::FunctionError("Invalid date".to_string())),
205                }
206            }
207        }
208    } else {
209        Ok(Value::Integer(Local::now().timestamp()))
210    }
211}
212
213/// FROM_UNIXTIME(timestamp) - Convert Unix timestamp to datetime
214pub fn from_unixtime(value: &Value) -> Result<Value> {
215    let timestamp = value.as_i64()?;
216    let dt = chrono::DateTime::from_timestamp(timestamp, 0)
217        .ok_or_else(|| DdbError::FunctionError("Invalid timestamp".to_string()))?;
218    Ok(Value::DateTime(dt.naive_local()))
219}
220
221/// DATEDIFF(date1, date2) - Days between two dates
222pub fn datediff(date1: &Value, date2: &Value) -> Result<Value> {
223    let d1 = match date(date1)? {
224        Value::Date(d) => d,
225        _ => return Err(DdbError::FunctionError("Invalid date1".to_string())),
226    };
227
228    let d2 = match date(date2)? {
229        Value::Date(d) => d,
230        _ => return Err(DdbError::FunctionError("Invalid date2".to_string())),
231    };
232
233    let diff = d1.signed_duration_since(d2).num_days();
234    Ok(Value::Integer(diff))
235}
236
237/// DATEADD(date, interval, unit) / DATE_ADD(date, INTERVAL n unit)
238/// unit can be: 'day', 'month', 'year', 'hour', 'minute', 'second'
239pub fn dateadd(date_val: &Value, interval: &Value, unit: &str) -> Result<Value> {
240    let n = interval.as_i64()?;
241
242    match date_val {
243        Value::Date(d) => {
244            let new_date = match unit.to_lowercase().as_str() {
245                "day" | "days" => *d + chrono::Duration::days(n),
246                "week" | "weeks" => *d + chrono::Duration::weeks(n),
247                "month" | "months" => {
248                    if n >= 0 {
249                        d.checked_add_months(chrono::Months::new(n as u32))
250                    } else {
251                        d.checked_sub_months(chrono::Months::new((-n) as u32))
252                    }
253                    .ok_or_else(|| DdbError::FunctionError("Date out of range".to_string()))?
254                }
255                "year" | "years" => {
256                    if n >= 0 {
257                        d.checked_add_months(chrono::Months::new((n * 12) as u32))
258                    } else {
259                        d.checked_sub_months(chrono::Months::new(((-n) * 12) as u32))
260                    }
261                    .ok_or_else(|| DdbError::FunctionError("Date out of range".to_string()))?
262                }
263                _ => return Err(DdbError::FunctionError(format!("Invalid unit for date: {}", unit))),
264            };
265            Ok(Value::Date(new_date))
266        }
267        Value::DateTime(dt) => {
268            let new_dt = match unit.to_lowercase().as_str() {
269                "day" | "days" => *dt + chrono::Duration::days(n),
270                "week" | "weeks" => *dt + chrono::Duration::weeks(n),
271                "hour" | "hours" => *dt + chrono::Duration::hours(n),
272                "minute" | "minutes" => *dt + chrono::Duration::minutes(n),
273                "second" | "seconds" => *dt + chrono::Duration::seconds(n),
274                "month" | "months" => {
275                    let date = dt.date();
276                    let new_date = if n >= 0 {
277                        date.checked_add_months(chrono::Months::new(n as u32))
278                    } else {
279                        date.checked_sub_months(chrono::Months::new((-n) as u32))
280                    }
281                    .ok_or_else(|| DdbError::FunctionError("Date out of range".to_string()))?;
282                    new_date.and_time(dt.time())
283                }
284                "year" | "years" => {
285                    let date = dt.date();
286                    let new_date = if n >= 0 {
287                        date.checked_add_months(chrono::Months::new((n * 12) as u32))
288                    } else {
289                        date.checked_sub_months(chrono::Months::new(((-n) * 12) as u32))
290                    }
291                    .ok_or_else(|| DdbError::FunctionError("Date out of range".to_string()))?;
292                    new_date.and_time(dt.time())
293                }
294                _ => return Err(DdbError::FunctionError(format!("Invalid unit: {}", unit))),
295            };
296            Ok(Value::DateTime(new_dt))
297        }
298        _ => {
299            // Try to parse as date first
300            let d = date(date_val)?;
301            dateadd(&d, interval, unit)
302        }
303    }
304}
305
306/// DATESUB(date, interval, unit) / DATE_SUB(date, INTERVAL n unit)
307pub fn datesub(date_val: &Value, interval: &Value, unit: &str) -> Result<Value> {
308    let n = interval.as_i64()?;
309    dateadd(date_val, &Value::Integer(-n), unit)
310}
311
312/// TIMESTAMPDIFF(unit, datetime1, datetime2) - Difference in specified units
313pub fn timestampdiff(unit: &str, datetime1: &Value, datetime2: &Value) -> Result<Value> {
314    let dt1 = match date(datetime1)? {
315        Value::Date(d) => d.and_hms_opt(0, 0, 0).unwrap(),
316        _ => match datetime1 {
317            Value::DateTime(dt) => *dt,
318            _ => return Err(DdbError::FunctionError("Invalid datetime1".to_string())),
319        }
320    };
321
322    let dt2 = match date(datetime2)? {
323        Value::Date(d) => d.and_hms_opt(0, 0, 0).unwrap(),
324        _ => match datetime2 {
325            Value::DateTime(dt) => *dt,
326            _ => return Err(DdbError::FunctionError("Invalid datetime2".to_string())),
327        }
328    };
329
330    let diff = dt2.signed_duration_since(dt1);
331
332    let result = match unit.to_lowercase().as_str() {
333        "second" | "seconds" => diff.num_seconds(),
334        "minute" | "minutes" => diff.num_minutes(),
335        "hour" | "hours" => diff.num_hours(),
336        "day" | "days" => diff.num_days(),
337        "week" | "weeks" => diff.num_weeks(),
338        "month" | "months" => {
339            let years = (dt2.year() - dt1.year()) as i64;
340            let months = (dt2.month() as i32 - dt1.month() as i32) as i64;
341            years * 12 + months
342        }
343        "year" | "years" => (dt2.year() - dt1.year()) as i64,
344        _ => return Err(DdbError::FunctionError(format!("Invalid unit: {}", unit))),
345    };
346
347    Ok(Value::Integer(result))
348}
349
350/// AGE(date1, [date2]) - Age between dates or from date to now
351pub fn age(date1: &Value, date2: Option<&Value>) -> Result<Value> {
352    let d1 = match date(date1)? {
353        Value::Date(d) => d,
354        _ => return Err(DdbError::FunctionError("Invalid date".to_string())),
355    };
356
357    let d2 = if let Some(v) = date2 {
358        match date(v)? {
359            Value::Date(d) => d,
360            _ => return Err(DdbError::FunctionError("Invalid date".to_string())),
361        }
362    } else {
363        Local::now().date_naive()
364    };
365
366    let years = d2.year() - d1.year();
367    Ok(Value::Integer(years as i64))
368}
369
370#[cfg(test)]
371mod tests {
372    use super::*;
373
374    #[test]
375    fn test_date_extraction() {
376        let dt = Value::String("2024-03-15".to_string());
377        assert_eq!(year(&dt).unwrap(), Value::Integer(2024));
378        assert_eq!(month(&dt).unwrap(), Value::Integer(3));
379        assert_eq!(day(&dt).unwrap(), Value::Integer(15));
380    }
381
382    #[test]
383    fn test_time_extraction() {
384        let t = Value::String("14:30:45".to_string());
385        let time_val = time(&t).unwrap();
386        assert_eq!(hour(&time_val).unwrap(), Value::Integer(14));
387        assert_eq!(minute(&time_val).unwrap(), Value::Integer(30));
388        assert_eq!(second(&time_val).unwrap(), Value::Integer(45));
389    }
390
391    #[test]
392    fn test_now() {
393        // Just make sure it doesn't panic
394        let _ = now();
395        let _ = curdate();
396        let _ = curtime();
397    }
398}
ddb_core/functions/datetime.rs

ddb_core/functions/
datetime.rs
