List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 4 1999 9:05am
Subject:TIMESTAMP bug in 3.22.20a/linux? and DATETIME question.
View as plain text  
I don't know if this is a bug or the way the TIMESTAMP is supposed to work, but
I felt it worthy of bringing to someone's attention:

I had this in a table:
  date TIMESTAMP(14) NOT NULL,     # date of insert or last update
(YYYYMMDDHHMMSS)

This was used as the date the record was created, and was to stay the same
always.
It did that.
UPDATES were NOT changing this field's value and I was passing in null during
the INSERT

Later I created a second column via ALTER TABLE:
  last_update TIMESTAMP(14) NOT NULL,

which was supposed to be used to monitor the last updates of the record.
Upon any "UPDATE" SQL call, I would explicitly say, "last_update = null".

An odd thing started to occur. All my "date" columns were then updating as well!
SO both columns had the same stamp in them.
For shits and giggles, I removed the "last_update = null" in my UPDATEs, and
then "date" started to automatically change.... grrr.

I later noticed in the documentation that TIMESTAMP should be changing upon ANY
INSERT or UPDATE
(which again, it hadn't been until this scenario and I didn't want it to do
unless I passed in a null explicitly during an UPDATE).

[root=ttyp1]1:45am:{/home/majordom/lists}> mysql --version
mysql  Ver 9.31 Distrib 3.22.20a, for pc-linux-gnu (i686)

-----------------------

Now for my questions:

Since using TIMESTAMP just hosed my data by updating all these records and
changing dates, I wanted to use the DATETIME type. Can I simply set them to
"null" and let mySQL put the correct date format in there like TIMESTAMP does?
Or do I have to manually make that crazy "YYY-MM-DD HH:MM:SS" string. Basically
by using an ALTERTABLE, I could simply change the date TIMESTAMP to DATETIME and
life would be good again if passing in a null would set it (for the new records
of course, since my old ones are ruined now).

Which leads me to my second question, why the heck are the two types so
divergent? Why not make DATETIME the same "YYYYMMDDHHMMSS" format as the
TIMESTAMP column so that I can compare or subtract them or whatever?!


Daevid Vincent.
http://www.WildTangent.com

Thread
TIMESTAMP bug in 3.22.20a/linux? and DATETIME question.Daevid Vincent4 May
  • Re: TIMESTAMP bug in 3.22.20a/linux? and DATETIME question.johan.engstrom4 May
  • Re: TIMESTAMP bug?Daevid Vincent7 May
  • Re: TIMESTAMP bug?Fred Read7 May