4

According to the MySQL reference manual, the year type is restricted to at most 9999 if I understand it correctly. (See http://dev.mysql.com/doc/refman/5.7/en/year.html).

So how then do you store large future or past dates? For example 6000BC or what got me asking, 21974AD? I have tried to find examples of using larger dates but haven't been able to and wondered if it's at all possible to do so.

(Small background to this, was testing some Java date-time validations, and found trying to insert record for the year 21974 into the database, causes an exception, due to the previously mentioned limitation)

  • 1
    Do you want to record complete dates or just years? – ypercubeᵀᴹ Dec 28 '16 at 12:51
  • I was thinking years. – Bheku Msimanga Dec 28 '16 at 13:15
  • 3
    If you only need years: use an integer type or DECIMAL(n), as big as you need. – joanolo Dec 28 '16 at 21:20
  • @joanolo I see, then how would one do date calculations, given that the year is just now a DECIMAL(n) instead of a type associated with Dates? Should I accept that for large years (>9999) that it's not possible within MySQL without introducing some functions or stored procs? – Bheku Msimanga Dec 29 '16 at 8:23
  • 3
    If you only need years, you actually don't need date calculations ;-) Year calculations would suffice, and you can do them using integers (or numeric, decimal). AFAIK, you cannot do date calcuations for years >9999 in mySQL. – joanolo Dec 29 '16 at 8:24
4

If you need to work only with years, you can represent them by means of any integer (that is: from smallint to bigint (tinyint is too small) or numeric/decimal) type of a size big enough for your needs.

If you really need date calculations involving years > 9999, you're out luck. MySQL documentation about dates clearly states that

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

If your needs for date ranges are really critical, and you have the possibility of choosing your database, PostgreSQL allows much larger ranges (4713 BC to 5874897 AD for dates, 4713 BC to 294276 AD for timestamps).

Other databases (Firebird, Oracle and MS SQL Server for instance) have date possible values similar to those of mySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.