FUDforum: comp.lang.php » Putting it all together

Home » Imported messages » comp.lang.php » Putting it all together

Show: Today's Messages :: Polls :: Message Navigator

Storing dates (was: Putting it all together) [message #186229 is a reply to message #186228]

Sat, 21 June 2014 10:15

Thomas 'PointedEars'
Messages: 701
Registered: October 2010

Karma:

Senior Member

[F'up2 comp.databases.mysql

Gordon Burditt wrote in comp.lang.php and comp.databases.mysql:

> [“richard” wrote:]
>> Why would anyone store a date in a MySQL
>> database using any data type other than DATE or DATETIME?
>
> Believe it or not, there are (very rarely) good reasons to do that.
>
> Genealogy is one example. It has lots of dates MySQL won't accept.
> First, some people (such as English royalty) can trace their ancestry
> back before 1000 A.D., before the limit on the DATE type.

ACK.

> Second, MySQL doesn't like imprecise dates, such as only a year being
> known.

This can be solved by setting the unknown parts to 1 and have other fields
of the record specify the precision.

Also, there is the YEAR type (preferably used as YEAR(4)), but it only has a
range from 1901 to 2155:

<http://dev.mysql.com/doc/refman/5.6/en/year.html>

> Third, during a time when both the Julian and Gregorian calendars
> were in use, depending on location, you often see dates recorded
> as "5 January 1712/13" or "5 January 1712/3". You might also see
> "5 January 1712".

Same there.

> Since the Julian calendar as used in the English colonies that later
> became the United States used March 25 as the first day of the year,
> you can get ridiculous-looking records such as a child born: March
> 27, 1700, died: March 23, 1700 (not a mistake: the child lived
> almost a full year) and born: March 24, 1700, died: March 25, 1701
> (the child lived about 24 hours). Also, a date recorded as "the
> 6th day of the third month of 1699" might be referring to March or
> May.
>
> It may be best to treat all dates (birth, death, marriage, graduation,
> etc.) as ranges.

Depends.

> These should be DATE types for efficient sorting and date arithmetic.

That contradicts your premise that dates could be before 1000-01-01 CE. It
is usually the dates of birth and death of people born before that date that
are uncertain and require ranges. For example, Plato lived “[from] 428/427
or 424/423 BC[a] [to] 348/347 BC” (Wikipedia).

> It's also important to record the date(s) as stated in the source(s) as
> originally recorded, complete with accurate reproduction of spelling
> errors and in the original language, in text fields (or perhaps as an
> image). This is not redundant. You may have to do considerable research
> to set the DATE fields from the original source data that may be
> conflicting, imprecise, or ambiguous.

ACK. But that information should be stored in separate columns.

> You might need to try extening the Gregorian calendar backwards to avoid
> death before birth and other anomalies.

It has been done before. MySQL already does it, not least because the first
day of the Gregorian calendar is _not_ 0001-01-01 CE, but 1582-10-15 CE.

< https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calend ar>

This has nothing to do with PHP anymore. Please stop crossposting (without
F'up2).

PointedEars
--
Sometimes, what you learn is wrong. If those wrong ideas are close to the
root of the knowledge tree you build on a particular subject, pruning the
bad branches can sometimes cause the whole tree to collapse.
-- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom(at)94(dot)75(dot)214(dot)39>

Report message to a moderator

[Message index]

		Putting it all together By: Mr Oldies on Fri, 20 June 2014 14:20
		Re: Putting it all together By: Evan Platt on Fri, 20 June 2014 15:11
		Re: Putting it all together By: Mr Oldies on Fri, 20 June 2014 15:24
		Re: Putting it all together By: Doug Miller on Fri, 20 June 2014 17:15
		Re: Putting it all together By: Evan Platt on Sat, 21 June 2014 03:01
		Re: Putting it all together By: Jrgen Exner on Fri, 20 June 2014 15:21
		Re: Putting it all together By: Norman Peelman on Sat, 21 June 2014 01:06
		Re: Putting it all together By: Denis McMahon on Sat, 21 June 2014 03:02
		Re: Putting it all together By: Denis McMahon on Sat, 21 June 2014 03:09
		Re: Putting it all together By: Jrgen Exner on Sat, 21 June 2014 03:13
		Re: Putting it all together By: Mr Oldies on Sat, 21 June 2014 03:34
		Re: Putting it all together By: Jrgen Exner on Sat, 21 June 2014 03:48
		Re: Putting it all together By: Evan Platt on Sat, 21 June 2014 04:00
		Re: Putting it all together By: Doug Miller on Sat, 21 June 2014 04:22
		Re: Putting it all together By: Jrgen Exner on Sat, 21 June 2014 05:11
		Re: Putting it all together By: Evan Platt on Sat, 21 June 2014 05:39
		Re: Putting it all together By: gordonb.zp2md on Sat, 21 June 2014 09:15
		Storing dates (was: Putting it all together) By: Thomas 'PointedEars' on Sat, 21 June 2014 10:15
		Re: Storing dates By: Mr Oldies on Sat, 21 June 2014 15:10
		Re: Storing dates By: Doug Miller on Sat, 21 June 2014 17:42
		Re: Storing dates By: Mr Oldies on Sat, 21 June 2014 18:28
		Re: Storing dates By: Jrgen Exner on Sat, 21 June 2014 19:04
		Re: Storing dates By: Doug Miller on Sat, 21 June 2014 19:06
		Re: Storing dates By: Tim Streater on Sat, 21 June 2014 21:06
		Re: Storing dates By: Jerry Stuckle on Sat, 21 June 2014 21:09
		Re: Storing dates By: Tim Streater on Sat, 21 June 2014 21:33
		Re: Storing dates By: Jerry Stuckle on Sat, 21 June 2014 21:49
		Re: Storing dates By: Thomas 'PointedEars' on Sun, 22 June 2014 12:38
		Re: Storing dates By: Jrgen Exner on Sat, 21 June 2014 21:31
		Re: Storing dates By: Tim Streater on Sat, 21 June 2014 21:44
		Re: Storing dates By: Thomas 'PointedEars' on Sun, 22 June 2014 12:59
		Re: Storing dates By: Denis McMahon on Sat, 21 June 2014 23:19
		Re: Storing dates By: Arno Welzel on Sun, 22 June 2014 06:53
		Re: Storing dates By: Erick T. Barkhuis on Sun, 22 June 2014 08:03
		Re: Storing dates By: Jerry Stuckle on Sun, 22 June 2014 13:29
		Re: Storing dates By: Erick T. Barkhuis on Sun, 22 June 2014 14:24
		Re: Storing dates By: Jerry Stuckle on Sun, 22 June 2014 14:32
		Re: Storing dates By: Erick T. Barkhuis on Sun, 22 June 2014 14:45
		Re: Storing dates By: Denis McMahon on Sun, 22 June 2014 19:44
		Re: Storing dates By: Jrgen Exner on Sun, 22 June 2014 15:26
		Re: Storing dates By: gordonb.q26od on Sun, 22 June 2014 15:00
		Re: Storing dates By: Arno Welzel on Sun, 22 June 2014 14:47
		Re: Storing dates By: Mr Oldies on Sun, 22 June 2014 17:55
		Re: Storing dates By: Jrgen Exner on Sun, 22 June 2014 19:31
		Re: Storing dates By: Thomas 'PointedEars' on Tue, 24 June 2014 20:27
		Re: Storing dates By: Denis McMahon on Sun, 22 June 2014 19:52
		Re: Storing dates By: Arno Welzel on Sun, 22 June 2014 19:52
		Re: Storing dates By: Mr Oldies on Sun, 22 June 2014 18:05
		Re: Storing dates By: Arno Welzel on Sun, 22 June 2014 19:56
		Re: Storing dates By: Mr Oldies on Mon, 23 June 2014 02:46
		Re: Storing dates By: Arno Welzel on Mon, 23 June 2014 06:11
		Re: Storing dates By: Mr Oldies on Mon, 23 June 2014 06:37
		Re: Storing dates By: Arno Welzel on Mon, 23 June 2014 09:10
		Re: Storing dates By: Evan Platt on Mon, 23 June 2014 13:50
		Re: Storing dates By: Mr Oldies on Mon, 23 June 2014 15:13
		Re: Storing dates By: Evan Platt on Mon, 23 June 2014 16:13
		Re: Storing dates By: Arno Welzel on Mon, 23 June 2014 20:33
		Re: Storing dates By: Mr Oldies on Mon, 23 June 2014 03:04
		Re: Storing dates By: Arno Welzel on Mon, 23 June 2014 06:12
		Re: Storing dates By: Geoff Muldoon on Mon, 23 June 2014 06:45
		Re: Storing dates By: Mr Oldies on Mon, 23 June 2014 11:58
		Re: Storing dates By: Jrgen Exner on Mon, 23 June 2014 16:50
		Re: Storing dates By: Christoph Michael Bec on Mon, 23 June 2014 17:02
		Re: Storing dates By: Geoff Muldoon on Tue, 24 June 2014 02:00
		Re: Storing dates By: Thomas 'PointedEars' on Tue, 24 June 2014 20:38
		Re: Storing dates By: Jrgen Exner on Sun, 22 June 2014 22:30
		Re: Storing dates By: Peter H. Coffin on Mon, 23 June 2014 01:10
		Re: Putting it all together By: Jrgen Exner on Sat, 21 June 2014 10:34
		Re: Putting it all together By: Denis McMahon on Sat, 21 June 2014 10:23
		Re: Putting it all together By: bill on Sat, 21 June 2014 11:03
		Re: Putting it all together By: Denis McMahon on Sat, 21 June 2014 15:11
		Re: Putting it all together By: Peter H. Coffin on Sat, 21 June 2014 15:57
		Re: Putting it all together By: Arno Welzel on Sun, 22 June 2014 06:45

Previous Topic:	str_replace does not like empty quotes
Next Topic:	SplFileObject always returns an extra "last" line -- why?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 22:20:52 GMT 2024

Total time taken to generate the page: 0.04314 seconds