6 ms·
What's the beef with the timestamp? I would expect, by default, that a timestamp reflects the last change to the row. Most systems use timestamps for optimistic
by ergo98 16y ago
What's the beef with the timestamp? I would expect, by default, that a timestamp reflects the last change to the row. Most systems use timestamps for optimistic locking/replication purposes.
- mbreese 16y agoUm... 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.
- ergo98 16y agoI 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.
- randallsquared 16y agoIn 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.
- JoachimSchipper 16y ago> neither [MySQL nor SQL server] ... will let you have more than one [timestamp] field in a table. Wait, what?
- gxti 16y agoThere 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.
- JoachimSchipper 16y agoI would think it useful to have 'created' and 'updated' fields, for instance. And silently updating only the first...
- randallsquared 16y agoBut 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).
- deleted 16y ago[deleted]
- brown9-2 16y agoThe 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!"
- prodigal_erik 16y agoISO 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.
- xzilla 16y ago"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.
- Lewisham 16y agoI often use timestamps so I know when a record was created. If I want it to reflect the last change to a row, I would set it to have a trigger on a "last_updated" column. I haven't been hit with this bug either, but this was by accident (or design in the GUI interface I was using). I find it quite shocking.