mysql q
- Started
- Last post
- 5 Responses
- acescence
how do i set up two TIMESTAMP fields to hold the created date and modified dates so that:
when a row is created, the current timestamp is inserted into the created field
when the record is updated, the current timestamp is inserted into the modified field
i can do one or the other, but can't seem to get both working.
- flavorful0
Hrm ... can't you just set up a procedure or SQL script that whenever the row is updated, it updates the timestamp in the modified datetime field?
- acescence0
i figured it out... you have to pass the second instance of TIMESTAMP a value of "NULL" on insert to get it to auto-insert the current timestamp.
- heavyt0
well, the default of the created field should be NOW().
the modified field should probably be set when you build the query.
ie. Insert into table (modified) values (date())something like that
- flavorful0
MySql, more like laMeSql.
- acescence0
first timestamp (modified) is NOTNULL and ON UPDATE CURRENT_TIMESTAMP
this gets set with current timestamp when the row is inserted or updated
second timestamp (created) is NOTNULL and default 0000-00-00 00:00:00
when you do an insert, if you pass a value of NULL for the created column, it will override the default and insert the current timestamp, seems a bit counter-intuitive, but it works, no query trickery required.