List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 16 1999 10:49am
Subject:Re: Saving dates as YYYYMMDD
View as plain text  
On Wed, 1999-09-15 20:12:17 +0200, Christian Mack wrote:
> "Daniel E. White" wrote:
[...]
> > +------------+------------+---------------------+
> > | date_field | time_field | datetime_field      |
> > +------------+------------+---------------------+
> > | 1999-09-14 | 20:49:01   | 1999-09-14 20:49:01 |
> > | 1999-09-14 | 356:49:01  | 1999-09-14 20:49:01 |
> > +------------+------------+---------------------+
[...]
> > Anyone know what that 356 is ?  It sure is NOT Julian day.
[...]
> The 365 are houres!
> The TIME field has a valid range from '-838:59:59' to '838:59:59'
> and it correctly uses 14 as days.
> So it calculates 14 days + 20 h 49min 01 sec = 356 h 49 min 01 sec.
> Months and years are skipped, because out of the valid TIME range.

At least for MySQL V3.22.19b this explanation can't be right, as my
own tests just seggest that it's a bug.

See what I tried, using MySQL V3.22.19b:

CREATE TABLE tmp (
   s  CHAR(19), s_d  DATE, s_t  TIME,
   dt DATETIME, dt_d DATE, dt_t TIME 
);

INSERT INTO tmp(s) VALUES
 ( '1999-09-14 20:49:01' )
,( NOW() )
,( CURDATE() )
,( CURTIME() )
,( '1 20:49:01' )
,( '2 20:49:01' )
,( '3 20:49:01' )
,( '13 20:49:01' )
,( '14 20:49:01' )
,( '15 20:49:01' );

SELECT * FROM tmp;
+---------------------+------+------+------+------+------+
| s                   | s_d  | s_t  | dt   | dt_d | dt_t |
+---------------------+------+------+------+------+------+
| 1999-09-14 20:49:01 | NULL | NULL | NULL | NULL | NULL |
| 1999-09-16 12:05:10 | NULL | NULL | NULL | NULL | NULL |
| 1999-09-16          | NULL | NULL | NULL | NULL | NULL |
| 12:05:10            | NULL | NULL | NULL | NULL | NULL |
| 1 20:49:01          | NULL | NULL | NULL | NULL | NULL |
| 2 20:49:01          | NULL | NULL | NULL | NULL | NULL |
| 3 20:49:01          | NULL | NULL | NULL | NULL | NULL |
| 13 20:49:01         | NULL | NULL | NULL | NULL | NULL |
| 14 20:49:01         | NULL | NULL | NULL | NULL | NULL |
| 15 20:49:01         | NULL | NULL | NULL | NULL | NULL |
+---------------------+------+------+------+------+------+

UPDATE tmp SET s_d=s, s_t=s, dt=s, dt_d=dt, dt_t=dt;
SELECT * FROM tmp;
+---------------------+------------+-----------+---------------------+------------+----------+
| s                   | s_d        | s_t       | dt                  | dt_d       | dt_t  
  |
+---------------------+------------+-----------+---------------------+------------+----------+
| 1999-09-14 20:49:01 | 1999-09-14 | 20:52:37  | 1999-09-14 20:49:01 | 1999-09-14 |
20:52:37 |
| 1999-09-16 12:05:10 | 1999-09-16 | 00:00:00  | 1999-09-16 12:05:10 | 1999-09-16 |
00:00:00 |
| 1999-09-16          | 1999-09-16 | 838:59:59 | 1999-09-16 00:00:00 | 1999-09-16 |
00:00:00 |
| 12:05:10            | 2012-05-10 | 12:05:10  | 2012-05-10 00:00:00 | 2012-05-10 |
00:02:40 |
| 1 20:49:01          | 0000-00-00 | 20:49:25  | 0000-00-00 00:00:00 | 0000-00-00 |
00:00:00 |
| 2 20:49:01          | 0000-00-00 | 20:49:49  | 0000-00-00 00:00:00 | 0000-00-00 |
00:00:00 |
| 3 20:49:01          | 0000-00-00 | 00:00:00  | 0000-00-00 00:00:00 | 0000-00-00 |
00:00:00 |
| 13 20:49:01         | 0000-00-00 | 20:52:13  | 0000-00-00 00:00:00 | 0000-00-00 |
00:00:00 |
| 14 20:49:01         | 0000-00-00 | 20:52:37  | 0000-00-00 00:00:00 | 0000-00-00 |
00:00:00 |
| 15 20:49:01         | 0000-00-00 | 00:00:00  | 0000-00-00 00:00:00 | 0000-00-00 |
00:00:00 |
+---------------------+------------+-----------+---------------------+------------+----------+

Row 1 and 2 show that assigning a DATETIME value to a TIME field
yields wierd wrong results, as is the case with rows 5 and up.

So I guess, this is a bug.  Solution is to avoid assigning non time
values to TIME fields.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Saving dates as YYYYMMDDClaudia M. Castaneda14 Sep
  • Re: Saving dates as YYYYMMDDMike Machado14 Sep
    • Re: Saving dates as YYYYMMDDMartin Ramsch14 Sep
  • Re: Saving dates as YYYYMMDDChristian Mack14 Sep
    • Re: Saving dates as YYYYMMDDDaniel E. White15 Sep
      • Re: Saving dates as YYYYMMDDMichael Widenius17 Sep
  • Re: Saving dates as YYYYMMDDChristian Mack15 Sep
    • Re: Saving dates as YYYYMMDDMartin Ramsch16 Sep
      • Re: Saving dates as YYYYMMDDMichael Widenius17 Sep