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.
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)
6 78151
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.
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:
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;
this wotks:
create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
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 :)
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: - -- 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)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
by: Frank Rizzo |
last post by:
Any ideas?
|
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...
|
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...
|
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...
|
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?
...
|
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 -...
|
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.
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |