Now() as DateTime default value

[Atli]

Hi.

I know this has been posted here before, but I couldn't find any answers.

I'm just wondering if you good people know a way to use the Now() function as the default value of a DateTime field, or anything that would give the same result, without having to use external scripts like PHP or ASP.

I do not want to use TimeStamp. I know it would get me similar results as using DateTime but with annoying side effects, like updating itself every time the row is updated.

Thanks in advance.

[bartonc]

Hi.

I know this has been posted here before, but I couldn't find any answers.

Im just wandering if you good ppl know a way to use the Now() function as the default value of a DateTime field, or anything that would give the same resault, without having to use external scripts like PHP or ASP.

I do not want to use TimeStamp. I know it would get me simular resaults as using DateTime but with annoying side effects, like updating itself everytime the row is updated.

Thanks in advance.

One way to do this is to use the TimeStamp data type. INSERT NULL into that field and it will automatically get the current DateTime.

[ronverdonk]

You cannot use a function or an expression as the default value for any type of column, except for the TIMESTAMP data type column, for which you can specify the CURRENT_TIMESTAMP as the default.

Ronald :cool:

[bartonc]

You cannot use a function or an expression as the default value for any type of column, except for the TIMESTAMP data type column, for which you can specify the CURRENT_TIMESTAMP as the default.

Ronald :cool:

Nice one Ronald! I hadn't seen that yet. Now I have run

ALTER TABLE `hetap_setup`.`stations`
    MODIFY COLUMN `timestamp`
    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

[srikanth kandi]

this wotks:

create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

[Atli]

Hey.

Like I said in my original post, TimeStamp was not an option for me. I was looking for a way to set the DateTime type automatically.

Thanks for the suggestions though :)

[Atli]

I did find a way to do this using Triggers. It's kind of a long way around this, but it works.

For example:

-- Clean up before starting.
DROP TABLE IF EXISTS `test_tbl`;
DROP TRIGGER IF EXISTS `test_tbl_before_insert`;
 
-- Create the table.
CREATE TABLE `test_tbl` (
    `id` Int Auto_Increment Not Null Primary Key,
    `random_value` VarChar(255) Not Null Default 'value',
    `created` DateTime Not Null
);
 
-- Create a trigger to update the created value.
DELIMITER //
DROP TRIGGER IF EXISTS `test_tbl_before_insert`//
CREATE TRIGGER `test_tbl_before_insert`
BEFORE INSERT ON `test_tbl`
FOR EACH ROW
BEGIN
    -- Only set the default value if it's empty
    IF NEW.created = '0000-00-00 00:00:00' THEN
        SET NEW.created = NOW();
    END IF;
END;//
DELIMITER ;
 
-- INSERT using the default value.
INSERT INTO `test_tbl`(`random_value`)
VALUES ('First'), ('Second'), ('Third');
 
-- INSERT explicit values
INSERT INTO `test_tbl`(`random_value`, `created`)
VALUES ('Fourth', '2010-01-01'), ('Fifth', '2009-12-24');
 
-- Select stuff
SELECT * FROM `test_tbl`;

Outputs:

+----+--------------+---------------------+
| id | random_value | created             |
+----+--------------+---------------------+
|  1 | First        | 2010-10-18 17:39:26 |
|  2 | Second       | 2010-10-18 17:39:26 |
|  3 | Third        | 2010-10-18 17:39:26 |
|  4 | Fourth       | 2010-01-01 00:00:00 |
|  5 | Fifth        | 2009-12-24 00:00:00 |
+----+--------------+---------------------+
5 rows in set (0,00 sec)