How can I select all records between two dates without using date fields?

Question

I have MySQL DB, and I need to be able to store dates before 1970, so I need my own way of storing dates. In my case, I need to be able to store dates to date and even in history. Obviously, earlier events in history require less accuracy - days, months, even years become less important, since we do not have this historical information. The main query related to dates will be the selection of records between two given dates.

I thought to use this format:

Year - int(6) | Month - tinyint(2) | day - tinyint (2) | time - time | AD tinyint (1) | mya - int (11)
But when it comes to the actual use of data in this format, it becomes difficult. For example, if I want to get all records between two dates, it will be similar (pseudocode, not SQL):

get all where year between minYear and maxYear
if year == minYear, month >= minMonth
if year == maxYear, month <= maxMonth
if month == minMonth, day >= minDay
if month == maxMonth, day <= maxDay
if day == minDay, time >= minTime
if day == maxDay, time <= maxTime

-, . / 0 AD, ! 2011 = 6,4 0 AD. - ?

Answer 1

. start_date , , ENUM (, , ). , .

DATE : "1000-01-01 00:00:00" "9999-12-31 23": 59: 59 ', , , , . , 0001-01-01 .

FOR BC. , , , . , AD, 1000 BC. 0- -2000, (-2000) , , Date-, . , . , ​​ , .

// lets save the 1st of January 214 BC
214-2000 = -1786
save the data as 01-01-1786
// you will probably want to save it actually as 01-01-1785 
// to compensate for the loss of the year 0

, DATE mySql.

:

if year modulo 400 is 0
   then is_leap_year
else if year modulo 100 is 0
   then not_leap_year
else if year modulo 4 is 0
   then is_leap_year
else
   not_leap_year

Answer 2

. , . , . wikipaedia

0 AD, (CJD), .

, (, ), , ,

, , , ( ) .....

Answer 3

It rather depends on what queries you want to run.

You can save dates as YYYYMMDDHHMMSS strings that will be sorted in chronological order and allow you to find dates in a range. See also ISO 8601 http://en.wikipedia.org/wiki/ISO_8601