>>>>> "Paul" == Paul DuBois <[EMAIL PROTECTED]> writes:
>> Stas writes:
>> > Hi. I have a problem with timestamp(4) data type. It doesnot work, if i
>> > write '9901' for example, it has '0000'. The timestamp(6) works correctly.
>> >
>> > And it will be very good, if you will add data type such timestamp(5) -
>> > YYYYMM.
>> >
>> > Stas
>> > [EMAIL PROTECTED]
>> >
>>
>> Hi!
>>
>> TIMESTAMP should be 12, 8 or 6. 4 can not work correctly.
Paul> Actually, TIMESTAMP(4) is documented as having a display format of YYMM.
Paul> And it does. But you cannot *insert* YYMM values as you might think.
Paul> The value you insert must be a legal date, and YYMM values are not
Paul> legal dates.
Paul> If you insert 990101, then the value retrieved will indeed be 9901.
Paul> What's happening when you insert '9901', I *think*, is that MySQL
Paul> interprets 99 as the years and 01 as the months. Still, the days part
Paul> is missing (i.e., 0), and 990100 isn't a legal date, so you get 0 as
Paul> the value that is stored.
Paul> The relevant section of the manual is 7.2.3 "Date and time types",
Paul> which says that only the formats described there are supported,
Paul> and that unpredictable results may occur if you use values in other
Paul> formats.
Paul> Later on, it says:
Paul> ---------------
Paul> Values specified as numbers should be 6, 8, 12 or 14 digits long.
Paul> If the number is 8 or 14 digits long, it is assumed to be in `YYYYMMDD'
Paul> or `YYYYMMDDHHMMSS' format and that the year is given by the first 4
Paul> digits. If the number is 6 or 12 digits long, it is assumed to be in
Paul> `YYMMDD' or `YYMMDDHHMMSS' format and that the year is given by the
Paul> first 2 digits. Numbers that are not one of these lengths are
Paul> interpreted as though padded with leading zeros to the closest length.
Paul> Values specified as non-delimited strings are interpreted using
Paul> their length as given. If the string is 8 or 14 characters long, the
Paul> year is assumed to be given by the first 4 characters. Otherwise the
Paul> year is assumed to be given by the first 2 characters. The string is
Paul> interpreted from left to right to find year, month, day, hour, minute
Paul> and second values, for as many parts as are present in the string.
Paul> ---------------
Paul> Maybe that's a bit unclear, because the second paragraph doesn't explicitly
Paul> *rule out* 2- or 4-character string values. Maybe something to that
Paul> effect should be added. Monty, any comment?
I think we should rule out 2 and 4 character values in this case.
As you pointed out, this can't be a valid date.
One 'feature' of timestamp is if you do an ALTER TABLE on it to a
larger timestamp type you will be able to retrieve the information
that was 'hidden' previously.
Regards,
Monty
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
