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

I have a MySQL DB and I need to be able to store dates earlier then 1970 so I need a custom way to store dates. In my case, I need to be able to store dates up to the present and as far back as history goes. Obviously earlier events in history require less accuracy - days, months, even years become less important, as we do not have this historical information. The main query involving dates will be to select records between two given dates.

I have 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 actually using data in this format it becomes difficult. For example, if I want to get all records between two dates it would be like (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

or something, which seems like a right pain. I could store se开发者_如何学Cconds before/after 0 AD, but that would take up way too much data! 2011 = 6.4 billion seconds since 0 AD. Does anybody have any ideas for this problem?

I would store the number of days since a given day. There are various dates that can be used as the zero day. These are generically known as Julian days see wikipaedia

As you are storing dates from 0 AD the I would look at the Chronological Julian Day (CJD) so all your values are positive.

As a side effect this means that you only need to look at dealing with different calendars ( e.g. when they changed to Gregorian and when start of year changed or use Islamic calendars) when you convert to or from a date when you know more detail about the actual date,

If interested in dates don't bother with seconds as these complicate things unnecessary and also mean you have to deal with larger data (ie longer than a long) and time zomes etc ....

You need at least one date to create an interval. I use one date field for start_date and an interval which can be a number of seconds or a fixed interval like ENUM(day,month,year). This depends on the data that you are working with.

For DATE the supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59', but this means that although earlier values might work, there is no guarantee. I see you can use 0001-01-01 with no problems.

FOR BC values. I dont know if there is a standard but I've seen systems that state their own starting point. Lets say your data will be pre AD but not longer that 1000 BC. You can state your 0 year as -2000, you apply (year- 2000) difference for all dates and maybe even build a wrapper Date class which applies only to your project. You can assign the time as an integer an do all operations with it with no problem, especially if you dont work with days or months granularities. Current calendars are not reliable to work in the past as the accuracy of date was wrongfully calculated for centuries, and repaired by some hotfixes that can do calculations pretty difficult.

// 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

I would recommend you to keep the date format as it will allow you to do fast DATE operation directly in mySql.

Update for calculating the leap years in your class:

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

It rather depends on what kind of queries you want to run.

You could store the dates as strings YYYYMMDDHHMMSS which will sort chronologically and allow you to find dates in a range. See also ISO 8601 http://en.wikipedia.org/wiki/ISO_8601