Solved

Keeping MySQL timestamp locked

Posted on 2007-11-17
9
1,273 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
  • 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:rubech
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
 
LVL 4

Expert Comment

by:rubech
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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:rubech
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:
rubech 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now