4

I just started working on application for astronomy and I need to store very ancient dates.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

But what about dates before year 1000?

I can put that into separate year, month, day fields, with INT datatype.

Is this good idea, or there is better way to store that?

Kamil
  • 13,363
  • 24
  • 88
  • 183
  • I'd really stay away from date and timestamp types, since the earliest timestamp (timestmap=0) goes back to 1970s... use an INT, I think that's okay. – Taher Mar 27 '14 at 15:38
  • For an astronomy application do you really need month and day precision? Wouldn't the year be enough? – StephaneM Mar 27 '14 at 15:43
  • Do you calculate anything with the dates, e.g. the difference between two dates? Do you always have the precise date available? If it's just for display, even a string might be fine. If not, then you will have to consider Julian and Gregorian dates and whatever. This said: It's obvious that the date type is inappropriate, but you will have to think it through, to determine what data type (and: one column or more?) would be appropriate for your case. – Thorsten Kettner Mar 27 '14 at 15:48
  • @StephaneM: They definitely seem to be required. Because position of starts change as the dates change and not just by a year change. – Ravinder Reddy Mar 27 '14 at 15:49
  • 1
    Do you considered the possibility of storing data shifted of 2000 yrs? e.g. if you refer to 14AC-03-27 you store in the database 2014-03-27. Also, what is the definition of a data before the gregorian calendar? – Antonio Ragagnin Mar 27 '14 at 16:01
  • Tip: Watch this before you even entertain the notion of writing custom code for handling dates. https://www.youtube.com/watch?feature=player_detailpage&v=-5wpm-gesOY&list=TLfudMVlMKosR5Y38oD6GaeZoyzfqE1IEc – JohnFx Mar 29 '14 at 05:49

1 Answers1

3

The convention in most astronomical applications is either to use a calendar which is Julian prior to 1582, including a year zero, and Gregorian thereafter, or to use a Julian day, of which there are several types, the most common being the number of solar days that have elapsed since solar noon in Greenwich on January 1, 4713 BC.

Neither are well represented as conventional datetimes and should probably be stored as floating-point numbers.

Stuart Caie
  • 2,803
  • 14
  • 15