Store datetime as native db types instead of seconds

[bill.mill+trac@…]

Entering a date for a milesone later than some point in 2038 is not supported. I encountered this when entering 2099 as the year, in order to make a milestone strictly a "future" milestone.

Why go to the effort of doing this? Ask yourself if it's the right thing to do to a user.

I assume you use the number of seconds in Unix time to sort dates. I suggest that this is a suboptimal method of comparing dates, and that a better one should be used.

Traceback (most recent call last):
  File "C:\Python23\Lib\site-packages\trac\web\standalone.py", line 303, in _do_trac_req
    dispatch_request(path_info, req, env)
  File "C:\Python23\Lib\site-packages\trac\web\main.py", line 139, in dispatch_request
    dispatcher.dispatch(req)
  File "C:\Python23\Lib\site-packages\trac\web\main.py", line 107, in dispatch
    resp = chosen_handler.process_request(req)
  File "C:\Python23\Lib\site-packages\trac\ticket\roadmap.py", line 355, in process_request
    self._do_save(req, db, milestone)
  File "C:\Python23\Lib\site-packages\trac\ticket\roadmap.py", line 393, in _do_save
    milestone.due = due and parse_date(due) or 0
  File "C:\Python23\Lib\site-packages\trac\util.py", line 453, in parse_date
    seconds = time.mktime(date)
OverflowError: mktime argument out of range

(Assigned to the ticket component, because a very superficial code read shows that ticket.model.Milestone appears to be the root source of the error)

[Christian Boos]

In 0.11, we switched to using datetime objects in the Python code. Those effectively support a wider range of dates. However, at the database level, we're still using the "epoch" (number of seconds since 1970), so it might be interesting to switch to an other representation there as well.

OTOH, this could well introduce another flury of database incompatibilities, so it's not clear if the benefits would outweight the inconvenients.

[osimons]

#5322 solves the traceback side of this - it now presents a useful error message at least.

cboos, I suppose you scheduled it as a future thing to remember, so I won't close it as duplicate. No doubt it will get more urgent as the years fly by, but in short-medium term I can't see much point in leaving it open - if nothing else someone can reopen it in 25+ years when scheduling for 2038 may actually be needed :-) I'll leave it to you to decide though.

[Christian Boos]

Replying to osimons:

cboos, I suppose you scheduled it as a future thing to remember, so I won't close it as duplicate. No doubt it will get more urgent as the years fly by, but in short-medium term I can't see much point in leaving it open

No, I left it open, tagged as "consider", for deciding whether we should eventually go with native database representation of date and time or keep using seconds. I'll update the summary to make it clearer.

I personally think it's OK the way we're doing now, but maybe it's worth considering for other reasons besides the support of an extended time range (or maybe that reason is enough?).

[Christian Boos]

By 2038, 32-bit ints will be history, but I doubt the trouble of supporting native date type across a wide range of databases will be gone ;-)

So I'd say we go on using seconds for storing datetime in the db, as long as there's no compelling reason to do otherwise.