Todays date

[Asbestos2]

Hi

I been creating tables in the Workbench 5.2. What I need is a couple of the date fields to automatically out in today's date. I tried commands like Now()in the default and even tried a trigger which looks fine but doesnt put the date in. I would prefer to keep it simple and put the command in the default..... Despite a massive search on the web I cannot find anything that works

So the question is ANY Ideas folks .................

Thanks

 

[r937]

you cannot default a column to the current date

you must supply the value when inserting, however, you can use mysql's current date function for this

for example,

INSERT INTO daTable ( mydata, mydate )
VALUES ( 'foobar' , CURRENT_DATE )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ramam1]

If you set the field to type "timestamp" and set the default value to CURRENT_TIMESTAMP that should do the trick; note that if you change your table definition then mysql will backfill the column with NOW().

`column_name_here` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

This will make the default value for the column to be the output of NOW() at the time the row is created if you don't specify a value explicitly.

 

[Asbestos2]

Being we are using an Access front end how would the insert work.

So each time a user adds a new record how will the database run the Insert.

I am fairly new to MySQL so not quite into the programming side as yet.

Thanks

 

[r937]

the problem with TIMESTAMP columns is that they also update automatically if the row is updated

so they're no good for a "datetime_added" column

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ramam1]

@r937: Not in mine. Looky:

mysql> create table `testing_dates` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `some_column` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2011-03-29 15:44:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into testing_dates set some_column = 101;
Query OK, 1 row affected (0.00 sec)

mysql> select * from testing_dates;
+----+---------------------+-------------+
| id | modified | some_column |
+----+---------------------+-------------+
| 1 | 2011-03-29 15:44:54 | 101 |
+----+---------------------+-------------+
1 row in set (0.00 sec)

mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2011-03-29 15:45:04 |
+---------------------+
1 row in set (0.00 sec)

mysql> update testing_dates set some_column=102 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2011-03-29 15:45:23 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from testing_dates;
+----+---------------------+-------------+
| id | modified | some_column |
+----+---------------------+-------------+
| 1 | 2011-03-29 15:44:54 | 102 |
+----+---------------------+-------------+
1 row in set (0.00 sec)

mysql>

 

[r937]

ramam, nice try, but watch this --

CREATE TABLE testing_timestamps 
( id       INTEGER   NOT NULL PRIMARY KEY AUTO_INCREMENT
, added    TIMESTAMP NOT NULL
, modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
, foobar   INTEGER       NULL  
);
SELECT NOW();
/* 2011-03-29 22:16:34 */

INSERT 
  INTO testing_timestamps
     ( added, foobar )
VALUES
     ( CURRENT_TIMESTAMP , 9 );
SELECT NOW();
/* 2011-03-29 22:16:57 */

INSERT 
  INTO testing_timestamps
     ( added, foobar )
VALUES
     ( CURRENT_TIMESTAMP , 37 );
SELECT NOW();
/* 2011-03-29 22:17:24 */

UPDATE testing_timestamps
   SET foobar = 937
 WHERE ID = 1;

SELECT *
  FROM testing_timestamps;
/* id   added                 modified              foobar                     
    1   2011-03-29 22:16:57   2011-03-29 22:16:57   937
    2   2011-03-29 22:17:24   2011-03-29 22:17:24   37  
*/

as you can see, the "added" column value for row 1 was updated!!! it shouldn't have been!! and "modified" column for row 1 should have been updated!!! and it wasn't!!!





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ramam1]

hey r937,
I am looking at your post and I don't understand how you drew your conclusion.

When you first inserted row 1 the time was between 22:16:34 and 22:16:57.
After updating row 1 we see that the 'added' field (set explicitly) and the modified field (set by auto-magic I claim exists) both are in that original time range. In fact they are the same value -> this is because you set `added` = NOW() and mysql set `modified` = NOW()... both at the time of the original insert. It looks like the subsequent update of row 1 had no impact on the values of added nor modified, as expected.

 

[Asbestos2]

Hi Guys

You lost me now which to be honest isnt hard to do. What we need is the current date (not time) to appear when we open a form (in access) to appear and be part of the record when it is saved....

But form what I can gather this is not possible, is this correct

Thanks

 

[ramam1]

Hey Asbestos2,
If you are only interested in setting the date when the record is initially saved (but not updated every time you change the record) then what I suggested will work; in order to show only the date and not the time you should be able to parse or format the field to show only the date (a timestamp in this context has both the date and the time.. you want to hide the time from the user).
If you need to update the date stored every time you save the form then this approach won't work -> instead you must figure out the current date and pass it into the database as a value for that column. I have no idea how to do anything in MS Access so I can't help you with that.
have fun

 

[r937]

What we need is the current date (not time) to appear when we open a form (in access) to appear and be part of the record when it is saved....

pls ask this question in forum701




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon