Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts

    MySql 5.1 TIMESTAMP Do Not Change Upon UPDATE

    I'm using MySql 5.1, and PMA 2.11.4. Is there a way to set it so a TIMESTAMP in a field does not update when the row updates? I've seen this discussed a couple times, but no real answer.

    Also can someone point me to some real good documentation on DATETIME?

    Does anyone know if they plan to allow functions such as now(); in DATETIME fields, or the option to not update TIMESTAMPS (if they all ready don't) in future releases?
    Thanks!

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,092
    Thanks
    23
    Thanked 594 Times in 593 Posts
    This is for mysql.
    A TIMESTAMP column is simply a DATETIME column that automatically updates to the current time every time the contents of that record are altered.
    The TIMESTAMP format is exactly the same as the DATETIME format.
    So if you do not want the column to change when updated use a DATETIME column instead of a TIMESTAMP column.
    Really think it's counter productive to have a TIMESTAMP column that don't update when there is an alternative to accomplish the same thing and not cause confusion.

    Maybe this will help http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    and here http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
    Last edited by sunfighter; 01-02-2013 at 04:43 PM.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    But an easy way to fix this, if you WANT to use a TIMESTAMP column, is to simply set that column to itself in the UPDATE:
    Code:
    UPDATE tablename SET field1=xxx, field2=yyy, myTimestamp=myTimestamp WHERE id=nnn
    Incidentally, a TIMESTAMP column will *NOT* automatically update *UNLESS* you specified that its DEFAULT value is CURRENT_TIMESTAMP. If you do *NOT* specify that default, none of this applies: No automatic value is ever assigned.

    RTFM: http://dev.mysql.com/doc/refman/5.1/...alization.html
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sunfighter (01-03-2013)

  • #4
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    Quote Originally Posted by sunfighter View Post
    This is for mysql.
    A TIMESTAMP column is simply a DATETIME column that automatically updates to the current time every time the contents of that record are altered.
    The TIMESTAMP format is exactly the same as the DATETIME format.
    So if you do not want the column to change when updated use a DATETIME column instead of a TIMESTAMP column.
    Really think it's counter productive to have a TIMESTAMP column that don't update when there is an alternative to accomplish the same thing and not cause confusion.

    Maybe this will help http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    and here http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

    Yes, but DATETIME doesn't insert the date and time on it's own like a TIMESTAMP. You have to insert the the date and time into it manually. Which is why I was curious if you could tell a TIMESTAMP not to update each time.

    Old Pendant, I thought of that, but I was just curious if there were a different way. I was playing with the CURRENT_TIMESTAMP check box earlier in PMA, but I always get

    Incorrect column specifier for column 'date_update'

    I'm just curious if they plan to allow DATETIME to have options like TIMESTAMPs have. Such as NOW() That way you could have TIMESTAMPs that update each time, and a DATETIME that inserts intself upon row creation using options such as NOW, CURDATE, or CURTIME and doesn't update each time. I've seen dozens of requests for NOW to be implemented with DATETIME.

    Thanks for the links guys!

    Old pendant, I figured it out. CURRENT_TIMESTAMP is amazing!
    Last edited by stevenmw; 01-02-2013 at 11:25 PM.
    Thanks!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    It's not just that you can't use NOW() as a default value...you can't use *ANYTHING* except a SIMPLE CONSTANT as the default value for ANY field type in MySQL! The sole exception is TIMESTAMP where you can use CURRENT_TIMESTAMP as the default. And that truly is the one and only exception.

    Even weirder, you can only use CURRENT_TIMESTAMP for *one* column in a table (dunno why you would WANT to use more, but it's just such as strange restriction).

    Compared to commercial DBs such as SQL Server and Oracle, MySQL has a whole raft of weird restrictions and limitations like this. SQL Server. for example, will let you set up even a user-defined function as the default value of a column.

    I think it points to likely a fundamental flaw in the underlying code in the INSERT implementation of MySQL, something so basic to the mechanism that it will be very very tough to change and that's why it's never been changed despite the oh-so-many requests for the change.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    Quote Originally Posted by Old Pedant View Post
    It's not just that you can't use NOW() as a default value...you can't use *ANYTHING* except a SIMPLE CONSTANT as the default value for ANY field type in MySQL! The sole exception is TIMESTAMP where you can use CURRENT_TIMESTAMP as the default. And that truly is the one and only exception.
    Yes, I know. TIMESTAMP has a whole list of functions it can use. I mentioned a couple in a post above, but I couldn't list them all off the top of my head. I just never understood why DATETIME could only be a constant. Like you said they're are a lot of weird restrictions, but it's free. So I can't really complain.

    Quote Originally Posted by Old Pedant View Post
    SQL Server. for example, will let you set up even a user-defined function as the default value of a column.
    I never knew that. That is a very cool concept. It would really simplify a lot of tasks.
    Thanks!

  • #7
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    When not using CURRENT_TIMESTAMP so the TIMESTAMP does not update is there a way to make the Default NOW rather than 0000-00-00 00:00:00?

    I know you can do it manually when inserting or editing a row, but I'm curious if the default can be set to a function, or something else can be done so when a row is inserted the TIMESTAMP adds the current date and time, but still does not update?
    Thanks!

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Quote Originally Posted by stevenmw View Post
    When not using CURRENT_TIMESTAMP so the TIMESTAMP does not update is there a way to make the Default NOW rather than 0000-00-00 00:00:00?

    I know you can do it manually when inserting or editing a row, but I'm curious if the default can be set to a function, or something else can be done so when a row is inserted the TIMESTAMP adds the current date and time, but still does not update?
    No. Read what I wrote and read the MySQL doc page I pointed you to.
    Also read this page:
    http://dev.mysql.com/doc/refman/5.1/...ate-table.html
    On that page read this:
    The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

    If a column definition includes no explicit DEFAULT value, MySQL determines the default value as described in Section 11.5, “Data Type Default Values”.

    BLOB and TEXT columns cannot be assigned a default value.
    Now, having said all that... Yes, "something else can be done so when a row is inserted." You could use a TRIGGER to do almost anything. But, quite frankly, it's a bad idea. Triggers are *SO* easy to get wrong, are not easily visible to somebody coming along later trying to modify your DB, and not terribly efficient.

    If your *ONLY* purpose in all this is to be able to set a DATETIME or TIMESTAMP field to NOW() or equivalent when a record is created--and you don't want to use DEFAULT=CURRENT_TIMESTAMP because of its effects when you UPDATE--then I'd just use a DATETIME field and specify NOT NULL. If you also specify STRICT mode, then MySQL *forces* you to assign a value to it when you do the initial INSERT and so you can assign NOW() at that time. Note that this only applies in STRICT mode. Without STRICT mode indeed you will then get a value of '0000-00-00 00:00:00'. But since you should always run MySQL in STRICT mode if you have any sense of self-preservation, all is well. In STRICT mode, you simply can't omit any NOT NULL field from the INSERT.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    I never use triggers. Should have put "The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column." in red since it says something new, and provides the main answer rather than the text that repeated what was said above. haha Thanks again. Appreciate it.
    Thanks!

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Clearly you missed the entire first paragraph of my post #5.

    That's exactly what I said there.

    Ummm...except then you quoted that paragraph in your post #6.

    So I guess you just missed what I was trying to say.

    Ah, well... all straightened out now.
    Last edited by Old Pedant; 01-03-2013 at 04:34 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,092
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Quote Originally Posted by Old Pedant View Post
    But an easy way to fix this, if you WANT to use a TIMESTAMP column, is to simply set that column to itself in the UPDATE:
    Code:
    UPDATE tablename SET field1=xxx, field2=yyy, myTimestamp=myTimestamp WHERE id=nnn
    That's new to me. Thanks Old Pedant for to info.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Quote Originally Posted by sunfighter View Post
    That's new to me. Thanks Old Pedant for to info.
    It's actually implicit in the docs, just not spelled out in those words.

    The docs do say that the default value (that is, CURRENT_TIMESTAMP in this case) is only used when no value is assigned by the user code. What they don't say is that the user value can, indeed, be the same as the existing value.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •