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
Solved

Keeping MySQL timestamp locked

Posted on 2007-11-17
9
1,282 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

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.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

809 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