Hi,
You have two way to set two default timestamp columns.
FIRST :
--------
just use a variable :
mysql> select @t:=current_timestamp;
+-----------------------+
| @t:=current_timestamp |
+-----------------------+
| 2005-05-19 08:58:56 |
+-----------------------+
1 row in set (0.01 sec)
mysql> insert into bookmark
(bookmarkname,url,folder_id,last_scanned,last_notified)
values('t','t','t',@t,@t);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from bookmark;
+-------------+--------------+-----+-----------+---------------------+---------------------+
| bookmark_id | bookmarkname | url | folder_id | last_scanned |
last_notified |
+-------------+--------------+-----+-----------+---------------------+---------------------+
| 1 | t | t | 0 | 2005-05-19 08:55:54 |
2005-05-19 08:55:54 |
| 2 | t | t | 0 | 2005-05-19 08:58:56 |
2005-05-19 08:58:56 |
+-------------+--------------+-----+-----------+---------------------+---------------------+
Even if the default is not set for your columns.
SECOND :
---------
We can use the fact that timestamp can't be NULL. This is the trick :
mysql> CREATE TABLE bookmark2 (
-> bookmark_id INTEGER NOT NULL AUTO_INCREMENT,
-> bookmarkname VARCHAR (80) NOT NULL,
-> url VARCHAR (150) NOT NULL,
-> folder_id INTEGER NOT NULL,
-> last_scanned timestamp NOT NULL default '0000-00-00',
-> last_notified timestamp default current_timestamp on update
current_timestamp,
-> PRIMARY KEY (bookmark_id)
-> ) TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> insert into bookmark2 (bookmarkname,url,folder_id) values('t','t','t');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from bookmark2;
+-------------+--------------+-----+-----------+---------------------+---------------------+
| bookmark_id | bookmarkname | url | folder_id | last_scanned |
last_notified |
+-------------+--------------+-----+-----------+---------------------+---------------------+
| 1 | t | t | 0 | 0000-00-00 00:00:00 |
2005-05-19 09:13:58 |
+-------------+--------------+-----+-----------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> insert into bookmark2 (bookmarkname,url,folder_id,last_scanned)
values('t','t','t',NULL);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from bookmark2;
+-------------+--------------+-----+-----------+---------------------+---------------------+
| bookmark_id | bookmarkname | url | folder_id | last_scanned |
last_notified |
+-------------+--------------+-----+-----------+---------------------+---------------------+
| 1 | t | t | 0 | 0000-00-00 00:00:00 |
2005-05-19 09:13:58 |
| 2 | t | t | 0 | 2005-05-19 09:16:16 |
2005-05-19 09:16:16 |
+-------------+--------------+-----+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)
So if you want a for insert, and a for update two columns, INSERT NULL in the
first, and put current_timestamp for the updated as default.
Mathias
Selon Lieven De Keyzer <[EMAIL PROTECTED]>:
> mysql> CREATE TABLE bookmark (
> -> bookmark_id INTEGER NOT NULL AUTO_INCREMENT,
> -> bookmarkname VARCHAR (80) NOT NULL,
> -> url VARCHAR (150) NOT NULL,
> -> folder_id INTEGER NOT NULL,
> -> last_scanned DATETIME DEFAULT NOW(),
> -> last_notified DATETIME DEFAULT NOW(),
> -> PRIMARY KEY (bookmark_id),
> -> FOREIGN KEY (folder_id) REFERENCES folder(folder_id) ON DELETE
> CASCADE) TYPE = InnoDB;
>
> ERROR 1067 (42000): Invalid default value for 'last_scanned'
>
> >From: Simon Garner <[EMAIL PROTECTED]>
> >To: mysql@lists.mysql.com
> >Subject: Re: Different TIMESTAMP columns
> >Date: Thu, 19 May 2005 09:52:02 +1200
> >
> >Lieven De Keyzer wrote:
> >>
> >>But how do I initialize the 2 datetime fields? With no DEFAULT, their
> >>values are 0000-00-00 00:00:00. Just what I wanted to avoid with TIMESTAMP
> >>
> >
> >Set their value to NOW() if you want the current date/time.
> >
> >-Simon
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
> >http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
