How can I select all records between two dates without using date fields?
- by Hayden Bech
Hi,
I have a MySQL DB and I need to be able to store dates earlier then 1970 - in my case, as early as 0 AD and earlier too, so I need a custom way to store dates. I have thought to use this format:
Year - int(6) | Month -int(2) | day - int (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 seconds before/after 0 AD, but that would take up way too much data! 2010 (EDIT: 2011) = 6.4 billion seconds since 0 AD. Does anybody have any ideas for this problem?
