Compare creation dates of things that may have been made before christ

Question

I'm thinking about making a project in a database with a large amount of objects / people / animals / buildings, etc.

The application would let the user select two candidates and see which came first. The comparison would be made by date, or course.

MySQL only allow dates after 01/01/1000.

If one user were to compare which came first: Michael Jackson or Fred Mercury, the answer would be easy since they came after this year.

But if they were to compare which came first: Tyranosaurus Rex or Dog, they both came before the accepted date.

How could I make those comparisons considering the SQL limit?

I didn't do anything yet, but this is something I'd like to know before I start doing something that will never work.

THIS IS NOT A DUPLICATE OF OTHER QUESTIONS ABOUT OLD DATES.

In other questions, people are asking about how to store. It would be extremely easy, just make a string out of it. But in my case, I'd need to compare such dates, which they didn't ask for, yet.

I could store the dates as a string, using A for after and B for before, as people answered in other questions. There would be no problem. But how could I compare those dates? What part of the string I'd need to break?

Just because something is a date, it doesn't mean that it has to be stored as a date. A string will do just fine. You can use whatever you want to make use of it once you pull it out. This is especially true when you're talking about billions of years. — durbnpoisn, Dec 30 '15 at 18:40
You may need to create your own "custom datatype" .. basically, the question you're asking is "how do I store dates prior to 0 AD ? — Ditto, Dec 30 '15 at 18:40
Possible duplicate of [Best way to handle dates prior to 1000 A.D. in MySQL?](http://dba.stackexchange.com/questions/7077/best-way-to-handle-dates-prior-to-1000-a-d-in-mysql) — Pekka, Dec 30 '15 at 18:41
Gordon, in this case dogs would be years 40,000 B.C. Something like `01/01/-40000` — Phiter, Dec 30 '15 at 18:41
@durbnpoisn: considering he's planning on doing math on the "dates", storing them as a string is probably still a "bad idea". Breaking it out to different numeric pieces may work better in the long run (ie think year, month, whatever ..) — Ditto, Dec 30 '15 at 18:42
Obviously the correct solution to this is subscribing to [creationism.](https://en.wikipedia.org/wiki/Creationism) Nothing beyond 6000 AD — Pekka, Dec 30 '15 at 18:42
Possible duplicate of [How to store very old dates in database?](http://stackoverflow.com/questions/18654781/how-to-store-very-old-dates-in-database) — Pekka, Dec 30 '15 at 18:42
Possible duplicate of [Does MySQL support historical date (like 1200)?](http://stackoverflow.com/questions/2487543/does-mysql-support-historical-date-like-1200) — Pekka, Dec 30 '15 at 18:43
Actually, it's "how do I store dates prior to 1000-01-01 AD?". — Dan Bracuk, Dec 30 '15 at 18:43
There come people marking my stuff as duplicate, like if I didn't do any research before asking this. — Phiter, Dec 30 '15 at 18:44
Well, it indeed *does* look like you didn't any research before asking this. How do the linked duplicates differ from your question? — Pekka, Dec 30 '15 at 18:45
@ditto. I agree. I like the other suggestion of breaking into separate pieces. It goes right along the lines of what I was suggesting. — durbnpoisn, Dec 30 '15 at 18:45
Then consider picking one of the storage solutions on offer, for example [this one](http://dba.stackexchange.com/a/7082/3362), and asking *specifically* about how to compare dates stored using it. — Pekka, Dec 30 '15 at 18:48
Storing dates as a string is not an option. There are much better alternatives around, like the one I linked to in my previous comment, or [this guy's suggestion](http://stackoverflow.com/a/18655735/187606) (Having a BIGINT with the number of seconds since, or before, 1970). — Pekka, Dec 30 '15 at 18:51
Ok, what if i store as seconds? They would remain the same, always. In 24 hours the number of seconds would have increased in 86,400. So the comparison wouldn't be as precise. — Phiter, Dec 30 '15 at 18:53

score 3 · Accepted Answer · answered Dec 30 '15 at 18:59

3

You could take a signed BIGINT field and use it as a UNIX timestamp.

A UNIX timestamp is the number of seconds that passed since January 1, 1970, at 0:00 UTC.

Any point in time would simply be a negative timestamp.

If my amateurish calculation is correct, a BIGINT would be enough to take you 292471208678 years into the past (from 1970) and the same number of years into the future. That ought to be enough for pretty much anything.

That would make dates very easy to compare - you'd simply have to see whether one date is bigger than the other.

The conversion from calendar date to timestamp you'd have to do outside mySQL, though.

Depending on what platform you are using there may be a date library to help you with the task.

answered Dec 30 '15 at 18:59

Pekka

442,112
142
972
1,088

I have just added a similar answer, I think it would be more precise, what do you think? – Phiter Dec 30 '15 at 19:04
I don't see how using days instead of seconds would be more *precise*, but it might be easier to implement the calculations. However, it could be that there is a date library for your programming language that can handle very big timestamps. In that case going with the seconds *could* be both more precise and easier to do. In general I think both ways are fine – Pekka Dec 30 '15 at 19:06
Yeah, sorry. It would indeed not be more precise. But would reduce in a great scale the big int value. But the hours/seconds are indeed not relevant. – Phiter Dec 30 '15 at 19:08
@Phiter yeah, either way works. I did check the scale, I think you have 292471208678 years even when using seconds, so no problem. I'd go with whatever is easier to implement on your end – Pekka Dec 30 '15 at 19:08
2

I would think that 292 billion years would be good enough, especially since the big bang was only 13.82 billion years ago. – EvilBob22 Dec 30 '15 at 22:30

score 2 · Answer 2 · answered Dec 30 '15 at 19:19

Why deal with static age at time of entry and offset?
User is going to want to see a date as a date anyway
Complex data entry

Three fields

year  smallint (good for up to -32,768 BC)
month tinyint 
day   tinyint

if ( (y1*10000 + m1*100 + d1) > (y2*10000 + m2*100 + d2) )

Phiter · Answer 3 · 2015-12-30T19:08:53.657

OK I had an idea.

Store the age in days, since the hours/seconds are irrelevant for this case.

Christ's age in days: -2015 * 365.

Dog's age in days: -40000 * 365.

In order to make precise calculations, I'd only need an extra field with the date I have added the values. Then add to the "age in days" the difference in days from the day I have added the register, from the day the user is making the comparison.

For example:

Dog's age has been added in 29/12/2015 and the age in days is -40000 * 365.

User is making a comparison on day 29/01/2016.

The difference in days between the two dates is 31 days.

So dog's age in days should be -40000 * 365 - 31.

Using an unsigned big int can do the trick.

Thanks to Pekka for suggesting using negative numbers for any date before the current date.

Compare creation dates of things that may have been made before christ

3 Answers3