Keeping MySQL timestamp locked

Hi!
I have a TIMESTAMP field in MySQL created every time a new record is being inserted. I'd like to keep the value entered the first time.
At present, every time I update the record, a new (current date and time) value overwrites the previous one.
So I need to have this value "locked" (somehow).
Actually, I need to have 2 separate fields: one for initial time ("locked") and the other for the updated time.
Any ideas?
Thanks!
FrossardAsked:
Who is Participating?
 
Ruben CornejoConnect With a Mentor CINCommented:
Soory about the typo!
The query you asked me about will insert another field, and this field will update the timestamp everytime you update a record
I think you also wanted a new field to do this, so you can keep track of the date of te last change

You only need to define the name of the field and replace it in the query


ALTER TABLE tablename ADD fieldnameyouwant TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON >UPDATE CURRENT_TIMESTAMP 

Open in new window

0
 
steelseth12Commented:
Change the datatype from timestamp to datetime.
Note: You will need to insert the value of the datetime field in your insert statement
0
 
Ruben CornejoCINCommented:
You need to alter your table

To 'lock' the timestamp use:
ALTER TABLE yourtable MODIFY fieldname  TIMESTAMP DEFAULT CURRENT_TIMESTAMP

to add the field that update the timestamp use:
ALTER TABLE yourtable ADD fieldname DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

For more informatio;
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html


0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
FrossardAuthor Commented:
"Change the datatype from timestamp to datetime.
Note: You will need to insert the value of the datetime field in your insert statement"

This sounds good, but I would like to have both at the same time: to "keep" the timestamped date/time, as well as not manually enter this value. Currenty (with datatype set to timestamp), whenever I insert a new record, the date/time is being automatically inserted, and that's what I want to retain. I don't want to enter the date/time every time manually, but I want to have it entered and "locked", not being updated every time I update the field.
0
 
Ruben CornejoCINCommented:
Frossard
runs this querys on you database, just replace the name of you table and you fields

To 'lock' the timestamp run this query:
ALTER TABLE yourtable MODIFY fieldname  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
this query insert the time stamp when you insert a new record, but do not update with every change

to add the field that update the timestamp run this one:
ALTER TABLE yourtable ADD fieldname DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
This query add another fields that uodates every time you made a change to the record

For more information;
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
0
 
FrossardAuthor Commented:
rubech:
My field is named MYDATETIME, so here's what I got when running the queries:

"SQL query:
ALTER TABLE MODIFY MYDATETIME DEFAULT CURRENT_TIMESTAMP

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MYDATETIME DEFAULT CURRENT_TIMESTAMP' at line 1"

"SQL query:
ALTER TABLE MODIFY MYDATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MYDATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP' at line 1"

"SQL query:
ALTER TABLE MODIFY MYDATETIME DEFAULT CURRENT_TIMESTAMP

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MYDATETIME DEFAULT CURRENT_TIMESTAMP' at line 1"
 

 
0
 
Ruben CornejoCINCommented:
You need to include the name of the table:
ALTER TABLE tablename MODIFY MYDATETIME DEFAULT CURRENT_TIMESTAMP

an for the new field that will update the timestamp every time you change a record:
ALTER TABLE tablename ADD fieldnameyouwant TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP



0
 
FrossardAuthor Commented:
Yes, I forgot to include the table name. So with 'mydata' as a table name, I get:

"SQL query:

ALTER TABLE mydata MODIFY MYDATETIME DEFAULT CURRENT_TIMESTAMP

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CURRENT_TIMESTAMP' at line 1 "
0
 
FrossardAuthor Commented:
OK, the following statement worked:

ALTER TABLE mydata MODIFY MYDATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Now, can you explain a little bit your advice:

>an for the new field that will update the timestamp every time you change a record:
>ALTER TABLE tablename ADD fieldnameyouwant TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON >UPDATE CURRENT_TIMESTAMP


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.