Sybase Watch: Timestamps

March 31, 2007

Timestamps - laugh or cry

The Sybase ASE TIMESTAMP datatype causes more confusion to developers than any other data type. The expectation is that it has a "meaningful" date/time which tells you when the column was last modified. Of course - it's completely different and I've yet to work on a database where it was required.

I suspect that the confusion comes from the prevalence of MySQL, where their TIMESTAMP datatype behaves like a DATETIME which is set to the current time when a record is modified.

Anyway, when looking up the MySQL timestamp on the web, I came across this irate post on the MySQL official support site. How I laughed - what would the poster make of the Sybase timestamp?

Subject: F*** You

The MySQL datatype isn't a timestamp. A timestamp should be a second count from the beginning of the unix epoch. This allows for linear math application of that data in geometric equations. The current "Timestamp" datatype is simply a renamed date datatype. Advertising the existence of a timestamp datatype when none exists in a commercial product could also be seen as fraud.

Suggested fix:

Provide a valid timestamp integer... or remove the datatype all together and stop making up datatypes that don't exist.

And the official MySQL Support reply?

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation

Posted at 08:10 PM | Permalink