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?
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.
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.
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.
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!
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.
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
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.
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?
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?
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.
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.
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.
Before you post, read our: 
