How to get CURRENT_TIMESTAMP or NOW in TIMESTAMP or DATETIME column

Hi,

I have read everywhere for this.  Some say it can be done and some say it cannot.  I have 2 TIMESTAMP columns, idstart & idfinish.  In the start I want it to take the current time.  I have tried changing it to CURRENT_TIMESTAMP DEFAULT or ON UPDATE with not success.  I have also tried changing inside my php/html form to use different timestamp things/CURRENT_TIMESTAMP etc.
MySQL says it can be done: http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

If you know the syntax please let me know cuz I'm doing it on the GUI in MySQL Administrator on Windows, I need to konw how to update an exiting DB/table like:

ALTER TABLE `onr`.`runs` MODIFY COLUMN `idstart` TIMESTAMP NOT NULL DEFAULT 'CURRENT_TIMESTAMP';

--this returns: "MYSQL error number 1067, invalid default value for idstart"

or just make it so somehow, from my form, the current time gets put in that column when some checkbox is checked, like:

<LABEL for="idstart">Start Time: </LABEL>
<INPUT type="text" name="idstart"> [YYYY-MM-DD HH:MM:SS]
<input type="checkbox" name="startcheck"> use current time
...
<?php
  if ($_POST['submit']) {
    if ($_POST['startcheck'] == "y") {
        $thetime = 'CURRENT_TIMESTAMP';
    }
    else {
        $thetime = $_POST['idstart'];
    }
LVL 1
sillygooseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
No quotes around CURRENT_TIMESTAMP:

ALTER TABLE `onr`.`runs` MODIFY COLUMN `idstart` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
todd_farmerCommented:
Here's my test:

mysql> use onr;
Database changed
mysql> create table runs (idstart TIMESTAMP);
Query OK, 0 rows affected (0.25 sec)

mysql> ALTER TABLE `onr`.`runs` MODIFY COLUMN `idstart` TIMESTAMP NOT NULL DEFAULT 'CURRENT_TIMESTAMP';
ERROR 1067 (42000): Invalid default value for 'idstart'
mysql>

mysql> ALTER TABLE `onr`.`runs` MODIFY COLUMN `idstart` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
sillygooseAuthor Commented:
Tried it bro, it sets the DEFAULT to CURRENT_TIMESTAMP, but the time is not put in the DB when I submit :

   $sql = "INSERT INTO runs (idusers,idvtltype,idvtlversion,idappversion,idbackupapp,iddetails,idtesttype,idstatus,idconfig,idstart,idfinish) VALUES
('{$_POST['idusers']}','{$_POST['idvtltype']}','{$_POST['idvtlversion']}','{$_POST['idappversion']}','{$_POST['idvbackupapp']}','{$_POST['iddetails']}','{$_POST['idtesttype']}','{$_POST['idstatus']}','{$_POST['idconfig']}','{$_POST['idstart']}','{$_POST['idfinish']}')";


Can't insert into table runs:
INSERT INTO runs (idusers,idvtltype,idvtlversion,idappversion,idbackupapp,iddetails,idtesttype,idstatus,idconfig,idstart,idfinish) VALUES ('bvitiello','DS','123','123','VNBU',' asdf','func','running',' asdf','','')
Incorrect datetime value: '' for column 'idstart' at row 1
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

todd_farmerCommented:
Your insert statement is not correct.  You can't pass an empty string (not the same thing as a NULL value) and have it stored to a TIMESTAMP column.  You have to modify your INSERT statement to either explicitly pass a null value or not include the column in the INSERT column list:

  $sql = "INSERT INTO runs (idusers,idvtltype,idvtlversion,idappversion,idbackupapp,iddetails,idtesttype,idstatus,idconfig,idstart,idfinish) VALUES
('{$_POST['idusers']}','{$_POST['idvtltype']}','{$_POST['idvtlversion']}','{$_POST['idappversion']}','{$_POST['idvbackupapp']}','{$_POST['iddetails']}','{$_POST['idtesttype']}','{$_POST['idstatus']}','{$_POST['idconfig']}',null,'{$_POST['idfinish']}')";

  $sql = "INSERT INTO runs (idusers,idvtltype,idvtlversion,idappversion,idbackupapp,iddetails,idtesttype,idstatus,idconfig,idfinish) VALUES
('{$_POST['idusers']}','{$_POST['idvtltype']}','{$_POST['idvtlversion']}','{$_POST['idappversion']}','{$_POST['idvbackupapp']}','{$_POST['iddetails']}','{$_POST['idtesttype']}','{$_POST['idstatus']}','{$_POST['idconfig']}','{$_POST['idfinish']}')";

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sillygooseAuthor Commented:
Ahh, yes.

I just did that before looking here and it worked, thanks!
todd_farmerCommented:
Good deal.  Thanks for the points!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.