Rusqlite + Chrono: How do I simplify code to obtain chrono datetime value

help
1

I am working on a function that determines the latest date in an sqlite database then subtracts days from that date, returning a vector of 4 dates as strings in 2022-04-20 format. In the database the dates are of type text. Instead of creating the variable latest_date is there a way to directly use the results of the sql query in NaiveDate parse_from_str which would hopefully simplify the code. Any assistance will be appreciated.

pub fn get_start_dates() -> Vec<String> {

    let db_file_path: &str = "stock_data.db";
    let conn = match Connection::open(db_file_path) {
        Ok(con) => {
            con
        },
        Err(e) => {
            println!("Error creating connectiont to db: {}", e);
            return Vec::new()
        },
    };

    let latest_date: String = match conn.query_row("SELECT MAX(date) FROM data", [], |row| row.get(0)) {
        Ok(result)=> {
            result
        },
        Err(e) => {
            println!("Error getting latest date from db: {}", e);
            return Vec::new()
        },
    };

    let latest_datetime: NaiveDate = match NaiveDate::parse_from_str(&latest_date, "%Y-%m-%d") {
        Ok(result) => {
            result
        },
        Err(e) => {
            println!("Error parsing string date to datetime: {}", e);
            return Vec::new()
        },
    };

    let mut start_dates: Vec<String> = Vec::new();

    for num_days in [90, 180, 365, 3650] {
        let temp_value: String = match latest_datetime.checked_sub_days(Days::new(num_days)) {
            Some(result) => {
                result.format("%Y-%m-%d").to_string()
            },
            None => {
                println!("error");
                return Vec::new()
            },
        };
        start_dates.push(temp_value);        
    }
    start_dates
}
2

That's not a good precondition. Stringly typed data will always cause troubles, because you'll need conversion. I suggest you either

  • Redesign your database to use actual date types, or
  • If you cannot change the DB structure, introduce a wrapper type around a date string

you're using SQLite, so you're SOL on data types. :smiley:

You can then implement appropriate conversion traits on the wrapper type like struct StringlyDate(String) to convert it into/from an actual Date data type.

As for the four offsets, you can implement an extension trait on e.g. Date that provides

const OFFSET_DAYS: [Days; 4] = [Days::new(90), Days::new(180), Days::new(365), Days::new(3650)];

fn offsets(self) -> Vec<Self> {
    OFFSET_DAYS.filter_map(|offset | self.checked_sub_days(offset)).collect()
}

You can then use the conversion traits on the resulting elements to convert it back to your stringly wrapper type when necessary.

3

Well you can simplify a lot of the code by returning a Result and using either helpers like map_err or a crate like anyhow so you can just ? away the error cases, but I'm not sure that actually covers your question.

If you're dealing with a lot of these string dates, I think the best you can do is just factor out that handling. Maybe just a function, maybe a wrapper type, depending on how you're using it.

4

See FromSql in rusqlite::types - Rust
(as indicated, you need to activate the chrono feature)

5

After some input from the forum, I switched to error handling with the ? and the closure functionality with query_row. Below is the updated function. Thank you for the input.

pub async fn get_start_dates() -> Result<Vec<String>, SqlChronoError> {

    let db_file_path: &str = "stock_data.db";
    let conn = Connection::open(db_file_path)?;

    let lastest_datetime_string = conn.query_row("SELECT MAX(date) FROM data", [], |row| row.get(0).map(|x: String| x.to_string()))?;
    let latest_datetime = NaiveDate::parse_from_str(&lastest_datetime_string, "%Y-%m-%d")?;

    const OFFSET_DAYS: [Days; 4] = [Days::new(90),
                                    Days::new(180),
                                    Days::new(365),
                                    Days::new(3650)];

    let start_dates = OFFSET_DAYS.iter()
            .filter_map(|offset| latest_datetime.checked_sub_days(*offset))
            .map(|nav_date| nav_date.format("%Y-%m-%d").to_string())
            .collect::<Vec<String>>();

    Ok(start_dates)
}