keep 19999 year into database

Question

I have the following date value, that want to keep into MySQL database. Table field is DATETIME datatype that does not allow keep such value. I get the following error message when try to insert a record with:

Data truncation: Incorrect datetime value: '19999-12-21 01:00:00'

Tue Dec 21 01:00:00 EET 19999
19999-12-21 01:00:00

Wow. You're expecting your system to be around for a while then. — Bathsheba, Jul 01 '15 at 12:15
can I somehow round/truncate 19999 to valid DATETIME year in Java ? — Dumitru Gutu, Jul 01 '15 at 12:17
use your own type for column, some long numeric type and hold there timestamp value — Andremoniy, Jul 01 '15 at 12:18
@Bathsheba you might be surprised how long code may live if written well. — Thorbjørn Ravn Andersen, Jul 01 '15 at 12:22
Are you sure you want 19,999 for the year? Isn't 9999 big enough? — Gordon Linoff, Jul 01 '15 at 12:23
According to https://dev.mysql.com/doc/refman/5.0/en/datetime.html "The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. " — vefthym, Jul 01 '15 at 12:27
@ThorbjørnRavnAndersen - That may be true. However, I doubt that they will still be using computers and programs as we currently understand them in ~18,000 yeas time. (Except as museum pieces.) — Stephen C, Jul 01 '15 at 12:29
@StephenC you never know... There is a lot of COBOL in the bank vaults. — Thorbjørn Ravn Andersen, Jul 01 '15 at 12:31
Think what kind of computers we were running 18 thousand years ago ... and extrapolate. But you are right. I will (literally) never know. — Stephen C, Jul 01 '15 at 12:36
so I have a datetime value in unix time format that come from another system that i need convert and keep into database. the unix time value is: 568970892000 — Dumitru Gutu, Jul 01 '15 at 13:05

Gordon Linoff · Answer 1 · 2015-07-01T12:33:13.563

4

There are reasons why one might want a year beyond 9999. For instance, if you are calculating astronomical events, you might want to know the next 1,000 times that Halley's comet will return to earth. Or, if you are fascinated by arcane religious calendars, you might be amused by the cycles of Jewish holidays through the seasons, or how often Ramadan starts on Christmas.

Unfortunately for this purpose, most databases where designed with business needs in mind, not astronomy or archeology or other domains. If you really need dates beyond the range, I would suggest setting up your own calendar system. One method is to create a bigint column and count the number of days since some canonical date, such as '0001-01-01'. Another is to store the dates as strings in a 'YYYYY-MM-DD' format.

Or, perhaps you just have a typo and 1999 is sufficient.

edited Jul 01 '15 at 12:33

answered Jul 01 '15 at 12:27

Gordon Linoff

1,242,037
58
646
786

Eloquent answer although you'd simply use seconds for such events. Years, days, etc. are not constant: yesterday's leap second epitomises that. – Bathsheba Jul 01 '15 at 12:32
@Bathsheba . . . Those interested in arcane calendar issues would probably be interested in John Skeet's top post: http://stackoverflow.com/questions/6841333/why-is-subtracting-these-two-times-in-1927-giving-a-strange-result/6841479#6841479. And that's on a much shorter time frame. – Gordon Linoff Jul 01 '15 at 12:34

score 2 · Answer 2 · answered Jul 01 '15 at 12:27

2

This is not a limit in Java but a limit in MySQL. The MySQL date types do not go that far into the future (https://dev.mysql.com/doc/refman/5.1/en/datetime.html)

Either switch databases or do it yourself by storing an appropriate counter you convert yourself.

If you switch databases, PostgreSQL 9 goes this far and then some (294276 AD). See http://www.postgresql.org/docs/9.1/static/datatype-datetime.html for details.

answered Jul 01 '15 at 12:27

Thorbjørn Ravn Andersen

73,784
33
194
347

1

Note that you may also just compare the date to a known date representing the largest date the database can hold, and if larger than that use the known date instead, – Thorbjørn Ravn Andersen Jul 01 '15 at 12:33

keep 19999 year into database

2 Answers2