NOW() not updating datetime?

Question

hi there,

i have a table that looks like this: Field: msg_sent_datetime
Type: datetime

yet when i use NOW() in a php mysql insert query it is staying as all zeros?

any idea why?

Answer 1

If you're using it only at the time of INSERT, you could make the field a TIMESTAMP and set a default of CURRENT_TIMESTAMP.

Without knowing more about the code it's difficult to suggest much else.

Example with CURRENT_TIMESTAMP

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msg TEXT,
    msg_sent_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Also, if you want it to always update when there's a change to the row, you can add the ON UPDATE CURRENT_TIMESTAMP property to the table definition:

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msg TEXT,
    msg_sent_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Of course, all of this is totally dependent on switching to TIMESTAMP which you may not want

Answer 2

I dont knw exactly why, but mysql has 2 datetime functions: now() and sysdate(). Perhaps you can substitute now() with sysdate() to help you troubleshoot.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_sysdate