473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 78151
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

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

Similar topics

2
by: Phil Powell | last post by:
I have a mySQL database table with a column field datatype of datetime that I set up in the original create table statement like this: create table nnet_usermetadata ( .... nnet_record_entered...
0
by: gw | last post by:
I am new to MySQL and my problem is with storing a datetime value in a table. I created a table with a column designated as DATETIME. I fill it with the NOW() function. No matter whether I...
44
by: Frank Rizzo | last post by:
Any ideas?
4
by: John J. Hughes II | last post by:
I have several functions that save the time when they did certain tasks to an SQL table. This was working fine as far as I could tell but I now have systems that are updating thousands of records...
2
by: Kenneth P | last post by:
Hi, I'm trying to use MySql 4.1 db instead of MSSql2k and am having problems with the DateTime in the db. In mysqladmtools 1.0.19 I'm recommended to use 0000-00-00 00:00:00 as the default...
14
by: Bob Day | last post by:
using VS 2003, VB.net... Why is this valid: dim x as datetime = nothing but this is invalid: public sub DoSomething (Optional byval x as datetime = nothing) ....code to do something end...
21
by: Willie jan | last post by:
place this behind a button that fills a listbox. as you will see the time is now and then 0 or filled in???????????? by hitting the button. is there a way to determine the real elapsed time? ...
4
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which -...
5
by: shapper | last post by:
Hello, I defined a DateTime variable: Dim dt As New DateTime How can I check if it is empty? Basically I want to check if it was given to it a DateTime value or not.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.