Ahhh, yeah, I did read that you couldn’t have multiple columns with CURRENT_TIMESTAMP. The wording was a bit vague though so I was going to try it out. Good to know.
I suppose the thing to do then is use TIMESTAMP for `updated` (with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) and then DATETIME for `created` and just use a NOW() in the original INSERT query. David Cook Systems Librarian Prosentient Systems 72/330 Wattle St, Ultimo, NSW 2007 From: koha-devel-boun...@lists.koha-community.org [mailto:koha-devel-boun...@lists.koha-community.org] On Behalf Of Jonathan Druart Sent: Thursday, 13 November 2014 7:27 PM To: koha-devel@lists.koha-community.org Subject: Re: [Koha-devel] Ambiguous column names Haha, funny. I tried to do that 2 days ago and... it does not work. create table test( created timestamp not null default current_timestamp, updated timestamp not null default current_timestamp on update current_timestamp ); ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause The solution would be to create a trigger... MySQL-- So maybe with a datetime? Hum, yes maybe, but only from MySQL 5.6.5 (https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html) MySQL-- 2014-11-13 4:10 GMT+01:00 David Cook <dc...@prosentient.com.au <mailto:dc...@prosentient.com.au> >: Thanks for the link! It's rather interesting! Yeah, I think "created" and "updated" columns would be useful. I think we already use them in "biblio", although maybe the code in Koha is setting those rather than the database. It would be handy to either auto-initialize/auto-update, or just pass null once to a "created" column and every time to an "updated" column. I'm happy to drop it, but thanks for the clarification :D. David Cook Systems Librarian Prosentient Systems 72/330 Wattle St, Ultimo, NSW 2007 > -----Original Message----- > From: koha-devel-boun...@lists.koha-community.org > <mailto:koha-devel-boun...@lists.koha-community.org> [mailto:koha-devel- > <mailto:koha-devel-> > boun...@lists.koha-community.org <mailto:boun...@lists.koha-community.org> ] > On Behalf Of Robin Sheat > Sent: Thursday, 13 November 2014 1:03 PM > To: koha-devel@lists.koha-community.org > <mailto:koha-devel@lists.koha-community.org> > Subject: Re: [Koha-devel] Ambiguous column names > > David Cook schreef op do 13-11-2014 om 12:12 [+1100]: > > I think the best option, if we were to change away from timestamp, > > would have to be "lastmodified", as timestamp types will get updated > > after every insert/update, I believe. But since they'd all be called > > "lastmodified", changing it from "timestamp" would become a bit moot, > > I think. > > Well, I was thinking both, "created" and "updated." So you know when the > record was created, and when it was last changed. Both very handy for > simple auditing or bug finding. > > It's possible to have a timestamp field that is initialised to the current > date > automatically on creation, and another that is auto updated. > > http://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html > > This would nicely mean that there's no code at all in Koha required to > maintain this. > > Anyway, I was just sticking my oar in, I've no desire to bikeshed it into the > ground, anything is better than nothing :) > > -- > Robin Sheat > Catalyst IT Ltd. > ✆ +64 4 803 2204 <tel:%2B64%204%20803%202204> > GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org <mailto:Koha-devel@lists.koha-community.org> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
