aljaz/turso
1
0
Fork 0
mirror of https://github.com/aljazceru/turso.git synced 2025-12-17 08:34:19 +01:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
turso/core/functions/datetime.rs
pedrocarlo e1d36a2221 clippy fix
2025-11-11 16:11:46 -03:00

1907 lines
68 KiB
Rust
Raw Permalink Blame History

use crate::types::AsValueRef;
use crate::types::Value;
use crate::LimboError::InvalidModifier;
use crate::{Result, ValueRef};
use chrono::{
DateTime, Datelike, NaiveDate, NaiveDateTime, NaiveTime, TimeDelta, TimeZone, Timelike, Utc,
};
/// Execution of date/time/datetime functions
#[inline(always)]
pub fn exec_date<I, E, V>(values: I) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
exec_datetime(values, DateTimeOutput::Date)
}
#[inline(always)]
pub fn exec_time<I, E, V>(values: I) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
exec_datetime(values, DateTimeOutput::Time)
}
#[inline(always)]
pub fn exec_datetime_full<I, E, V>(values: I) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
exec_datetime(values, DateTimeOutput::DateTime)
}
#[inline(always)]
pub fn exec_strftime<I, E, V>(values: I) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
let mut values = values.into_iter();
if values.len() == 0 {
return Value::Null;
}
let value = values.next().unwrap();
let value = value.as_value_ref();
let format_str = if matches!(
value,
ValueRef::Text(_) | ValueRef::Integer(_) | ValueRef::Float(_)
) {
format!("{value}")
} else {
return Value::Null;
};
exec_datetime(values, DateTimeOutput::StrfTime(format_str))
}
enum DateTimeOutput {
Date,
Time,
DateTime,
// Holds the format string
StrfTime(String),
JuliaDay,
}
fn exec_datetime<I, E, V>(values: I, output_type: DateTimeOutput) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
let values = values.into_iter();
if values.len() == 0 {
let now = parse_naive_date_time(Value::build_text("now")).unwrap();
return format_dt(now, output_type, false);
}
let mut values = values.peekable();
let first = values.peek().unwrap();
if let Some(mut dt) = parse_naive_date_time(first) {
// if successful, treat subsequent entries as modifiers
modify_dt(&mut dt, values.skip(1), output_type)
} else {
// if the first argument is NOT a valid date/time, treat the entire set of values as modifiers.
let mut dt = chrono::Local::now().to_utc().naive_utc();
modify_dt(&mut dt, values, output_type)
}
}
fn modify_dt<I, E, V>(dt: &mut NaiveDateTime, mods: I, output_type: DateTimeOutput) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
let mods = mods.into_iter();
let mut n_floor: i64 = 0;
let mut subsec_requested = false;
for modifier in mods {
if let ValueRef::Text(ref text_rc) = modifier.as_value_ref() {
// TODO: to prevent double conversion and properly support 'utc'/'localtime', we also
// need to keep track of the current timezone and apply it to the modifier.
let parsed = parse_modifier(text_rc.as_str());
if !matches!(parsed, Ok(Modifier::Floor) | Ok(Modifier::Ceiling)) {
n_floor = 0;
}
match apply_modifier(dt, text_rc.as_str(), &mut n_floor) {
Ok(true) => subsec_requested = true,
Ok(false) => {}
Err(_) => return Value::build_text(""),
}
if matches!(parsed, Ok(Modifier::Floor) | Ok(Modifier::Ceiling)) {
n_floor = 0;
}
} else {
return Value::build_text("");
}
}
if is_leap_second(dt) || *dt > get_max_datetime_exclusive() {
return Value::build_text("");
}
format_dt(*dt, output_type, subsec_requested)
}
fn format_dt(dt: NaiveDateTime, output_type: DateTimeOutput, subsec: bool) -> Value {
match output_type {
DateTimeOutput::Date => Value::from_text(dt.format("%Y-%m-%d").to_string()),
DateTimeOutput::Time => {
let t = if subsec {
dt.format("%H:%M:%S%.3f").to_string()
} else {
dt.format("%H:%M:%S").to_string()
};
Value::from_text(t)
}
DateTimeOutput::DateTime => {
let t = if subsec && dt.nanosecond() != 0 {
dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string()
} else {
dt.format("%Y-%m-%d %H:%M:%S").to_string()
};
Value::from_text(t)
}
DateTimeOutput::StrfTime(format_str) => Value::from_text(strftime_format(&dt, &format_str)),
DateTimeOutput::JuliaDay => Value::Float(to_julian_day_exact(&dt)),
}
}
// Not as fast as if the formatting was native to chrono, but a good enough
// for now, just to have the feature implemented
fn strftime_format(dt: &NaiveDateTime, format_str: &str) -> String {
use crate::functions::strftime::CustomStrftimeItems;
use std::fmt::Write;
// Necessary to remove %f and %J that are exclusive formatters to sqlite
// Chrono does not support them, so it is necessary to replace the modifiers manually
// Sqlite uses 9 decimal places for julianday in strftime
let copy_format = format_str
.to_string()
.replace("%J", &format!("{:.9}", to_julian_day_exact(dt)));
let items = CustomStrftimeItems::new(&copy_format);
// The write! macro is used here as chrono's format can panic if the formatting string contains
// unknown specifiers. By using a writer, we can catch the panic and handle the error
let mut formatted = String::new();
match write!(formatted, "{}", dt.format_with_items(items)) {
Ok(_) => formatted,
// On sqlite when the formatting fails nothing is printed
Err(_) => "".to_string(),
}
}
fn apply_modifier(dt: &mut NaiveDateTime, modifier: &str, n_floor: &mut i64) -> Result<bool> {
let parsed_modifier = parse_modifier(modifier)?;
match parsed_modifier {
Modifier::Days(days) => *dt += TimeDelta::days(days),
Modifier::Hours(hours) => *dt += TimeDelta::hours(hours),
Modifier::Minutes(minutes) => *dt += TimeDelta::minutes(minutes),
Modifier::Seconds(seconds) => *dt += TimeDelta::seconds(seconds),
Modifier::Months(m) => {
// Convert months to years + leftover months
let years = m / 12;
let leftover = m % 12;
add_years_and_months(dt, years, leftover, n_floor)?;
}
Modifier::Years(y) => {
add_years_and_months(dt, y, 0, n_floor)?;
}
Modifier::TimeOffset(offset) => *dt += offset,
Modifier::DateOffset {
years,
months,
days,
} => {
add_years_and_months(dt, years, months, n_floor)?;
*dt += TimeDelta::days(days as i64);
}
Modifier::DateTimeOffset {
years,
months,
days,
seconds,
} => {
add_years_and_months(dt, years, months, n_floor)?;
*dt += chrono::Duration::days(days as i64);
*dt += chrono::Duration::seconds(seconds.into());
}
Modifier::Floor => {
if *n_floor <= 0 {
return Ok(false);
}
*dt -= TimeDelta::days(*n_floor);
}
Modifier::Ceiling => {
*n_floor = 0;
}
Modifier::StartOfMonth => {
*dt = NaiveDate::from_ymd_opt(dt.year(), dt.month(), 1)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap();
}
Modifier::StartOfYear => {
*dt = NaiveDate::from_ymd_opt(dt.year(), 1, 1)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap();
}
Modifier::StartOfDay => {
*dt = dt.date().and_hms_opt(0, 0, 0).unwrap();
}
Modifier::Weekday(day) => {
let current_day = dt.weekday().num_days_from_sunday();
let target_day = day;
let days_to_add = (target_day + 7 - current_day) % 7;
*dt += TimeDelta::days(days_to_add as i64);
}
Modifier::Auto => todo!(), // Will require storing info about the original arg passed when
Modifier::UnixEpoch => todo!(), // applying modifiers. All numbers passed to date/time/dt are
Modifier::JulianDay => todo!(), // assumed to be julianday, so adding these now is redundant
Modifier::Localtime => {
let utc_dt = DateTime::<Utc>::from_naive_utc_and_offset(*dt, Utc);
*dt = utc_dt.with_timezone(&chrono::Local).naive_local();
}
Modifier::Utc => {
// TODO: handle datetime('now', 'utc') no-op
let local_dt = chrono::Local.from_local_datetime(dt).unwrap();
*dt = local_dt.with_timezone(&Utc).naive_utc();
}
Modifier::Subsec => {
*dt = dt.with_nanosecond(dt.nanosecond()).unwrap();
return Ok(true);
}
}
Ok(false)
}
fn is_julian_day_value(value: f64) -> bool {
(0.0..5373484.5).contains(&value)
}
fn add_years_and_months(
dt: &mut NaiveDateTime,
years: i32,
months: i32,
n_floor: &mut i64,
) -> Result<()> {
add_whole_years(dt, years, n_floor)?;
add_months_in_increments(dt, months, n_floor)?;
Ok(())
}
fn add_whole_years(dt: &mut NaiveDateTime, years: i32, n_floor: &mut i64) -> Result<()> {
if years == 0 {
return Ok(());
}
let target_year = dt.year() + years;
let (m, d, hh, mm, ss) = (dt.month(), dt.day(), dt.hour(), dt.minute(), dt.second());
// attempt same (month, day) in new year
if let Some(date) = NaiveDate::from_ymd_opt(target_year, m, d) {
*dt = date
.and_hms_opt(hh, mm, ss)
.ok_or_else(|| InvalidModifier("Invalid datetime format".to_string()))?;
return Ok(());
}
// if invalid: compute overflow days
let last_day_in_feb = last_day_in_month(target_year, m);
if d > last_day_in_feb {
// leftover = d - last_day_in_feb
let leftover = d - last_day_in_feb;
// base date is last_day_in_feb
let base_date = NaiveDate::from_ymd_opt(target_year, m, last_day_in_feb)
.ok_or_else(|| InvalidModifier("Invalid datetime format".to_string()))?
.and_hms_opt(hh, mm, ss)
.ok_or_else(|| InvalidModifier("Invalid time format".to_string()))?;
*dt = base_date + chrono::Duration::days(leftover as i64);
*n_floor += leftover as i64;
} else {
// do we fall back here?
}
Ok(())
}
fn add_months_in_increments(dt: &mut NaiveDateTime, months: i32, n_floor: &mut i64) -> Result<()> {
let step = if months >= 0 { 1 } else { -1 };
for _ in 0..months.abs() {
add_one_month(dt, step, n_floor)?;
}
Ok(())
}
// sqlite resolves any ambiguity between advancing months by using the 'ceiling'
// value, computing overflow days and advancing to the next valid date
// e.g. 2024-01-31 + 1 month = 2024-03-02
//
// the modifiers 'ceiling' and 'floor' will determine behavior, so we'll need to eagerly
// evaluate modifiers in the future to support those, and 'julianday'/'unixepoch'
fn add_one_month(dt: &mut NaiveDateTime, step: i32, n_floor: &mut i64) -> Result<()> {
let (y0, m0, d0) = (dt.year(), dt.month(), dt.day());
let (hh, mm, ss) = (dt.hour(), dt.minute(), dt.second());
let mut new_year = y0;
let mut new_month = m0 as i32 + step;
if new_month > 12 {
new_month -= 12;
new_year += 1;
} else if new_month < 1 {
new_month += 12;
new_year -= 1;
}
let last_day = last_day_in_month(new_year, new_month as u32);
if d0 <= last_day {
// valid date
*dt = NaiveDate::from_ymd_opt(new_year, new_month as u32, d0)
.ok_or_else(|| InvalidModifier("Invalid Auto format".to_string()))?
.and_hms_opt(hh, mm, ss)
.ok_or_else(|| InvalidModifier("Invalid Auto format".to_string()))?;
} else {
let leftover = d0 - last_day;
let base_date = NaiveDate::from_ymd_opt(new_year, new_month as u32, last_day)
.ok_or_else(|| InvalidModifier("Invalid Auto format".to_string()))?
.and_hms_opt(hh, mm, ss)
.ok_or_else(|| InvalidModifier("Invalid Auto format".to_string()))?;
*dt = base_date + chrono::Duration::days(leftover as i64);
*n_floor += leftover as i64;
}
Ok(())
}
#[inline(always)]
fn last_day_in_month(year: i32, month: u32) -> u32 {
for day in (28..=31).rev() {
if NaiveDate::from_ymd_opt(year, month, day).is_some() {
return day;
}
}
28
}
pub fn exec_julianday<I, E, V>(values: I) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
exec_datetime(values, DateTimeOutput::JuliaDay)
}
fn to_julian_day_exact(dt: &NaiveDateTime) -> f64 {
// SQLite's computeJD algorithm
let mut y = dt.year();
let mut m = dt.month() as i32;
let d = dt.day() as i32;
if m <= 2 {
y -= 1;
m += 12;
}
let a = (y + 4800) / 100;
let b = 38 - a + (a / 4);
let x1 = 36525 * (y + 4716) / 100;
let x2 = 306001 * (m + 1) / 10000;
// iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5) * 86400000)
let jd_days = (x1 + x2 + d + b) as f64 - 1524.5;
let mut i_jd = (jd_days * 86400000.0) as i64;
// Add time component in milliseconds
// iJD += h*3600000 + m*60000 + (sqlite3_int64)(s*1000 + 0.5)
let h_ms = dt.hour() as i64 * 3600000;
let m_ms = dt.minute() as i64 * 60000;
let s_ms = (dt.second() as f64 * 1000.0 + dt.nanosecond() as f64 / 1_000_000.0 + 0.5) as i64;
i_jd += h_ms + m_ms + s_ms;
// Convert back to floating point JD
i_jd as f64 / 86400000.0
}
pub fn exec_unixepoch(time_value: &Value) -> Result<Value> {
let dt = parse_naive_date_time(time_value);
match dt {
Some(dt) if !is_leap_second(&dt) => {
Ok(Value::Integer(get_unixepoch_from_naive_datetime(dt)))
}
_ => Ok(Value::Null),
}
}
fn get_unixepoch_from_naive_datetime(value: NaiveDateTime) -> i64 {
if is_leap_second(&value) {
return 0;
}
value.and_utc().timestamp()
}
fn parse_naive_date_time(time_value: impl AsValueRef) -> Option<NaiveDateTime> {
let time_value = time_value.as_value_ref();
match time_value {
ValueRef::Text(s) => get_date_time_from_time_value_string(s.as_str()),
ValueRef::Integer(i) => get_date_time_from_time_value_integer(i),
ValueRef::Float(f) => get_date_time_from_time_value_float(f),
_ => None,
}
}
fn get_date_time_from_time_value_string(value: &str) -> Option<NaiveDateTime> {
// Time-value formats:
// 1-7. YYYY-MM-DD[THH:MM[:SS[.SSS]]]
// 8-10. HH:MM[:SS[.SSS]]
// 11. 'now'
// 12. DDDDDDDDDD (Julian day number as integer or float)
//
// Ref: https://sqlite.org/lang_datefunc.html#tmval
// Check for 'now'
if value.trim().eq_ignore_ascii_case("now") {
return Some(chrono::Local::now().to_utc().naive_utc());
}
// Check for Julian day number (integer or float)
if let Ok(julian_day) = value.parse::<f64>() {
return get_date_time_from_time_value_float(julian_day);
}
// Attempt to parse with various formats
let date_only_format = "%Y-%m-%d";
let datetime_formats: [&str; 9] = [
"%Y-%m-%d %H:%M",
"%Y-%m-%d %H:%M:%S",
"%Y-%m-%d %H:%M:%S%.f",
"%Y-%m-%dT%H:%M",
"%Y-%m-%dT%H:%M:%S",
"%Y-%m-%dT%H:%M:%S%.f",
"%H:%M",
"%H:%M:%S",
"%H:%M:%S%.f",
];
// First, try to parse as date-only format
if let Ok(date) = NaiveDate::parse_from_str(value, date_only_format) {
return Some(date.and_time(NaiveTime::from_hms_opt(0, 0, 0).unwrap()));
}
for format in &datetime_formats {
if let Some(dt) = if format.starts_with("%H") {
// For time-only formats, assume date 2000-01-01
// Ref: https://sqlite.org/lang_datefunc.html#tmval
parse_datetime_with_optional_tz(
&format!("2000-01-01 {value}"),
&format!("%Y-%m-%d {format}"),
)
} else {
parse_datetime_with_optional_tz(value, format)
} {
return Some(dt);
}
}
None
}
fn parse_datetime_with_optional_tz(value: &str, format: &str) -> Option<NaiveDateTime> {
// Try parsing with timezone
let with_tz_format = format.to_owned() + "%:z";
if let Ok(dt) = DateTime::parse_from_str(value, &with_tz_format) {
return Some(dt.with_timezone(&Utc).naive_utc());
}
let mut value_without_tz = value;
if value.ends_with('Z') {
value_without_tz = &value[0..value.len() - 1];
}
// Parse without timezone
if let Ok(dt) = NaiveDateTime::parse_from_str(value_without_tz, format) {
return Some(dt);
}
None
}
fn get_date_time_from_time_value_integer(value: i64) -> Option<NaiveDateTime> {
i32::try_from(value).map_or_else(
|_| None,
|value| {
if value.is_negative() || !is_julian_day_value(value as f64) {
return None;
}
get_date_time_from_time_value_float(value as f64)
},
)
}
fn get_date_time_from_time_value_float(value: f64) -> Option<NaiveDateTime> {
if value.is_infinite() || value.is_nan() || !is_julian_day_value(value) {
return None;
}
julian_day_to_datetime(value).ok()
}
/// Convert a Julian Day number (as f64) to a NaiveDateTime
/// This uses SQLite's algorithm which converts to integer milliseconds first
/// to preserve precision, then converts back to date/time components.
fn julian_day_to_datetime(jd: f64) -> Result<NaiveDateTime> {
// SQLite approach: Convert JD to integer milliseconds
// iJD = (sqlite3_int64)(jd * 86400000.0 + 0.5)
let i_jd = (jd * 86400000.0 + 0.5) as i64;
// Compute the date (Year, Month, Day) from iJD
// Z = (int)((iJD + 43200000)/86400000)
let z = ((i_jd + 43200000) / 86400000) as i32;
// SQLite's algorithm from computeYMD
let alpha = ((z as f64 + 32044.75) / 36524.25) as i32 - 52;
let a = z + 1 + alpha - ((alpha + 100) / 4) + 25;
let b = a + 1524;
let c = ((b as f64 - 122.1) / 365.25) as i32;
let d = (36525 * (c & 32767)) / 100;
let e = ((b - d) as f64 / 30.6001) as i32;
let x1 = (30.6001 * e as f64) as i32;
let day = (b - d - x1) as u32;
let month = if e < 14 { e - 1 } else { e - 13 } as u32;
let year = if month > 2 { c - 4716 } else { c - 4715 };
// Compute the time (Hour, Minute, Second) from iJD
// day_ms = (int)((iJD + 43200000) % 86400000)
let day_ms = ((i_jd + 43200000) % 86400000) as i32;
// s = (day_ms % 60000) / 1000.0
let s_millis = day_ms % 60000;
let seconds = (s_millis / 1000) as u32;
let millis = (s_millis % 1000) as u32;
// day_min = day_ms / 60000
let day_min = day_ms / 60000;
let minutes = (day_min % 60) as u32;
let hours = (day_min / 60) as u32;
// Create the date
let date = NaiveDate::from_ymd_opt(year, month, day)
.ok_or_else(|| crate::LimboError::InternalError("Invalid date".to_string()))?;
// Create time with millisecond precision converted to nanoseconds
let nanos = millis * 1_000_000;
let time = NaiveTime::from_hms_nano_opt(hours, minutes, seconds, nanos)
.ok_or_else(|| crate::LimboError::InternalError("Invalid time".to_string()))?;
Ok(NaiveDateTime::new(date, time))
}
fn is_leap_second(dt: &NaiveDateTime) -> bool {
// The range from 1,000,000,000 to 1,999,999,999 represents the leap second.
dt.second() == 59 && dt.nanosecond() > 999_999_999
}
fn get_max_datetime_exclusive() -> NaiveDateTime {
// The maximum date in SQLite is 9999-12-31
NaiveDateTime::new(
NaiveDate::from_ymd_opt(10000, 1, 1).unwrap(),
NaiveTime::from_hms_milli_opt(00, 00, 00, 000).unwrap(),
)
}
/// Modifier doc https://www.sqlite.org/lang_datefunc.html#modifiers
#[allow(dead_code)]
#[derive(Debug, PartialEq)]
enum Modifier {
Days(i64),
Hours(i64),
Minutes(i64),
Seconds(i64),
Months(i32),
Years(i32),
TimeOffset(TimeDelta),
DateOffset {
years: i32,
months: i32,
days: i32,
},
DateTimeOffset {
years: i32,
months: i32,
days: i32,
seconds: i32,
},
Ceiling,
Floor,
StartOfMonth,
StartOfYear,
StartOfDay,
Weekday(u32),
UnixEpoch,
JulianDay,
Auto,
Localtime,
Utc,
Subsec,
}
fn parse_modifier_number(s: &str) -> Result<i64> {
s.trim()
.parse::<i64>()
.map_err(|_| InvalidModifier(format!("Invalid number: {s}")))
}
/// supports YYYY-MM-DD format for time shift modifiers
fn parse_modifier_date(s: &str) -> Result<NaiveDate> {
NaiveDate::parse_from_str(s, "%Y-%m-%d")
.map_err(|_| InvalidModifier("Invalid date format".to_string()))
}
/// supports following formats for time shift modifiers
/// - HH:MM
/// - HH:MM:SS
/// - HH:MM:SS.SSS
fn parse_modifier_time(s: &str) -> Result<NaiveTime> {
match s.len() {
5 => NaiveTime::parse_from_str(s, "%H:%M"),
8 => NaiveTime::parse_from_str(s, "%H:%M:%S"),
12 => NaiveTime::parse_from_str(s, "%H:%M:%S.%3f"),
_ => return Err(InvalidModifier(format!("Invalid time format: {s}"))),
}
.map_err(|_| InvalidModifier(format!("Invalid time format: {s}")))
}
fn parse_modifier(modifier: &str) -> Result<Modifier> {
// Small helpers to check string suffix/prefix in a case-insensitive way with no allocation
fn ends_with_ignore_ascii_case(s: &str, suffix: &str) -> bool {
s.len() >= suffix.len() && s[s.len() - suffix.len()..].eq_ignore_ascii_case(suffix)
}
fn starts_with_ignore_ascii_case(s: &str, prefix: &str) -> bool {
s.len() >= prefix.len() && s[..prefix.len()].eq_ignore_ascii_case(prefix)
}
let modifier = modifier.trim();
// We intentionally avoid usage of match_ignore_ascii_case! macro here because it lead to enormous amount of LLVM code which signifnicantly increase compilation time (see https://github.com/tursodatabase/turso/pull/3929)
// Fast path for exact matches
if modifier.eq_ignore_ascii_case("ceiling") {
return Ok(Modifier::Ceiling);
} else if modifier.eq_ignore_ascii_case("floor") {
return Ok(Modifier::Floor);
} else if modifier.eq_ignore_ascii_case("start of month") {
return Ok(Modifier::StartOfMonth);
} else if modifier.eq_ignore_ascii_case("start of year") {
return Ok(Modifier::StartOfYear);
} else if modifier.eq_ignore_ascii_case("start of day") {
return Ok(Modifier::StartOfDay);
} else if modifier.eq_ignore_ascii_case("unixepoch") {
return Ok(Modifier::UnixEpoch);
} else if modifier.eq_ignore_ascii_case("julianday") {
return Ok(Modifier::JulianDay);
} else if modifier.eq_ignore_ascii_case("auto") {
return Ok(Modifier::Auto);
} else if modifier.eq_ignore_ascii_case("localtime") {
return Ok(Modifier::Localtime);
} else if modifier.eq_ignore_ascii_case("utc") {
return Ok(Modifier::Utc);
} else if modifier.eq_ignore_ascii_case("subsec") || modifier.eq_ignore_ascii_case("subsecond")
{
return Ok(Modifier::Subsec);
}
// Patterns
if starts_with_ignore_ascii_case(modifier, "weekday ") {
let s = &modifier[8..];
let day = parse_modifier_number(s)?;
if !(0..=6).contains(&day) {
Err(InvalidModifier(
"Weekday must be between 0 and 6".to_string(),
))
} else {
Ok(Modifier::Weekday(day as u32))
}
} else if ends_with_ignore_ascii_case(modifier, " day") {
Ok(Modifier::Days(parse_modifier_number(
&modifier[..modifier.len() - 4],
)?))
} else if ends_with_ignore_ascii_case(modifier, " days") {
Ok(Modifier::Days(parse_modifier_number(
&modifier[..modifier.len() - 5],
)?))
} else if ends_with_ignore_ascii_case(modifier, " hour") {
Ok(Modifier::Hours(parse_modifier_number(
&modifier[..modifier.len() - 5],
)?))
} else if ends_with_ignore_ascii_case(modifier, " hours") {
Ok(Modifier::Hours(parse_modifier_number(
&modifier[..modifier.len() - 6],
)?))
} else if ends_with_ignore_ascii_case(modifier, " minute") {
Ok(Modifier::Minutes(parse_modifier_number(
&modifier[..modifier.len() - 7],
)?))
} else if ends_with_ignore_ascii_case(modifier, " minutes") {
Ok(Modifier::Minutes(parse_modifier_number(
&modifier[..modifier.len() - 8],
)?))
} else if ends_with_ignore_ascii_case(modifier, " second") {
Ok(Modifier::Seconds(parse_modifier_number(
&modifier[..modifier.len() - 7],
)?))
} else if ends_with_ignore_ascii_case(modifier, " seconds") {
Ok(Modifier::Seconds(parse_modifier_number(
&modifier[..modifier.len() - 8],
)?))
} else if ends_with_ignore_ascii_case(modifier, " month") {
Ok(Modifier::Months(
parse_modifier_number(&modifier[..modifier.len() - 6])? as i32,
))
} else if ends_with_ignore_ascii_case(modifier, " months") {
Ok(Modifier::Months(
parse_modifier_number(&modifier[..modifier.len() - 7])? as i32,
))
} else if ends_with_ignore_ascii_case(modifier, " year") {
Ok(Modifier::Years(
parse_modifier_number(&modifier[..modifier.len() - 5])? as i32,
))
} else if ends_with_ignore_ascii_case(modifier, " years") {
Ok(Modifier::Years(
parse_modifier_number(&modifier[..modifier.len() - 6])? as i32,
))
} else if starts_with_ignore_ascii_case(modifier, "+")
|| starts_with_ignore_ascii_case(modifier, "-")
{
let sign = if starts_with_ignore_ascii_case(modifier, "-") {
-1
} else {
1
};
let rest = &modifier[1..];
let parts: Vec<&str> = rest.split(' ').collect();
let digits_in_date = 10;
match parts.len() {
1 => {
if parts[0].len() == digits_in_date {
let date = parse_modifier_date(parts[0])?;
Ok(Modifier::DateOffset {
years: sign * date.year(),
months: sign * date.month() as i32,
days: sign * date.day() as i32,
})
} else {
// time values are either 12, 8 or 5 digits
let time = parse_modifier_time(parts[0])?;
let time_delta = sign * (time.num_seconds_from_midnight() as i32);
Ok(Modifier::TimeOffset(TimeDelta::seconds(time_delta.into())))
}
}
2 => {
let date = parse_modifier_date(parts[0])?;
let time = parse_modifier_time(parts[1])?;
// Convert time to total seconds (with sign)
let time_delta = sign * (time.num_seconds_from_midnight() as i32);
Ok(Modifier::DateTimeOffset {
years: sign * (date.year()),
months: sign * (date.month() as i32),
days: sign * date.day() as i32,
seconds: time_delta,
})
}
_ => Err(InvalidModifier(
"Invalid date/time offset format".to_string(),
)),
}
} else {
Err(InvalidModifier(
"Invalid date/time offset format".to_string(),
))
}
}
pub fn exec_timediff<I, E, V>(values: I) -> Value
where
V: AsValueRef,
E: ExactSizeIterator<Item = V>,
I: IntoIterator<IntoIter = E, Item = V>,
{
let mut values = values.into_iter();
if values.len() < 2 {
return Value::Null;
}
let start = parse_naive_date_time(values.next().unwrap());
let end = parse_naive_date_time(values.next().unwrap());
match (start, end) {
(Some(start), Some(end)) => {
let duration = start.signed_duration_since(end);
format_time_duration(&duration)
}
_ => Value::Null,
}
}
/// Format the time duration as +/-YYYY-MM-DD HH:MM:SS.SSS as per SQLite's timediff() function
fn format_time_duration(duration: &chrono::Duration) -> Value {
let is_negative = duration.num_seconds() < 0;
let abs_duration = if is_negative { -*duration } else { *duration };
let total_seconds = abs_duration.num_seconds();
let hours = (total_seconds % 86400) / 3600;
let minutes = (total_seconds % 3600) / 60;
let seconds = total_seconds % 60;
let days = total_seconds / 86400;
let years = days / 365;
let remaining_days = days % 365;
let months = 0;
let total_millis = abs_duration.num_milliseconds();
let millis = total_millis % 1000;
let result = format!(
"{}{:04}-{:02}-{:02} {:02}:{:02}:{:02}.{:03}",
if is_negative { "-" } else { "+" },
years,
months,
remaining_days,
hours,
minutes,
seconds,
millis
);
Value::build_text(result)
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn test_valid_get_date_from_time_value() {
let now = chrono::Local::now().to_utc().format("%Y-%m-%d").to_string();
let prev_date_str = "2024-07-20";
let test_date_str = "2024-07-21";
let next_date_str = "2024-07-22";
let test_cases: Vec<(Value, &str)> = vec![
// Format 1: YYYY-MM-DD (no timezone applicable)
(Value::build_text("2024-07-21"), test_date_str),
// Format 2: YYYY-MM-DD HH:MM
(Value::build_text("2024-07-21 22:30"), test_date_str),
(Value::build_text("2024-07-21 22:30+02:00"), test_date_str),
(Value::build_text("2024-07-21 22:30-05:00"), next_date_str),
(Value::build_text("2024-07-21 01:30+05:00"), prev_date_str),
(Value::build_text("2024-07-21 22:30Z"), test_date_str),
// Format 3: YYYY-MM-DD HH:MM:SS
(Value::build_text("2024-07-21 22:30:45"), test_date_str),
(
Value::build_text("2024-07-21 22:30:45+02:00"),
test_date_str,
),
(
Value::build_text("2024-07-21 22:30:45-05:00"),
next_date_str,
),
(
Value::build_text("2024-07-21 01:30:45+05:00"),
prev_date_str,
),
(Value::build_text("2024-07-21 22:30:45Z"), test_date_str),
// Format 4: YYYY-MM-DD HH:MM:SS.SSS
(Value::build_text("2024-07-21 22:30:45.123"), test_date_str),
(
Value::build_text("2024-07-21 22:30:45.123+02:00"),
test_date_str,
),
(
Value::build_text("2024-07-21 22:30:45.123-05:00"),
next_date_str,
),
(
Value::build_text("2024-07-21 01:30:45.123+05:00"),
prev_date_str,
),
(Value::build_text("2024-07-21 22:30:45.123Z"), test_date_str),
// Format 5: YYYY-MM-DDTHH:MM
(Value::build_text("2024-07-21T22:30"), test_date_str),
(Value::build_text("2024-07-21T22:30+02:00"), test_date_str),
(Value::build_text("2024-07-21T22:30-05:00"), next_date_str),
(Value::build_text("2024-07-21T01:30+05:00"), prev_date_str),
(Value::build_text("2024-07-21T22:30Z"), test_date_str),
// Format 6: YYYY-MM-DDTHH:MM:SS
(Value::build_text("2024-07-21T22:30:45"), test_date_str),
(
Value::build_text("2024-07-21T22:30:45+02:00"),
test_date_str,
),
(
Value::build_text("2024-07-21T22:30:45-05:00"),
next_date_str,
),
(
Value::build_text("2024-07-21T01:30:45+05:00"),
prev_date_str,
),
(Value::build_text("2024-07-21T22:30:45Z"), test_date_str),
// Format 7: YYYY-MM-DDTHH:MM:SS.SSS
(Value::build_text("2024-07-21T22:30:45.123"), test_date_str),
(
Value::build_text("2024-07-21T22:30:45.123+02:00"),
test_date_str,
),
(
Value::build_text("2024-07-21T22:30:45.123-05:00"),
next_date_str,
),
(
Value::build_text("2024-07-21T01:30:45.123+05:00"),
prev_date_str,
),
(Value::build_text("2024-07-21T22:30:45.123Z"), test_date_str),
// Format 8: HH:MM
(Value::build_text("22:30"), "2000-01-01"),
(Value::build_text("22:30+02:00"), "2000-01-01"),
(Value::build_text("22:30-05:00"), "2000-01-02"),
(Value::build_text("01:30+05:00"), "1999-12-31"),
(Value::build_text("22:30Z"), "2000-01-01"),
// Format 9: HH:MM:SS
(Value::build_text("22:30:45"), "2000-01-01"),
(Value::build_text("22:30:45+02:00"), "2000-01-01"),
(Value::build_text("22:30:45-05:00"), "2000-01-02"),
(Value::build_text("01:30:45+05:00"), "1999-12-31"),
(Value::build_text("22:30:45Z"), "2000-01-01"),
// Format 10: HH:MM:SS.SSS
(Value::build_text("22:30:45.123"), "2000-01-01"),
(Value::build_text("22:30:45.123+02:00"), "2000-01-01"),
(Value::build_text("22:30:45.123-05:00"), "2000-01-02"),
(Value::build_text("01:30:45.123+05:00"), "1999-12-31"),
(Value::build_text("22:30:45.123Z"), "2000-01-01"),
// Test Format 11: 'now'
(Value::build_text("now"), &now),
// Format 12: DDDDDDDDDD (Julian date as float or integer)
(Value::Float(2460512.5), test_date_str),
(Value::Integer(2460513), test_date_str),
];
for (input, expected) in test_cases {
let result = exec_date(&[input.clone()]);
assert_eq!(
result,
Value::build_text(expected.to_string()),
"Failed for input: {input:?}"
);
}
}
#[test]
fn test_invalid_get_date_from_time_value() {
let invalid_cases = vec![
Value::build_text("2024-07-21 25:00"), // Invalid hour
Value::build_text("2024-07-21 24:00:00"), // Invalid hour
Value::build_text("2024-07-21 23:60:00"), // Invalid minute
Value::build_text("2024-07-21 22:58:60"), // Invalid second
Value::build_text("2024-07-32"), // Invalid day
Value::build_text("2024-13-01"), // Invalid month
Value::build_text("invalid_date"), // Completely invalid string
Value::build_text(""), // Empty string
Value::Integer(i64::MAX), // Large Julian day
Value::Integer(-1), // Negative Julian day
Value::Float(f64::MAX), // Large float
Value::Float(-1.0), // Negative Julian day as float
Value::Float(f64::NAN), // NaN
Value::Float(f64::INFINITY), // Infinity
Value::Null, // Null value
Value::Blob(vec![1, 2, 3]), // Blob (unsupported type)
// Invalid timezone tests
Value::build_text("2024-07-21T12:00:00+24:00"), // Invalid timezone offset (too large)
Value::build_text("2024-07-21T12:00:00-24:00"), // Invalid timezone offset (too small)
Value::build_text("2024-07-21T12:00:00+00:60"), // Invalid timezone minutes
Value::build_text("2024-07-21T12:00:00+00:00:00"), // Invalid timezone format (extra seconds)
Value::build_text("2024-07-21T12:00:00+"), // Incomplete timezone
Value::build_text("2024-07-21T12:00:00+Z"), // Invalid timezone format
Value::build_text("2024-07-21T12:00:00+00:00Z"), // Mixing offset and Z
Value::build_text("2024-07-21T12:00:00UTC"), // Named timezone (not supported)
];
for case in invalid_cases.iter() {
let result = exec_date([case]);
match result {
Value::Text(ref result_str) if result_str.value.is_empty() => (),
_ => panic!("Expected empty string for input: {case:?}, but got: {result:?}"),
}
}
}
#[test]
fn test_valid_get_time_from_datetime_value() {
let test_time_str = "22:30:45";
let prev_time_str = "20:30:45";
let next_time_str = "03:30:45";
let test_cases = vec![
// Format 1: YYYY-MM-DD (no timezone applicable)
(Value::build_text("2024-07-21"), "00:00:00"),
// Format 2: YYYY-MM-DD HH:MM
(Value::build_text("2024-07-21 22:30"), "22:30:00"),
(Value::build_text("2024-07-21 22:30+02:00"), "20:30:00"),
(Value::build_text("2024-07-21 22:30-05:00"), "03:30:00"),
(Value::build_text("2024-07-21 22:30Z"), "22:30:00"),
// Format 3: YYYY-MM-DD HH:MM:SS
(Value::build_text("2024-07-21 22:30:45"), test_time_str),
(
Value::build_text("2024-07-21 22:30:45+02:00"),
prev_time_str,
),
(
Value::build_text("2024-07-21 22:30:45-05:00"),
next_time_str,
),
(Value::build_text("2024-07-21 22:30:45Z"), test_time_str),
// Format 4: YYYY-MM-DD HH:MM:SS.SSS
(Value::build_text("2024-07-21 22:30:45.123"), test_time_str),
(
Value::build_text("2024-07-21 22:30:45.123+02:00"),
prev_time_str,
),
(
Value::build_text("2024-07-21 22:30:45.123-05:00"),
next_time_str,
),
(Value::build_text("2024-07-21 22:30:45.123Z"), test_time_str),
// Format 5: YYYY-MM-DDTHH:MM
(Value::build_text("2024-07-21T22:30"), "22:30:00"),
(Value::build_text("2024-07-21T22:30+02:00"), "20:30:00"),
(Value::build_text("2024-07-21T22:30-05:00"), "03:30:00"),
(Value::build_text("2024-07-21T22:30Z"), "22:30:00"),
// Format 6: YYYY-MM-DDTHH:MM:SS
(Value::build_text("2024-07-21T22:30:45"), test_time_str),
(
Value::build_text("2024-07-21T22:30:45+02:00"),
prev_time_str,
),
(
Value::build_text("2024-07-21T22:30:45-05:00"),
next_time_str,
),
(Value::build_text("2024-07-21T22:30:45Z"), test_time_str),
// Format 7: YYYY-MM-DDTHH:MM:SS.SSS
(Value::build_text("2024-07-21T22:30:45.123"), test_time_str),
(
Value::build_text("2024-07-21T22:30:45.123+02:00"),
prev_time_str,
),
(
Value::build_text("2024-07-21T22:30:45.123-05:00"),
next_time_str,
),
(Value::build_text("2024-07-21T22:30:45.123Z"), test_time_str),
// Format 8: HH:MM
(Value::build_text("22:30"), "22:30:00"),
(Value::build_text("22:30+02:00"), "20:30:00"),
(Value::build_text("22:30-05:00"), "03:30:00"),
(Value::build_text("22:30Z"), "22:30:00"),
// Format 9: HH:MM:SS
(Value::build_text("22:30:45"), test_time_str),
(Value::build_text("22:30:45+02:00"), prev_time_str),
(Value::build_text("22:30:45-05:00"), next_time_str),
(Value::build_text("22:30:45Z"), test_time_str),
// Format 10: HH:MM:SS.SSS
(Value::build_text("22:30:45.123"), test_time_str),
(Value::build_text("22:30:45.123+02:00"), prev_time_str),
(Value::build_text("22:30:45.123-05:00"), next_time_str),
(Value::build_text("22:30:45.123Z"), test_time_str),
// Format 12: DDDDDDDDDD (Julian date as float or integer)
(Value::Float(2460082.1), "14:24:00"),
(Value::Integer(2460082), "12:00:00"),
];
for (input, expected) in test_cases {
let result = exec_time(&[input]);
if let Value::Text(result_str) = result {
assert_eq!(result_str.as_str(), expected);
} else {
panic!("Expected Value::Text, but got: {result:?}");
}
}
}
#[test]
fn test_invalid_get_time_from_datetime_value() {
let invalid_cases = vec![
Value::build_text("2024-07-21 25:00"), // Invalid hour
Value::build_text("2024-07-21 24:00:00"), // Invalid hour
Value::build_text("2024-07-21 23:60:00"), // Invalid minute
Value::build_text("2024-07-21 22:58:60"), // Invalid second
Value::build_text("2024-07-32"), // Invalid day
Value::build_text("2024-13-01"), // Invalid month
Value::build_text("invalid_date"), // Completely invalid string
Value::build_text(""), // Empty string
Value::Integer(i64::MAX), // Large Julian day
Value::Integer(-1), // Negative Julian day
Value::Float(f64::MAX), // Large float
Value::Float(-1.0), // Negative Julian day as float
Value::Float(f64::NAN), // NaN
Value::Float(f64::INFINITY), // Infinity
Value::Null, // Null value
Value::Blob(vec![1, 2, 3]), // Blob (unsupported type)
// Invalid timezone tests
Value::build_text("2024-07-21T12:00:00+24:00"), // Invalid timezone offset (too large)
Value::build_text("2024-07-21T12:00:00-24:00"), // Invalid timezone offset (too small)
Value::build_text("2024-07-21T12:00:00+00:60"), // Invalid timezone minutes
Value::build_text("2024-07-21T12:00:00+00:00:00"), // Invalid timezone format (extra seconds)
Value::build_text("2024-07-21T12:00:00+"), // Incomplete timezone
Value::build_text("2024-07-21T12:00:00+Z"), // Invalid timezone format
Value::build_text("2024-07-21T12:00:00+00:00Z"), // Mixing offset and Z
Value::build_text("2024-07-21T12:00:00UTC"), // Named timezone (not supported)
];
for case in invalid_cases {
let result = exec_time(&[case.clone()]);
match result {
Value::Text(ref result_str) if result_str.value.is_empty() => (),
_ => panic!("Expected empty string for input: {case:?}, but got: {result:?}"),
}
}
}
#[test]
fn test_parse_days() {
assert_eq!(parse_modifier("5 days").unwrap(), Modifier::Days(5));
assert_eq!(parse_modifier("-3 days").unwrap(), Modifier::Days(-3));
assert_eq!(parse_modifier("+2 days").unwrap(), Modifier::Days(2));
assert_eq!(parse_modifier("4 days").unwrap(), Modifier::Days(4));
assert_eq!(parse_modifier("6 DAYS").unwrap(), Modifier::Days(6));
assert_eq!(parse_modifier("+5 DAYS").unwrap(), Modifier::Days(5));
}
#[test]
fn test_parse_hours() {
assert_eq!(parse_modifier("12 hours").unwrap(), Modifier::Hours(12));
assert_eq!(parse_modifier("-2 hours").unwrap(), Modifier::Hours(-2));
assert_eq!(parse_modifier("+3 HOURS").unwrap(), Modifier::Hours(3));
}
#[test]
fn test_parse_minutes() {
assert_eq!(parse_modifier("30 minutes").unwrap(), Modifier::Minutes(30));
assert_eq!(
parse_modifier("-15 minutes").unwrap(),
Modifier::Minutes(-15)
);
assert_eq!(
parse_modifier("+45 MINUTES").unwrap(),
Modifier::Minutes(45)
);
}
#[test]
fn test_parse_seconds() {
assert_eq!(parse_modifier("45 seconds").unwrap(), Modifier::Seconds(45));
assert_eq!(
parse_modifier("-10 seconds").unwrap(),
Modifier::Seconds(-10)
);
assert_eq!(
parse_modifier("+20 SECONDS").unwrap(),
Modifier::Seconds(20)
);
}
#[test]
fn test_parse_months() {
assert_eq!(parse_modifier("3 months").unwrap(), Modifier::Months(3));
assert_eq!(parse_modifier("-1 months").unwrap(), Modifier::Months(-1));
assert_eq!(parse_modifier("+6 MONTHS").unwrap(), Modifier::Months(6));
}
#[test]
fn test_parse_years() {
assert_eq!(parse_modifier("2 years").unwrap(), Modifier::Years(2));
assert_eq!(parse_modifier("-1 years").unwrap(), Modifier::Years(-1));
assert_eq!(parse_modifier("+10 YEARS").unwrap(), Modifier::Years(10));
}
#[test]
fn test_parse_time_offset() {
assert_eq!(
parse_modifier("+01:30").unwrap(),
Modifier::TimeOffset(TimeDelta::hours(1) + TimeDelta::minutes(30))
);
assert_eq!(
parse_modifier("-00:45").unwrap(),
Modifier::TimeOffset(TimeDelta::minutes(-45))
);
assert_eq!(
parse_modifier("+02:15:30").unwrap(),
Modifier::TimeOffset(
TimeDelta::hours(2) + TimeDelta::minutes(15) + TimeDelta::seconds(30)
)
);
assert_eq!(
parse_modifier("+02:15:30.250").unwrap(),
Modifier::TimeOffset(
TimeDelta::hours(2) + TimeDelta::minutes(15) + TimeDelta::seconds(30)
)
);
}
#[test]
fn test_parse_date_offset() {
assert_eq!(
parse_modifier("+2023-05-15").unwrap(),
Modifier::DateOffset {
years: 2023,
months: 5,
days: 15,
}
);
assert_eq!(
parse_modifier("-2023-05-15").unwrap(),
Modifier::DateOffset {
years: -2023,
months: -5,
days: -15,
}
);
}
#[test]
fn test_parse_date_time_offset() {
assert_eq!(
parse_modifier("+2023-05-15 14:30").unwrap(),
Modifier::DateTimeOffset {
years: 2023,
months: 5,
days: 15,
seconds: (14 * 60 + 30) * 60,
}
);
assert_eq!(
parse_modifier("-0001-05-15 14:30").unwrap(),
Modifier::DateTimeOffset {
years: -1,
months: -5,
days: -15,
seconds: -((14 * 60 + 30) * 60),
}
);
}
#[test]
fn test_parse_start_of() {
assert_eq!(
parse_modifier("start of month").unwrap(),
Modifier::StartOfMonth
);
assert_eq!(
parse_modifier("START OF MONTH").unwrap(),
Modifier::StartOfMonth
);
assert_eq!(
parse_modifier("start of year").unwrap(),
Modifier::StartOfYear
);
assert_eq!(
parse_modifier("START OF YEAR").unwrap(),
Modifier::StartOfYear
);
assert_eq!(
parse_modifier("start of day").unwrap(),
Modifier::StartOfDay
);
assert_eq!(
parse_modifier("START OF DAY").unwrap(),
Modifier::StartOfDay
);
}
#[test]
fn test_parse_weekday() {
assert_eq!(parse_modifier("weekday 0").unwrap(), Modifier::Weekday(0));
assert_eq!(parse_modifier("WEEKDAY 6").unwrap(), Modifier::Weekday(6));
}
#[test]
fn test_parse_ceiling_modifier() {
assert_eq!(parse_modifier("ceiling").unwrap(), Modifier::Ceiling);
assert_eq!(parse_modifier("CEILING").unwrap(), Modifier::Ceiling);
}
#[test]
fn test_parse_other_modifiers() {
assert_eq!(parse_modifier("unixepoch").unwrap(), Modifier::UnixEpoch);
assert_eq!(parse_modifier("UNIXEPOCH").unwrap(), Modifier::UnixEpoch);
assert_eq!(parse_modifier("julianday").unwrap(), Modifier::JulianDay);
assert_eq!(parse_modifier("JULIANDAY").unwrap(), Modifier::JulianDay);
assert_eq!(parse_modifier("auto").unwrap(), Modifier::Auto);
assert_eq!(parse_modifier("AUTO").unwrap(), Modifier::Auto);
assert_eq!(parse_modifier("localtime").unwrap(), Modifier::Localtime);
assert_eq!(parse_modifier("LOCALTIME").unwrap(), Modifier::Localtime);
assert_eq!(parse_modifier("utc").unwrap(), Modifier::Utc);
assert_eq!(parse_modifier("UTC").unwrap(), Modifier::Utc);
assert_eq!(parse_modifier("subsec").unwrap(), Modifier::Subsec);
assert_eq!(parse_modifier("SUBSEC").unwrap(), Modifier::Subsec);
assert_eq!(parse_modifier("subsecond").unwrap(), Modifier::Subsec);
assert_eq!(parse_modifier("SUBSECOND").unwrap(), Modifier::Subsec);
}
#[test]
fn test_parse_invalid_modifier() {
assert!(parse_modifier("invalid modifier").is_err());
assert!(parse_modifier("5").is_err());
assert!(parse_modifier("days").is_err());
assert!(parse_modifier("++5 days").is_err());
assert!(parse_modifier("weekday 7").is_err());
}
fn create_datetime(
year: i32,
month: u32,
day: u32,
hour: u32,
min: u32,
sec: u32,
) -> NaiveDateTime {
NaiveDate::from_ymd_opt(year, month, day)
.unwrap()
.and_hms_opt(hour, min, sec)
.unwrap()
}
fn setup_datetime() -> NaiveDateTime {
create_datetime(2023, 6, 15, 12, 30, 45)
}
#[test]
fn test_apply_modifier_days() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "5 days", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 20, 12, 30, 45));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-3 days", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 12, 12, 30, 45));
}
#[test]
fn test_apply_modifier_hours() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "6 hours", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 18, 30, 45));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-2 hours", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 10, 30, 45));
}
#[test]
fn test_apply_modifier_minutes() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "45 minutes", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 13, 15, 45));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-15 minutes", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 12, 15, 45));
}
#[test]
fn test_apply_modifier_seconds() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "30 seconds", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 12, 31, 15));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-20 seconds", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 12, 30, 25));
}
#[test]
fn test_apply_modifier_time_offset() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "+01:30", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 14, 0, 45));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-00:45", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 11, 45, 45));
}
#[test]
fn test_apply_modifier_date_time_offset() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "+0001-01-01 01:01", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2024, 7, 16, 13, 31, 45));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-0001-01-01 01:01", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2022, 5, 14, 11, 29, 45));
// Test with larger offsets
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "+0002-03-04 05:06", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2025, 9, 19, 17, 36, 45));
dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "-0002-03-04 05:06", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2021, 3, 11, 7, 24, 45));
}
#[test]
fn test_apply_modifier_start_of_year() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "start of year", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 1, 0, 0, 0));
}
#[test]
fn test_apply_modifier_start_of_day() {
let mut dt = setup_datetime();
let mut n_floor = 0;
apply_modifier(&mut dt, "start of day", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 0, 0, 0));
}
fn text(value: &str) -> Value {
Value::build_text(value.to_string())
}
fn format(dt: NaiveDateTime) -> String {
dt.format("%Y-%m-%d %H:%M:%S").to_string()
}
fn weekday_sunday_based(dt: &NaiveDateTime) -> u32 {
dt.weekday().num_days_from_sunday()
}
#[test]
fn test_single_modifier() {
let time = setup_datetime();
let expected = format(time - TimeDelta::days(1));
let result = exec_datetime(
&[text("2023-06-15 12:30:45"), text("-1 day")],
DateTimeOutput::DateTime,
);
assert_eq!(result, text(&expected));
}
#[test]
fn test_multiple_modifiers() {
let time = setup_datetime();
let expected = format(time - TimeDelta::days(1) + TimeDelta::hours(3));
let result = exec_datetime(
&[
text("2023-06-15 12:30:45"),
text("-1 day"),
text("+3 hours"),
],
DateTimeOutput::DateTime,
);
assert_eq!(result, text(&expected));
}
#[test]
fn test_subsec_modifier() {
let time = setup_datetime();
let result = exec_datetime(
&[text("2023-06-15 12:30:45"), text("subsec")],
DateTimeOutput::Time,
);
let result = NaiveTime::parse_from_str(&result.to_string(), "%H:%M:%S%.3f").unwrap();
assert_eq!(time.time(), result);
}
#[test]
fn test_start_of_day_modifier() {
let time = setup_datetime();
let start_of_day = time.date().and_hms_opt(0, 0, 0).unwrap();
let expected = format(start_of_day - TimeDelta::days(1));
let result = exec_datetime(
&[
text("2023-06-15 12:30:45"),
text("start of day"),
text("-1 day"),
],
DateTimeOutput::DateTime,
);
assert_eq!(result, text(&expected));
}
#[test]
fn test_start_of_month_modifier() {
let time = setup_datetime();
let start_of_month = NaiveDate::from_ymd_opt(time.year(), time.month(), 1)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap();
let expected = format(start_of_month + TimeDelta::days(1));
let result = exec_datetime(
&[
text("2023-06-15 12:30:45"),
text("start of month"),
text("+1 day"),
],
DateTimeOutput::DateTime,
);
assert_eq!(result, text(&expected));
}
#[test]
fn test_start_of_year_modifier() {
let time = setup_datetime();
let start_of_year = NaiveDate::from_ymd_opt(time.year(), 1, 1)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap();
let expected = format(start_of_year + TimeDelta::days(30) + TimeDelta::hours(5));
let result = exec_datetime(
&[
text("2023-06-15 12:30:45"),
text("start of year"),
text("+30 days"),
text("+5 hours"),
],
DateTimeOutput::DateTime,
);
assert_eq!(result, text(&expected));
}
#[test]
fn test_timezone_modifiers() {
let dt = setup_datetime();
let result_local = exec_datetime(
&[text("2023-06-15 12:30:45"), text("localtime")],
DateTimeOutput::DateTime,
);
assert_eq!(
result_local,
text(
&dt.and_utc()
.with_timezone(&chrono::Local)
.format("%Y-%m-%d %H:%M:%S")
.to_string()
)
);
// TODO: utc modifier assumes time given is not already utc
// add test when fixed in the future
}
#[test]
fn test_combined_modifiers() {
let time = create_datetime(2000, 1, 1, 0, 0, 0);
let expected = time - TimeDelta::days(1)
+ TimeDelta::hours(5)
+ TimeDelta::minutes(30)
+ TimeDelta::seconds(15);
let result = exec_datetime(
&[
text("2000-01-01 00:00:00"),
text("-1 day"),
text("+5 hours"),
text("+30 minutes"),
text("+15 seconds"),
text("subsec"),
],
DateTimeOutput::DateTime,
);
let result =
NaiveDateTime::parse_from_str(&result.to_string(), "%Y-%m-%d %H:%M:%S%.3f").unwrap();
assert_eq!(expected, result);
}
#[test]
fn test_max_datetime_limit() {
// max datetime limit
let max = NaiveDate::from_ymd_opt(9999, 12, 31)
.unwrap()
.and_hms_opt(23, 59, 59)
.unwrap();
let expected = format(max);
let result = exec_datetime(&[text("9999-12-31 23:59:59")], DateTimeOutput::DateTime);
assert_eq!(result, text(&expected));
}
// leap second
#[test]
fn test_leap_second_ignored() {
let leap_second = NaiveDate::from_ymd_opt(2024, 6, 30)
.unwrap()
.and_hms_nano_opt(23, 59, 59, 1_500_000_000)
.unwrap();
let expected = String::new(); // SQLite ignores leap seconds
let result = exec_datetime(&[text(&leap_second.to_string())], DateTimeOutput::DateTime);
assert_eq!(result, text(&expected));
}
#[test]
fn test_already_on_weekday_no_change() {
// 2023-01-01 is a Sunday => weekday 0
let mut dt = create_datetime(2023, 1, 1, 12, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "weekday 0", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 1, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 0);
}
#[test]
fn test_move_forward_if_different() {
// 2023-01-01 is a Sunday => weekday 0
// "weekday 1" => next Monday => 2023-01-02
let mut dt = create_datetime(2023, 1, 1, 12, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "weekday 1", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 2, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 1);
// 2023-01-03 is a Tuesday => weekday 2
// "weekday 5" => next Friday => 2023-01-06
let mut dt = create_datetime(2023, 1, 3, 12, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "weekday 5", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 6, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 5);
}
#[test]
fn test_wrap_around_weekend() {
// 2023-01-06 is a Friday => weekday 5
// "weekday 0" => next Sunday => 2023-01-08
let mut dt = create_datetime(2023, 1, 6, 12, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "weekday 0", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 8, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 0);
// Now confirm that being on Sunday (weekday 0) and asking for "weekday 0" stays put
apply_modifier(&mut dt, "weekday 0", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 8, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 0);
}
#[test]
fn test_same_day_stays_put() {
// 2023-01-05 is a Thursday => weekday 4
// Asking for weekday 4 => no change
let mut dt = create_datetime(2023, 1, 5, 12, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "weekday 4", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 5, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 4);
}
#[test]
fn test_already_on_friday_no_change() {
// 2023-01-06 is a Friday => weekday 5
// Asking for weekday 5 => no change if already on Friday
let mut dt = create_datetime(2023, 1, 6, 12, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "weekday 5", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 1, 6, 12, 0, 0));
assert_eq!(weekday_sunday_based(&dt), 5);
}
#[test]
fn test_apply_modifier_julianday() {
let dt = create_datetime(2000, 1, 1, 12, 0, 0);
// Convert datetime to julian day using our implementation
let julian_day_value = to_julian_day_exact(&dt);
// Convert back
let dt_result = julian_day_to_datetime(julian_day_value).unwrap();
assert_eq!(dt_result, dt);
}
#[test]
fn test_apply_modifier_start_of_month() {
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let mut n_floor = 0;
apply_modifier(&mut dt, "start of month", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 1, 0, 0, 0));
}
#[test]
fn test_apply_modifier_subsec() {
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let dt_with_nanos = dt.with_nanosecond(123_456_789).unwrap();
dt = dt_with_nanos;
let mut n_floor = 0;
apply_modifier(&mut dt, "subsec", &mut n_floor).unwrap();
assert_eq!(dt, dt_with_nanos);
}
#[test]
fn test_apply_modifier_floor_modifier_n_floor_gt_0() {
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let mut n_floor = 3;
apply_modifier(&mut dt, "floor", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 12, 12, 30, 45));
}
#[test]
fn test_apply_modifier_floor_modifier_n_floor_le_0() {
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let mut n_floor = 0;
apply_modifier(&mut dt, "floor", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 15, 12, 30, 45));
n_floor = 2;
apply_modifier(&mut dt, "floor", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 13, 12, 30, 45));
}
#[test]
fn test_apply_modifier_ceiling_modifier_sets_n_floor_to_zero() {
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let mut n_floor = 5;
apply_modifier(&mut dt, "ceiling", &mut n_floor).unwrap();
assert_eq!(n_floor, 0);
}
#[test]
fn test_apply_modifier_start_of_month_basic() {
// Basic check: from mid-month to the 1st at 00:00:00.
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let mut n_floor = 0;
apply_modifier(&mut dt, "start of month", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 1, 0, 0, 0));
}
#[test]
fn test_apply_modifier_start_of_month_already_at_first() {
// If we're already at the start of the month, no change.
let mut dt = create_datetime(2023, 6, 1, 0, 0, 0);
let mut n_floor = 0;
apply_modifier(&mut dt, "start of month", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 6, 1, 0, 0, 0));
}
#[test]
fn test_apply_modifier_start_of_month_edge_case() {
// edge case: month boundary. 2023-07-31 -> start of July.
let mut dt = create_datetime(2023, 7, 31, 23, 59, 59);
let mut n_floor = 0;
apply_modifier(&mut dt, "start of month", &mut n_floor).unwrap();
assert_eq!(dt, create_datetime(2023, 7, 1, 0, 0, 0));
}
#[test]
fn test_apply_modifier_subsec_no_change() {
let mut dt = create_datetime(2023, 6, 15, 12, 30, 45);
let dt_with_nanos = dt.with_nanosecond(123_456_789).unwrap();
dt = dt_with_nanos;
let mut n_floor = 0;
apply_modifier(&mut dt, "subsec", &mut n_floor).unwrap();
assert_eq!(dt, dt_with_nanos);
}
#[test]
fn test_apply_modifier_subsec_preserves_fractional_seconds() {
let mut dt = create_datetime(2025, 1, 2, 4, 12, 21)
.with_nanosecond(891_000_000) // 891 milliseconds
.unwrap();
let mut n_floor = 0;
apply_modifier(&mut dt, "subsec", &mut n_floor).unwrap();
let formatted = dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string();
assert_eq!(formatted, "2025-01-02 04:12:21.891");
}
#[test]
fn test_apply_modifier_subsec_no_fractional_seconds() {
let mut dt = create_datetime(2025, 1, 2, 4, 12, 21);
let mut n_floor = 0;
apply_modifier(&mut dt, "subsec", &mut n_floor).unwrap();
let formatted = dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string();
assert_eq!(formatted, "2025-01-02 04:12:21.000");
}
#[test]
fn test_apply_modifier_subsec_truncate_to_milliseconds() {
let mut dt = create_datetime(2025, 1, 2, 4, 12, 21)
.with_nanosecond(891_123_456)
.unwrap();
let mut n_floor = 0;
apply_modifier(&mut dt, "subsec", &mut n_floor).unwrap();
let formatted = dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string();
assert_eq!(formatted, "2025-01-02 04:12:21.891");
}
#[test]
fn test_is_leap_second() {
let dt = DateTime::from_timestamp(1483228799, 999_999_999)
.unwrap()
.naive_utc();
assert!(!is_leap_second(&dt));
let dt = DateTime::from_timestamp(1483228799, 1_500_000_000)
.unwrap()
.naive_utc();
assert!(is_leap_second(&dt));
}
#[test]
fn test_strftime() {}
#[test]
fn test_exec_timediff() {
let start = Value::build_text("12:00:00");
let end = Value::build_text("14:30:45");
let expected = Value::build_text("-0000-00-00 02:30:45.000");
assert_eq!(exec_timediff(&[start, end]), expected);
let start = Value::build_text("14:30:45");
let end = Value::build_text("12:00:00");
let expected = Value::build_text("+0000-00-00 02:30:45.000");
assert_eq!(exec_timediff(&[start, end]), expected);
let start = Value::build_text("12:00:01.300");
let end = Value::build_text("12:00:00.500");
let expected = Value::build_text("+0000-00-00 00:00:00.800");
assert_eq!(exec_timediff(&[start, end]), expected);
let start = Value::build_text("13:30:00");
let end = Value::build_text("16:45:30");
let expected = Value::build_text("-0000-00-00 03:15:30.000");
assert_eq!(exec_timediff(&[start, end]), expected);
let start = Value::build_text("2023-05-10 23:30:00");
let end = Value::build_text("2023-05-11 01:15:00");
let expected = Value::build_text("-0000-00-00 01:45:00.000");
assert_eq!(exec_timediff(&[start, end]), expected);
let start = Value::Null;
let end = Value::build_text("12:00:00");
let expected = Value::Null;
assert_eq!(exec_timediff(&[start, end]), expected);
let start = Value::build_text("not a time");
let end = Value::build_text("12:00:00");
let expected = Value::Null;
assert_eq!(exec_timediff(&[start, end.clone()]), expected);
let start = Value::build_text("12:00:00");
let expected = Value::Null;
assert_eq!(exec_timediff(&[start, end]), expected);
}
}
Reference in New Issue View Git Blame Copy Permalink