Is there a MySQL feature like SQL Server's TIMESTAMP column?

Question

My preliminary research says "no".

I have a database app in SQL Server that uses the SQL Server timestamp column for tracking changes.... that is, I can go through the table and know that if I see a timestamp > than some reference timestamp, that the row has been added or updated.

I'm looking for a type like that in MySQL. It doesn't seem to exist. So, will I have to replicate this functionality using either triggers (good) or application logic (bad)?

Note that just using MySQL's TIMESTAMP isn't really a great solution, as it's not always unique, and not monotonically increasing over time.

EDIT --- here's the scenario I've been using these for, for years...

I have a table in a OLTP system which is the current state of the inventory. I have a front end GUI that's displaying different views of the inventory. I'd like to occasionally go and ask the DB "which rows are new/changed". With SQL Server's TIMESTAMP/ROWVERSION, I could do that easily. If I use an actual date type, I would always run into problems where I either got an update more than once (not a huge disaster), or occasionally missed an update (a disaster).

Answer 1

I'm not sure I understand why this doesn't work for your scenario. Is TIMESTAMP guaranteed to be unique in MS SQL Server?

Aha -- I see in SQL Server timestamp is a synonym for rowversion which is sort of like MySQL's AUTO_INCREMENT except it generates a new monotonically increasing value on UPDATE as well as on INSERT.

No, MySQL doesn't have anything like this. Nor does MySQL support a SEQUENCE object like Oracle or PostgreSQL or IBM DB2. A sequence would allow you to generate a new value from a trigger, for instance.

---

I read your updated info in your question, so I understand the problem you are trying to solve.

What I have seen as an alternative solution is to add another attribute in the table you're processing, call it is_processed or something. Enter a NULL when you insert a new row. When you get around to processing it, change the value in that column to 1. Then you'll always know which rows you have yet to process -- they'll be the rows where is_processed IS NULL.

---

Re your comment: Okay, I see the problem. Each user needs their own view of which rows are new/changed.

Another hack I've seen used in MySQL to simulate a sequence object is a table that contains an auto-increment primary key and nothing else. You can generate new unique monotonically increasing values by inserting into this table, and then roll back the insert.

CREATE TABLE sequence (id SERIAL) ENGINE=InnoDB; -- Must be InnoDB

START TRANSACTION;
INSERT INTO sequence () VALUES (); -- Yes this is a legal statement.
ROLLBACK;

SELECT LAST_INSERT_ID();

You can't start and rollback transactions within a MySQL trigger, so you'll have to generate the new values in your application code.

Or else you could switch to PostgreSQL, and get real support for sequences.

Answer 2

If the monotonicity problem is due to daylight saving time, you can set the time zone to UTC when checking TIMESTAMP columns:

SET time_zone = timezone;

Alternatively, you could fill DATETIME columns with UTC_TIMESTAMP using triggers.

Unfortunately, neither idea enforces uniqueness, nor will they prevent race conditions with transactions.