Hacker News Reader

Comment by mbreese | Hacker News Reader

Um... no. Shouldn't a timestamp reflect a specific point in time? And it shouldn't change unless there is an ON UPDATE condition set. That would be the correct thing to do according to the principle of least surprise. You could then set a column to auto-update, but it shouldn't be the default action.

I'm fairly shocked that I haven't been hit with this "feature". Thankfully, I usually set a DEFAULT on timestamp columns.

View on HN

In MySQL (and SQL Server, apparently), it reflects the specific point in time when this row was last updated, and neither database will let you have more than one such field in a table. The data type everyone here seems to want is "datetime", which behaves like you want a "timestamp" to behave.

> neither [MySQL nor SQL server] ... will let you have more than one [timestamp] field in a table.

Wait, what?

There can be only one because it exists solely to be the "last updated" field. There's no point in having more than one. In MySQL, it simply only updates the first -- so you get a sometimes magic field.

I would think it useful to have 'created' and 'updated' fields, for instance. And silently updating only the first...

But you can have this: the "created" field is a datetime field, and the "updated" field would be "timestamp". This is far less problematic, in my opinion, than things like requiring a table with exactly one row to do simple selects, or pretending the empty string and null are the same (both of these behaviors are Oracle, by the way, not postgresql).

The problem here is that one word ("timestamp") is used to mean two completely different things:

1) a datatype (date with time)

2) a pseudo-version number for concurrency control

Both are equally as valid, and depending on your background, when you see the word "timestamp" with zero context you may automatically refer in your head to one definition or another.

I don't see what the problem here is if you expect #1 but got #2 when you used MySQL. This seems like the type of problem that bites you the very first time you encounter it, and then you read the documentation and say "Oh I understand, 'timestamp' has a different definition here, what I really need is the 'foo' datatype. Isn't that it interesting that one word can have more than one meaning!"

ISO standards define the meaning of "timestamp" in SQL. MySQL actually implements the standard type, but gives it the wrong name! They should have used some other name for the bizzare mishmash of data type and trigger they also invented.

"Both are equally valid", except that the former (a data type) is more equally valid than the latter (pseudo-version), since the SQL Standard defines it that way.

I primarily use the timestamp data type in SQL Server. Which is a data type that isn't actually a time (or date) at all, but instead is a incrementing database-wide value that is automatically updated on every row update.

Reply on news.ycombinator.com