search

DOMjudge / domjudge

DOMjudge programming contest jury system
https://www.domjudge.org
GNU General Public License v2.0
709 stars 248 forks source link

store times in UTC #121

Closed eldering closed 10 years ago

eldering commented 10 years ago

We should store all timestamps in the database in UTC.

There is currently no timezone info associated to timestamps, so when one is running DOMjudge in a multiple timezone contest (e.g. USA-SER) or reimporting data from a contest run in a different timezone, then timestamps become ambiguous and could conflict with timestamps from other sources, such as from the filesystem. Switching to UTC, and using the system configured timezone addresses this issue.

eldering commented 10 years ago

Imported from trac issue 121. Created by eldering on 2013-06-22T10:23:57, last modified: 2013-06-26T16:50:01

eldering commented 10 years ago

Trac comment by xtsimpouris on 2013-06-26 16:50:01:

Have you considered UNIX timestamp?

A. We don't care about querying time spans using mySQL's date functions (BETWEEN(), DATE_ADD etc.) B. It's easy to use, just an integer, that changes only at the presentation level according to current date time zone settings, or user's settings, really easy with PHP C. Better performance (http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/)

thijskh commented 10 years ago

We discussed this a bit and came to the conclusion that if we want to add timezones, switching to unix epoch may indeed be the best way to do that. It will require quite some database changes but it indeed has the advantages above, with (A) augmented to say that epoch /does/ support operations like < and >.

If we add an epoch best add subsecond precision at the same time; make it an int containing the timestamp *1000.

eldering commented 10 years ago

Implemented in e2f9169923887a7170400d67aa50a161aa9e01c7 and following commits.