0

I read a question about historical dates (does-mysql-support-historical-date-like-1200) and there is an interresting response that talk about the use of "datestamp", an equivalent of timestamp but with day as interval.

To achieve this it's said that we need to handle leap years and other stuffs. Is this really matters ?

What if we assume that all months have 31 days as well at insert time than at fetch time ?

ie.:

  • 0856-11-05 -> 5 + 11*31 + 856*(12*31) = 318778;
  • 318778 / 372 = (int)856, 318778 - (856*372) = 346, 346 / 31 = (int)11, 346 - (11*31) = 5

Eventually we can check at insert time if the date is possible...

What do you think about that ?

Thanks in advance!

Community
  • 1
  • 1
Sébastien
  • 1,553
  • 2
  • 18
  • 31
  • 1
    If your app can tolerate dates that might be a couple of days (or weeks) off, then there's little point in spending time on the complex and correct solution? :-) – Sergio Tulentsev Dec 19 '11 at 11:19
  • 1
    The exact code is published in many places search for Julian daycount - however note the different dates at which the year changed of year at least in England – mmmmmm Dec 19 '11 at 11:28
  • @SergeiTulentsev, in what is that an incorrect solution? If a user set a date, I'll always fetch the right date, like my exemple. The user can set a date that did not exist (ie. 2011/02/31) but we can check it at insert time. – Sébastien Dec 19 '11 at 11:48
  • `DATE` and `DATETIME` support date ranges from 1000-01-01 to 9999-12-31. – O. Jones Dec 19 '11 at 12:50
  • If your historic dates are in that range, it makes sense to use mySQL's built in calendar code. But you didn't specify the range you need. If you need a wider range, you may want to figure out how to use the Java Calendar class, and serialize those values into your DBMS. – O. Jones Dec 19 '11 at 12:56

0 Answers0