Is there an equivalent to LAST_INSERT_ID for timestamps?

Question

I love the LAST_INSERT_ID() function of MySQL. I use it all the time to retrieve the id of the just inserted row to return from my stored procedure afterwards. However, now I have a table which has a TIMESTAMP as Primary Key that is set to DEFAULT CURRENT_TIMESTAMP. How can I retrieve this last inserted timestamp?

Answer 1

This should do it safely:

START TRANSACTION;
{do your insert};
SELECT MAX({timestamp field}) FROM {table};
COMMIT;

Answer 2

Using MAX method on timestamp field.

Answer 3

Another way to go about it is to leverage the LAST_INSERT_ID(expr) version of that function, which can actually set the value to whatever integer you like (perhaps the UNIX_TIMESTAMP integer of your timestamp?):

INSERT INTO my_tbl SET the_ts = FROM_UNIXTIME(LAST_INSERT_ID(UNIX_TIMESTAMP()));

Now you can:

SELECT FROM_UNIXTIME(LAST_INSERT_ID());

As in the transactional solution provided by Jon Harmon, one downside of this solution is that one has to go into the insert with this in mind (overriding the column's default with the same value explicitly, or starting a transaction). Code readability decreases a bit, and it can confuse others who wish to use this table. I think Jon's solution does a better job of retaining readability.

Also, bear in mind that if you're doing this in a trigger, triggers swallow last_insert_id() value changes.

However, you can bubble it out of a trigger using a session var if you really need to.

---

Try this example of setting LAST_INSERT_ID:

SELECT FROM_UNIXTIME(LAST_INSERT_ID(UNIX_TIMESTAMP()));
SELECT FROM_UNIXTIME(LAST_INSERT_ID());