MySql N00b, problem with timestamp

Question

There are some other questions where people have problems with timestamp being all zeros. I have checked them and this is not a duplicate.

I declare a table like this:

CREATE TABLE  `my_db`.`my_table` (
  `time_stamp` timestamp NOT NULL,
  `author` varchar() NOT NULL,
  `text` text NOT NULL,
  `md5` int(11) NOT NULL,
  PRIMARY KEY (`time_stamp`)
) ;

I also have a second table which will have a timestamp as primary key and they should have the same value.

Coding in Delphi, I use SELECT CURRENT_TIMESTAMP which returns something like `'19/6/2010 4:56:17 AM' which I then use in an INSERT statement. The INSERT succeeds, but the timestamp is all zer0s.

What am I doing wrong?

---

kk, here's the INSERT code ...

  sqlCommand := 'INSERT INTO my_db.my_table(time_stamp, author, text, md5) VALUES ("' 
                   + timestamp + 
                    '", "mawg", ' +
                    '"Hello, world"' +
                    0 +
                    '");';
  Result := DoSQlCommandWithNoResultSet(sqlCommand, AdoConnection);

---

[Edit] Insertion will be extremely low rate, one entry every few weeks or maybe months, so I am happy with timestamp as primary key. I am keeping "versions" of things, so timestamp makes sense to me.

I am begging to think that this is an ADO problem, although I would expect ADO to just "pass through". I don't see any other solution. In a console, the output is "correct", but when run through ADO in Delphi then it is wrong

---

Hmm, can I specify to MySql how it ought to format its dates?

Answer 1

Have a look at MySQL date functions. They are very extensive and allow a high flexibility.

Besides all that, I would recommend re-thinking your table structure. A timestap as a primary key is not exactly what you want. When you have high traffic, it CAN happen, that the timestamp is the same. Also if you are saving 2 or more records in a row, the timestamp will be the same. Furthermore, your MD5 column is set to int(11). MD5 hashes use mixed characters, so i would rather go with varchar(32).

CREATE TABLE  `my_db`.`my_table` ( 
  `id` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL,
  `author` varchar(100) NOT NULL,
  `text` text NOT NULL,
  `md5` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ;

Answer 2

AFAIR 19/6/2010 4:56:17 AM is not a valid date format for MySQL date types. You should convert it to 2010-06-19 04:56:14 (see doc).