Datetimes include extraneous microseconds

Issue #105 resolved
Christopher Groskopf created an issue

When reading a simple test file I created in Open Office (on OSX), I noticed that my datetimes are not exactly correct. The following translations happened:

{{{ spreadsheet -> python 01/01/1948 14:57:13 -> 1948-01-01T14:57:12.999997 01/01/1971 04:14:00 -> 1971-01-01T04:14:00.000001 }}}

This looks to me like a classic case of Python's floating point inaccuracy. I'm currently working around it like this:

{{{

!python

    # v is a datetime obtained from cell.value.
    ms = v.microsecond

    if ms < 1000:
        v = v.replace(microsecond=0)
    elif ms > 999000:
        v = v.replace(second=v.second + 1, microsecond=0)

}}}

Obviously this is an imperfect solution--though the potential inaccuracy is small it will create a lot of overhead for larger documents.

All testing was done on commit 134c257abd1e (latest as of 12/26). Thanks!

Comments (8)

  1. Gregoire Piffault

    I came across the same problem with a file (didn't tried with yours) and found a solution replacing in shared/datetime.py

    hours = floor(value * 24)
    mins = floor(value * 24 * 60) - floor(hours * 60)
    secs = floor(value * 24 * 60 * 60) - floor(hours * 60 * 60) - \
            floor(mins * 60)
    return datetime.time(int(hours), int(mins), int(secs))
    

    By

    secs = int(round(value * 24 * 60 * 60))
    hours, secs = divmod(secs, 3600)
    mins, secs = divmod(secs, 60)
    return datetime.time(hours, mins, secs)
    
  2. CharlieC

    This seems to have been resolved some time ago with a move to using Python's datetime library.

    wb = load_workbook("bug105.xlsx")
    ws = wb.active
    ws['G2']
    <Cell Sheet1.G2>
    ws['G2'].value
    datetime.datetime(1971, 1, 1, 4, 14, 0, 1)
    
  3. DingGGu

    Same issue here. on macOS 10.14

                        if value.microsecond > 999000:
                            value = value + timedelta(seconds=1)
    
                        # wtf end
                        value = value.replace(microsecond=0, tzinfo=ctz)
    
  4. Log in to comment