469,942 Members | 2,606 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,942 developers. It's quick & easy.

Now() as DateTime default value

Atli
5,058 Expert 4TB
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.
Jan 24 '07 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. -- Clean up before starting.
  2. DROP TABLE IF EXISTS `test_tbl`;
  3. DROP TRIGGER IF EXISTS `test_tbl_before_insert`;
  4.  
  5. -- Create the table.
  6. CREATE TABLE `test_tbl` (
  7.     `id` Int Auto_Increment Not Null Primary Key,
  8.     `random_value` VarChar(255) Not Null Default 'value',
  9.     `created` DateTime Not Null
  10. );
  11.  
  12. -- Create a trigger to update the created value.
  13. DELIMITER //
  14. DROP TRIGGER IF EXISTS `test_tbl_before_insert`//
  15. CREATE TRIGGER `test_tbl_before_insert`
  16. BEFORE INSERT ON `test_tbl`
  17. FOR EACH ROW
  18. BEGIN
  19.     -- Only set the default value if it's empty
  20.     IF NEW.created = '0000-00-00 00:00:00' THEN
  21.         SET NEW.created = NOW();
  22.     END IF;
  23. END;//
  24. DELIMITER ;
  25.  
  26. -- INSERT using the default value.
  27. INSERT INTO `test_tbl`(`random_value`)
  28. VALUES ('First'), ('Second'), ('Third');
  29.  
  30. -- INSERT explicit values
  31. INSERT INTO `test_tbl`(`random_value`, `created`)
  32. VALUES ('Fourth', '2010-01-01'), ('Fifth', '2009-12-24');
  33.  
  34. -- Select stuff
  35. SELECT * FROM `test_tbl`;
Outputs:
Expand|Select|Wrap|Line Numbers
  1. +----+--------------+---------------------+
  2. | id | random_value | created             |
  3. +----+--------------+---------------------+
  4. |  1 | First        | 2010-10-18 17:39:26 |
  5. |  2 | Second       | 2010-10-18 17:39:26 |
  6. |  3 | Third        | 2010-10-18 17:39:26 |
  7. |  4 | Fourth       | 2010-01-01 00:00:00 |
  8. |  5 | Fifth        | 2009-12-24 00:00:00 |
  9. +----+--------------+---------------------+
  10. 5 rows in set (0,00 sec)

6 77772
bartonc
6,596 Expert 4TB
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.
Jan 24 '07 #2
ronverdonk
4,258 Expert 4TB
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:
Jan 24 '07 #3
bartonc
6,596 Expert 4TB
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
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `hetap_setup`.`stations`
  2.     MODIFY COLUMN `timestamp`
  3.     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
Jan 25 '07 #4
this wotks:

create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Oct 14 '10 #5
Atli
5,058 Expert 4TB
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 :)
Oct 14 '10 #6
Atli
5,058 Expert 4TB
I did find a way to do this using Triggers. It's kind of a long way around this, but it works.

For example:
Expand|Select|Wrap|Line Numbers
  1. -- Clean up before starting.
  2. DROP TABLE IF EXISTS `test_tbl`;
  3. DROP TRIGGER IF EXISTS `test_tbl_before_insert`;
  4.  
  5. -- Create the table.
  6. CREATE TABLE `test_tbl` (
  7.     `id` Int Auto_Increment Not Null Primary Key,
  8.     `random_value` VarChar(255) Not Null Default 'value',
  9.     `created` DateTime Not Null
  10. );
  11.  
  12. -- Create a trigger to update the created value.
  13. DELIMITER //
  14. DROP TRIGGER IF EXISTS `test_tbl_before_insert`//
  15. CREATE TRIGGER `test_tbl_before_insert`
  16. BEFORE INSERT ON `test_tbl`
  17. FOR EACH ROW
  18. BEGIN
  19.     -- Only set the default value if it's empty
  20.     IF NEW.created = '0000-00-00 00:00:00' THEN
  21.         SET NEW.created = NOW();
  22.     END IF;
  23. END;//
  24. DELIMITER ;
  25.  
  26. -- INSERT using the default value.
  27. INSERT INTO `test_tbl`(`random_value`)
  28. VALUES ('First'), ('Second'), ('Third');
  29.  
  30. -- INSERT explicit values
  31. INSERT INTO `test_tbl`(`random_value`, `created`)
  32. VALUES ('Fourth', '2010-01-01'), ('Fifth', '2009-12-24');
  33.  
  34. -- Select stuff
  35. SELECT * FROM `test_tbl`;
Outputs:
Expand|Select|Wrap|Line Numbers
  1. +----+--------------+---------------------+
  2. | id | random_value | created             |
  3. +----+--------------+---------------------+
  4. |  1 | First        | 2010-10-18 17:39:26 |
  5. |  2 | Second       | 2010-10-18 17:39:26 |
  6. |  3 | Third        | 2010-10-18 17:39:26 |
  7. |  4 | Fourth       | 2010-01-01 00:00:00 |
  8. |  5 | Fifth        | 2009-12-24 00:00:00 |
  9. +----+--------------+---------------------+
  10. 5 rows in set (0,00 sec)
Oct 18 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Phil Powell | last post: by
reply views Thread by gw | last post: by
44 posts views Thread by Frank Rizzo | last post: by
4 posts views Thread by John J. Hughes II | last post: by
2 posts views Thread by Kenneth P | last post: by
14 posts views Thread by Bob Day | last post: by
21 posts views Thread by Willie jan | last post: by
4 posts views Thread by =?Utf-8?B?QmFidU1hbg==?= | last post: by
5 posts views Thread by shapper | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.