Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.mysql > #5379

Re: Storing dates

Date 2014-06-21 13:09 +0200
From Luuk <luuk@invalid.lan>
Newsgroups comp.databases.mysql
Subject Re: Storing dates
References (1 earlier) <att9q99g56phlhcf34vhs52i7fgdvfi2ak@4ax.com> <1v5cqtux7fm2t$.14sqgdhtzf3vw.dlg@40tude.net> <XnsA3533BF035EAdougmilmaccom@78.46.70.116> <ppOdnbD7S_CF0DjOnZ2dnUVZ_rydnZ2d@posted.internetamerica> <6164852.CzF7jrs0IF@PointedEars.de>
Message-ID <5o5g7b-efj.ln1@luuk.invalid.lan> (permalink)

Show all headers | View raw

On 21-6-2014 12:15, Thomas 'PointedEars' Lahn wrote:
> [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>
>


But here is says:
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'.
http://dev.mysql.com/doc/refman/5.6/en/datetime.html

Which makes it strange that a year can only between 1901 en 2155


and also this seems to produce the right answer:
MariaDB [test]> select adddate(now(), INTERVAL -1013 YEAR);
+-------------------------------------+
| adddate(now(), INTERVAL -1013 YEAR) |
+-------------------------------------+
| 1001-06-21 13:08:31                 |
+-------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select year(adddate(now(), INTERVAL -1013 YEAR));
+-------------------------------------------+
| year(adddate(now(), INTERVAL -1013 YEAR)) |
+-------------------------------------------+
|                                      1001 |
+-------------------------------------------+
1 row in set (0.01 sec)

MariaDB [test]>

Back to comp.databases.mysql | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Putting it all together richard <noreply@example.com> - 2014-06-20 10:20 -0400
  Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 08:11 -0700
    Re: Putting it all together richard <noreply@example.com> - 2014-06-20 11:24 -0400
      Re: Putting it all together Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-20 17:15 +0000
      Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 20:01 -0700
  Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 08:21 -0700
  Re: Putting it all together Norman Peelman <npeelman@cfl.rr.com> - 2014-06-20 21:06 -0400
  Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 03:02 +0000
  Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 03:09 +0000
  Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 20:13 -0700
    Re: Putting it all together richard <noreply@example.com> - 2014-06-20 23:34 -0400
      Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 20:48 -0700
      Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 21:00 -0700
      Re: Putting it all together Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-21 04:22 +0000
        Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 22:11 -0700
          Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 22:39 -0700
        Re: Putting it all together gordonb.zp2md@burditt.org (Gordon Burditt) - 2014-06-21 04:15 -0500
          Storing dates (was: Putting it all together) Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-21 12:15 +0200
            Re: Storing dates Luuk <luuk@invalid.lan> - 2014-06-21 13:09 +0200
            Re: Storing dates richard <noreply@example.com> - 2014-06-21 11:10 -0400
              Re: Storing dates Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-21 17:42 +0000
                Re: Storing dates richard <noreply@example.com> - 2014-06-21 14:28 -0400
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 12:04 -0700
                Re: Storing dates Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-21 19:06 +0000
                Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-21 22:06 +0100
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-21 17:09 -0400
                Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-21 22:33 +0100
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-21 17:49 -0400
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-22 14:38 +0200
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 14:31 -0700
                Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-21 22:44 +0100
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-22 14:59 +0200
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-23 05:54 +0000
                Re: Storing dates Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 23:19 +0000
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 08:53 +0200
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-22 08:03 +0000
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-22 09:29 -0400
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-22 14:24 +0000
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-22 10:32 -0400
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-22 14:45 +0000
                Re: Storing dates Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-22 19:44 +0000
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-22 08:26 -0700
                Re: Storing dates gordonb.q26od@burditt.org (Gordon Burditt) - 2014-06-22 10:00 -0500
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 16:47 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 13:55 -0400
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-22 12:31 -0700
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-24 22:27 +0200
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-24 13:41 -0700
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-24 20:29 -0400
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-25 08:17 +0200
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-25 06:31 +0000
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-25 01:25 -0700
                Re: Storing dates Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-25 12:02 +0000
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-25 12:17 +0000
                Re: Storing dates Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-22 19:52 +0000
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 21:52 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 14:05 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 21:56 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 22:46 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 08:11 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-23 02:37 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 11:10 +0200
                Re: Storing dates Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-23 06:50 -0700
                Re: Storing dates richard <noreply@example.com> - 2014-06-23 11:13 -0400
                Re: Storing dates Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-23 09:13 -0700
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 22:33 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 23:04 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 08:12 +0200
                Re: Storing dates Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2014-06-23 16:45 +1000
                Re: Storing dates richard <noreply@example.com> - 2014-06-23 07:58 -0400
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-23 09:50 -0700
                Re: Storing dates Christoph Michael Becker <cmbecker69@arcor.de> - 2014-06-23 19:02 +0200
                Re: Storing dates Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2014-06-24 12:00 +1000
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-24 22:38 +0200
                Re: Storing dates Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2014-06-25 09:03 +1000
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-24 20:27 -0400
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-25 17:15 +0200
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-25 11:43 -0400
                Re: Storing dates Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2014-06-25 11:34 +1000
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-25 08:20 +0200
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-22 15:30 -0700
                Re: Storing dates "Peter H. Coffin" <hellsop@ninehells.com> - 2014-06-22 20:10 -0500
          Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 03:34 -0700
            Re: Putting it all together "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-21 12:27 +0000
              Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 08:25 -0700
      Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 10:23 +0000
      Re: Putting it all together bill <william@TechServSys.com> - 2014-06-21 07:03 -0400
        Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 15:11 +0000
      Re: Putting it all together "Peter H. Coffin" <hellsop@ninehells.com> - 2014-06-21 10:57 -0500
      Re: Putting it all together Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 08:45 +0200

csiph-web