36

I've got a table that looks like this:

CREATE TABLE IF NOT EXISTS `Hosts` (
`id` int(128) NOT NULL AUTO_INCREMENT,
`IP` varchar(15) NOT NULL DEFAULT '',
`Port` varchar(5) NOT NULL DEFAULT '',
`Password` varchar(32) NOT NULL DEFAULT '',
`Username` varchar(32) NOT NULL DEFAULT '',
`Tid` varchar(32) NOT NULL DEFAULT '',
`EquipType` varchar(64) NOT NULL DEFAULT '',
`Version` varchar(128) DEFAULT NULL,
`Status` varchar(10) NOT NULL DEFAULT '',
`Location` varchar(128) NOT NULL DEFAULT '',
`Lastconnection` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`Lastbackup` date NOT NULL DEFAULT '0000-00-00',
`Backupstatus` varchar(64) NOT NULL DEFAULT '',
`Backupmsg` text,
`Backupfile` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `IP` (`IP`),
KEY `Tid` (`Tid`),
KEY `EquipType` (`EquipType`),
KEY `Status` (`Status`),
KEY `Lastbackup` (`Lastbackup`),
KEY `Backupstatus` (`Backupstatus`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=716 ;

In my mind, this means that any time a row is updated, the field 'Lastconnection' should be stamped with the current timestamp. However, when I run something like:

update Hosts set Backupstatus = 'FAIL',  Backupmsg = 'Connection timed out' where Tid = 'SITE001'

Lastconnection stays '0000-00-00 00:00:00'. There's either a database issue I'm not seeing, or I'm completely misunderstanding the 'ON UPDATE CURRENT_TIMESTAMP' clause.

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
coding_hero
  • 1,759
  • 3
  • 19
  • 34
  • What is your version? I only ask because of this bug report: http://bugs.mysql.com/bug.php?id=28904 – Horus Apr 06 '11 at 18:41
  • That the ts column as you report is filled with '0000-00-00 00:00:00' does contradict my guess it could have smth. to do with the NO_ZERO_DATE mode, but take a look at http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_no_zero_date – initall Apr 06 '11 at 18:54
  • I think that may be it. Many rows are being stamped properly, but some may have duplicate values. I'll try an upgrade. – coding_hero Apr 06 '11 at 18:58
  • Using null for the datetime column in the query worked for me. – shasi kanth Apr 09 '14 at 11:37
  • 2
    according to https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html the current timestamp will update ONLY if one of the values in the row changes (i.e. if all the values stay the same, the `lastconnection` column will not update either even if you run `UPDATE` query) – ierdna Mar 29 '17 at 14:28

5 Answers5

35

Have you tried to use null for that field when updating?

You could also try setting default value to CURRENT_TIMESTAMP, rather than 0000-00-00 00:00:00.

Nevertheless, whenever I want to have creation and update time I always use the following:

...
CREATED timestamp NOT NULL default '0000-00-00 00:00:00',
UPDATED timestamp NOT NULL default now() on update now(),
....

I use now(), because is an alias for CURRENT_TIMESTAMP and it is shorter. At the end, table structure gets CURRENT_TIMESTAMP, so don't worry.

The trick with CREATED field is to remember to use null on both fields for INSERT statements, for UPDATE statements it is not required:

INSERT INTO mytable (field1, field2, created, updated)
VALUES ('foo', 'bar', null, null);
newfurniturey
  • 37,556
  • 9
  • 94
  • 102
barranquero
  • 350
  • 3
  • 7
  • More explanation on this... http://dev.mysql.com/doc/refman/5.0/en/timestamp.html – barranquero Aug 03 '11 at 08:46
  • 2
    I had to set the timestamp field to `null` in the update statement. Now every time the field is updated (even when the data has not really changed). – testing Jan 22 '13 at 16:13
  • Yes, and there exists other synonyms for NOW() like LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP(), CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() - http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html – barranquero Jul 01 '14 at 08:21
  • 2
    Isn't it better to use `UTC_TIMESTAMP()` to avoid problems with the MySQL server timezone being different to your application? – rjh Jan 19 '17 at 10:39
19

It might be the case that the update statement doesn't change anything. If the row with Tid = 'SITE001' already has Backupstatus set to 'FAIL' and Backupmsg set to 'Connection timed out' (maybe, set by some previous backup attempt), then MySQL will skip this row and therefore won't change the Lastconnection timestamp.

Also, I see ON UPDATE CURRENT_TIMESTAMP more like an administrative feature to keep track of data changes. As a programmer, I would add the timestamp update explicitly:

update Hosts
set Backupstatus = 'FAIL', Backupmsg = 'Connection timed out', Lastconnection = NOW() where Tid = 'SITE001'.

Gas Welder
  • 565
  • 5
  • 12
  • 3
    It's a common mistake true, "on update" clause is more administrative and should not be used like this. If there is no real changements on the data, it will not work. – Thomas Decaux May 02 '14 at 11:42
10

You must remember that if no value was changed on update it won't set the current time stamp,

You have to set the value in query NOW() to set for the current time stamp !!!

update Hosts set Backupstatus = 'FAIL',Lastconnection = NOW() , Backupmsg = 'Connection timed out' where Tid = 'SITE001'

Remember the value must change in order for the current time stamp to change.

Pini Cheyni
  • 5,073
  • 2
  • 40
  • 58
1

To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.

DEFAULT 0 do not work if the NO_ZERO_DATE SQL mode is enabled because that mode causes “zero” date values (specified, for example, as 0 '0000-00-00 00:00:00') to be rejected. Be aware that the TRADITIONAL SQL mode includes NO_ZERO_DATE.

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

Nag Hammadi
  • 297
  • 1
  • 6
  • 17
  • 3
    Am I missing something, or is this answer saying to do what the OP was already _doing_, which failed anyway? He said that the timestamp field with `ON UPDATE CURRENT_TIMESTAMP` **does not change on update**, didn’t he? Good general information about timestamps; bad answer to this question. – Frungi Dec 28 '13 at 07:54
1

If you want the record to automatically update the timestamp whenever the record is changed, here's the four simple steps you need to accomplish (could be all in one step, depending on if you're using command line or GUI to administer):

  1. Create field to hold auto-updated timestamp (I typically call mine 'modified').
  2. Specify field Type as 'TIMESTAMP'
  3. Specify field Default as 'CURRENT_TIMESTAMP'
  4. Specify field Extra as 'ON UPDATE CURRENT_TIMESTAMP'

Now the field that contains your timestamp will always be updated to the current timestamp anytime the record is updated.

psterling
  • 75
  • 7