Question about suitable types for APIs that don't have timezone information

[jmcnamara]

This is a question and not an issue. I didn't see a specific Chrono forum so I am asking here. Apologies if it isn't the right place.

I am writing an Excel xlsx writing library in rust and would like to use chrono::DateTime as the type for API functions/methods for writing Excel date and times.

Datetimes in Excel are a serial date with days counted from an epoch (generally 1899-12-31) and the time as a percentage/decimal of the seconds in the day. Both are stored in the same f64 value, for example, 2022/09/17 12:00:00 is stored as 44821.5 with a separate numeric format like yyyy/mm/dd hh:mm. (Excel can save dates in a text ISO 8601 format in "Strict Open XML Spreadsheet" format but in practice this is rarely used.) Excel also doesn't use timezones or try to convert or encode timezone information in any way.

I am looking for some advice on what you think might be suitable types for the functions/method. I plan to use DateTime<Utc>, Date<Utc> and NaiveTime for date-times, dates, and time respectively. However since there aren't any timezones involved maybe NaiveDateTime and NaiveDate are better. At the same time they seem, from the documentation, to be lower level types so they may not be suitable for general APIs. And also I see some discussion about deprecating NaiveDate.

Here is a sample rust main with a conversion function for reference:

use chrono::prelude::*;

fn main() {
    let datetime = Utc.ymd(1899, 12, 31).and_hms(0, 0, 0);
    assert_eq!(0.0, datetime_to_excel(datetime));

    let datetime = Utc.ymd(2022, 9, 17).and_hms(12, 0, 0);
    assert_eq!(44821.5, datetime_to_excel(datetime));
}

// Convert a chrono::DateTime to an Excel serial datetime. In Excel a datetime
// is stored as a f64 where the integer part stores the number of days since the
// epoch and the fractional part stores the percentage of the day.
fn datetime_to_excel(datetime: DateTime<Utc>) -> f64 {
    let epoch = Utc.ymd(1899, 12, 31).and_hms(0, 0, 0);

    // The date portion of the Excel datetime is the number of days since the
    // epoch, which is either 1899-12-31 or 1904-01-01.
    let since_epoch = datetime - epoch;
    let mut excel_date = since_epoch.num_days() as f64;

    // For legacy reasons Excel treats 1900 as a leap year. We add an additional
    // day for dates after the leapday in the 1899 epoch.
    if epoch.year() == 1899 && excel_date > 59.0 {
        excel_date += 1.0;
    }

    // The time portion of the Excel datetime is the number of seconds divided
    // by the total number of seconds in the day. We start by getting the number
    // of seconds in the day.
    let mut excel_time = datetime.num_seconds_from_midnight() as f64;

    // Add the milliseconds as a fraction of a second. Milliseconds are the
    // smallest resolution supported by Excel.
    excel_time += datetime.timestamp_subsec_millis() as f64 / 1000.0;

    // Get the time as a percentage of the total number of seconds in the day.
    excel_time /= 24.0 * 60.0 * 60.0;

    excel_date + excel_time
}

Any advice is appreciated.

[esheppa]

Thanks for the questions @jmcnamara. In your specific case NaiveDateTime, NaiveDate and NaiveTime are probably most suitable for representing the equivalent types in Excel. We will likely rename NaiveTime and NaiveDate in future to just Time and Date which will promote them a bit better as choices, while deprecating Date<Tz>. DateTime<Utc> is probably a sub-optimal choice however as I think users entering timestamps in an Excel file would probably view these as Local/Naive timestamps.

[jmcnamara]

Thanks. I appreciate the feedback. Closing.