CODEWITHSUNDEEP
mysqldatetime
Dmytro Krasun
Dmytro Krasun

Reputation: 1742

How to store out of default range dates in MySQL database?

I need to store dates in MySQL from 6000 years BC till now. But I can't use DateTime type for this:

MySQL reference: "The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'"

From 6000 BC to now in seconds (60 * 60 * 60 * 24 * 31 * 12) * 8000 = 15427584000000. Now I think about storing dates using BigInteger (like TimeStamp) and all time convert this to normal date.

What can you advice me? Maybe, some solutions already exist?

Upvotes: 3

Views: 652

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39961

You could store it in two columns, one for year having positive values as AC and negative as BC, that way there is no real limit on how far back you can go, and you're storing numbers as numbers so it's a space saver. And it is fast to search and order on.

The second column could be a normal date for those dates that are actually possible to represent as a date making all dates after 1000 AC possible to pinpoint with day and time. This second column should allow for null values.

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

You could go for a CHAR(20) data type formatted like:

  • first letter denotes the era alphabetically (A for before christ and B for after)
  • the rest of the 19 characters are the datetime in the format you mentioned (ymd h:m:s)

This way, you will also have sort and indexed search on that column.

Having a BIGINT you will have to take a reference datetime to which all your dates will be relative to. Also, transforming from that timestamp to datetimes will pretty much bloat your application code

Upvotes: 2

Related Questions