Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
01-02-2013, 05:54 AM #1
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!
01-02-2013, 04:40 PM #2
- Join Date
- Jan 2011
- Thanked 760 Times in 758 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.
01-02-2013, 06:59 PM #3
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:
UPDATE tablename SET field1=xxx, field2=yyy, myTimestamp=myTimestamp WHERE id=nnn
RTFM: http://dev.mysql.com/doc/refman/5.1/...alization.htmlBe yourself. No one else is as qualified.
Users who have thanked Old Pedant for this post:
01-02-2013, 11:22 PM #4
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!
01-02-2013, 11:44 PM #5
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.Be yourself. No one else is as qualified.
01-02-2013, 11:53 PM #6
01-03-2013, 01:03 AM #7
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!
01-03-2013, 02:31 AM #8
Also read this page:
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.
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.Be yourself. No one else is as qualified.
01-03-2013, 03:03 AM #9
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!
01-03-2013, 04:31 AM #10
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.Be yourself. No one else is as qualified.
01-03-2013, 03:45 PM #11
- Join Date
- Jan 2011
- Thanked 760 Times in 758 Posts
01-03-2013, 08:50 PM #12
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.Be yourself. No one else is as qualified.