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

Posted by Hayden Bech on Stack Overflow See other posts from Stack Overflow or by Hayden Bech
Published on 2011-01-05T13:36:27Z Indexed on 2011/01/05 13:53 UTC
Read the original article Hit count: 206

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query