5

So I have this stamp timestamp DEFAULT NOW() ON UPDATE NOW() row on my table, and I need it to update even when the update I'm executing is basically same data on all fields.

Is there any way of doing this within the declaration of the table, like some other option other than on update, or do I have to force a stamp = now() every time I update (and remove the on update of course since it will be useless).

I've seen this thread, but it only answers what is happening and why, not how to get around it other than forcing it indirectly

Community
  • 1
  • 1
Bimp
  • 494
  • 2
  • 5
  • 14

3 Answers3

3

As @veeTrain said, it'd be easy if you added it to your update statement. This is just another way of doing it, you can also use unix_timestamp().

UPDATEtable_nameSETlast_logged_in= unix_timestamp() WHEREid= '$user_id'

I know my response is late, but I ran into a similar issue and figured I'd share my solution for those encountering this thread in the future.

Community
  • 1
  • 1
Winter
  • 1,699
  • 1
  • 19
  • 26
  • that depends on the type of the column `last_logged_in`. UNIX_TIMESTAMP() returns an integer, whereas NOW() returns a formatted DATETIME. There are reasons to choose one over the other, see this question for more: http://stackoverflow.com/questions/14849911/difference-between-unix-timestamp-and-now-in-mysql – SpaceDog Sep 27 '13 at 03:15
  • 1
    Right, it also depends on the usage. You can also use PHP's formatting where needed: date("F j, Y, g:i a"); PHP reference: [here](http://php.net/manual/en/function.date.php) and MySQL date/time functions: [here](http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html) – Winter Sep 27 '13 at 03:52
2

You'd have to use a trigger to force it each time.

DELIMITER GO

CREATE TRIGGER `mydb`.`mytable_U` BEFORE UPDATE ON `mydb`.`mytable`  
FOR EACH ROW 
BEGIN  
    SET NEW.stamp = CURRENT_TIMESTAMP;   
END
GO

DELIMITER ;
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Hmm, can I set this trigger up within my java code by any chance, or does it have to be done externally? – Bimp Dec 28 '11 at 15:26
  • @Jayto: it goes into the database. The DB engine fires it for you when you send an UPDATE – gbn Dec 28 '11 at 15:37
  • I know it does, but i'm creating the database itself and the tables through java (jdbc in netbeans), and I was wondering if I could do the same with the trigger, and if I'd use the same syntax. – Bimp Dec 28 '11 at 15:49
  • The exact SQL above is what you need to run from your client code – gbn Dec 28 '11 at 15:52
  • I actually didn't include the delimiter part but yeah it worked. Its a pretty simple database with just that one table and the one trigger, you reckon the delimiter is necessary (i'm not even sure about its role in mysql)? Anyhow, I'll just need to find how to remove the trigger in the end and I'm set. Thanks! – Bimp Dec 28 '11 at 16:03
  • @Jayto: yes. Otherwise you'll get errors. The ; after CURRENT_TIMESTAMP will try to terminate the trigger. – gbn Dec 28 '11 at 16:05
  • Ah I see. I guess it only worked because I tried it on just one row? – Bimp Dec 28 '11 at 16:09
  • One row of data. I now tried to use the delimiters too but i'm getting sql syntax errors. I've used multiple lines for the command, just concatenating the strings with + – Bimp Dec 28 '11 at 16:16
  • @Jayto: I'd ask a new question because now you have a Java problem I'd say – gbn Dec 28 '11 at 16:21
  • strSQL="CREATE TRIGGER `channel`.`basestations` BEFORE UPDATE ON `channel`.`basestations` "+ "FOR EACH ROW "+ "BEGIN "+ "SET NEW.stamp = CURRENT_TIMESTAMP; "+ "END;"; this works fine for multiple rows of data, are you sure It's not correct in some way? – Bimp Dec 28 '11 at 16:22
  • You *need* `DELIMITER GO` like I did above in the same string – gbn Dec 28 '11 at 16:28
  • I found this: `The DELIMITER command is only for the mysql client. Other APIs and libraries don't need it, as you implicitly define the end of your query by the length of the string sent into the API function/method that processes it.` So from what I understand, I don't need to use a custom delimiter outside a mysql client – Bimp Dec 28 '11 at 18:15
1

I find it easier to just do the following as part of your update statement:

UPDATE table_name SET last_logged_in = NOW() WHERE `user_id`...

This way the value is always updated and if nothing else has changed the timestamp still gets updated. Thanks for your question; it was the same as mine.

veeTrain
  • 2,915
  • 2
  • 26
  • 43