Solved

Keeping MySQL timestamp locked

Posted on 2007-11-17
9
1,286 Views
Last Modified: 2009-12-16
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!
0
Comment
Question by:Frossard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 20

Expert Comment

by:steelseth12
ID: 20304456
Change the datatype from timestamp to datetime.
Note: You will need to insert the value of the datetime field in your insert statement
0
 
LVL 4

Expert Comment

by:Ruben Cornejo
ID: 20304626
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
 

Author Comment

by:Frossard
ID: 20305030
"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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Expert Comment

by:Ruben Cornejo
ID: 20305056
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
 

Author Comment

by:Frossard
ID: 20305748
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
 
LVL 4

Expert Comment

by:Ruben Cornejo
ID: 20305809
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
 

Author Comment

by:Frossard
ID: 20306397
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
 

Author Comment

by:Frossard
ID: 20306475
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
 
LVL 4

Accepted Solution

by:
Ruben Cornejo earned 250 total points
ID: 20309509
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

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question