Solved

Keeping MySQL timestamp locked

Posted on 2007-11-17
9
1,284 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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