Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Friday April 20, 2007
10:29 AM

MySQL Timestamp Confusion

[ #33047 ]

Straight from the docs for the latest version, regarding the initialization and updating of timestamp columns (emphasis their's, snarky parentheticals mine):

  • If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored (gosh, that's handy). The default can be CURRENT_TIMESTAMP or a constant date and time value.
  • DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column (huh?). For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.
  • ... snip ...

It goes on like that, with a number of curious statements which make me wonder if the developers sat in a back room drinking cheap whiskey and making up "features" to foist on people.

The second bullet point is scary. Why the hell would I want to have "DEFAULT NULL" treated as "DEFAULT CURRENT_TIMESTAMP"? I want my database to do what I tell my database to do, damn it. What's worse, they refer to the first timestamp column. Ignoring the fact that the MySQL is drifting even further from the relational model (sets aren't ordered!), there's a very practical issue here. If I drop and recreate a table, my table's behavior can change dramatically if I reorder my columns. That should not be relevant.

And for other timestamp columns, DEFAULT NULL is treated as DEFAULT 0? Why? This doesn't make a damned bit of sense to me. It also worries me given Adrian's post on a similar MySQL issue.

Reading through the comments for those docs is also "real fun".

MySQL: Transactions, check. Triggers, check. Foreign keys, check. Still a toy? Check.


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

MySQL Timestamp Confusion 7 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • Why the hell would I want to have "DEFAULT NULL" treated as "DEFAULT CURRENT_TIMESTAMP"?

    Because I certainly don't believe that all of my middleware has the exact same time, and I want a single canonical reference for the time I added a row to the database.

    Maybe I'm too lazy always to include now() every time I insert a row into the table, and I'm definitely too lazy to write a stored procedure to insert the row or a trigger to set those values, but the fewer magical things the middleware has to do and

    • I can't say you've provided a convincing argument for why a DEFAULT NULL gets current timestamp or 0 depending on it's position.

      If the the designer has said they want an empty timestamp to default to NULL, the value should be NULL, not whatever the database decides to do with it.

      What if my first timestamp column in a table represents the date that I shipped an order to a customer and the second represents the date it was ordered? If I haven't shipped it to the customer yet ... ? That's one heck of an odd im

      • I can't say you've provided a convincing argument for why a DEFAULT NULL gets current timestamp or 0 depending on it's position.

        True. Likewise, I didn't provide a convincing argument why butterscotch is disgusting, but at least I can defend that position.

    • Yes, that is a good argument showing that it makes perfect sense to use DEFAULT CURRENT_TIMESTAMP.

      However, am I missing something, or did you fail to even notice that the point in question is about DEFAULT NULL (which is being treated the same as DEFAULT CURRENT_TIMESTAMP for some bizarre reason (except when it’s not))?

      • However, am I missing something, or did you fail to even notice that the point in question is about DEFAULT NULL...

        You're right; I did fail to notice that. Somehow I turned it around in my head so that it meant "When you insert no value into this column," which is completely different.

  • The ability to specify a useful default for timestamp columns is newish. The old behavior was to treat the first timestamp column with DEFAULT NULL as what can now be expressed as DEFAULT CURRENT_TIMESTAMP. If you didn't like it, you used datetime as the column type instead of timestamp.
  • Yeah, I've worked with a big pile of MySQL-based software that uses timestamps instead of date times all over the place, and then does crazier things. The first timestamp, for example, is used as a last modification time, except it's displayed to the users as something more like a created/modified-in-certain-way time, meaning that if you modify other fields, you have to modify that one to explicily /not/ change.

    Sure, it's a sign of programmer failure, but it's yet another case where MySQL makes easy things
    --
    rjbs
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.