Setup DATE column that defaults to now and now on update 1

[MrCBofBCinTX]

After many attempts and reading many sites instructions, I just can't get this to work.

1 I don't want time, just the date.
2 I want the current date with first entry.
3 Want new current date for each update.
4 Want this to continue working using perl dbi

I managed to get a timestamp column to partially work, but I don't want time and it failed with my dbi access.

Sound like this is something I should be able to set-up and have work behind the scenes

 

[DonQuichote]

You have basically two options:
- use a timestamp and live with the fact that it comes with a time.
- Use a trigger. See the fine manual for how to define a trigger.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[MrCBofBCinTX]

Sounds like a trigger would be best solution, but a little more work to learn how.

I think I just found source of some of my overall difficulties.
Apparently there is a bug in change or modify columns using current_timestamp.
I think that is why I have been unable to adjust pre-existing columns to what I wanted.
Bug report says that dropping and recreating table is necessary. I'm using 5.0.51a
Report was about 5.0.14 and .15

 

[MrCBofBCinTX]

I tried to create a series of different triggers, but just got errors using them.

So I went back to trying timestamp.
I still get no date whatsoever from perl dbi unless I manually enter it, which is what I want to avoid.
column defaults to current_timestamp but that only works from mysql command line.

I would hope to avoid having to parse the date out for every function call in perl! That seems like a pointless chore!

 

[DonQuichote]

Care to share what you have done? What was the trigger definition and what is the table definition? (SHOW CREATE TABLE ...). Do you use the column in your INSERT queries?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

1 I don't want time, just the date.
2 I want the current date with first entry.
3 Want new current date for each update.
4 Want this to continue working using perl dbi

use DATE

on your first INSERT, do this --

INSERT
INTO daTable ( ... , myDate , ... )
VALUES ( ... , CURRENT_DATE , ... )

then make sure all your subsequent UPDATE statements update it

UPDATE daTable
SET foo = 'somevalue'
, myDate = CURRENT_DATE

a little attention to detail, and you can completely forget about all that trigger nonsense



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

 

[MrCBofBCinTX]

[tt]
mysql> SHOW CREATE TABLE products;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| products | CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`product_description` varchar(100) default NULL,
`sku` varchar(7) default NULL,
`price` float NOT NULL,
`vendor_name` varchar(35) NOT NULL,
`vendor_id` int(11) NOT NULL,
`up_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=194 DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[/tt]

This is the latest version of this table. works ok using mysql, gives all zeros for timestamp using dbi

[tt]
mysql> SHOW CREATE TABLE vendor;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vendor | CREATE TABLE `vendor` (
`vendor_id` int(10) unsigned NOT NULL auto_increment,
`vendor_name` varchar(35) NOT NULL,
`vendor_address` varchar(100) NOT NULL,
`vendor_phone` varchar(25) default NULL,
`date_added` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`vendor_id`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[/tt]

gives all zeros through dbi also. Only works correctly if I actually enter an actual date through dbi

I simply cannot manage to create a date column that will work with dates only AND also update them automatically.
I've been at this silly issue for several days now.

I cannot find any example anywhere of how to create such a column, yet I read all over the place that this is possible

I tried the following and it fails to create a table

[tt]
CREATE TABLE returned ( return_date date NOT NULL default CURRENT_DATE ON UPDATE CURRENT_DATE);
[/tt]
most things I try give syntax error
or ERROR 1067 (42000): Invalid default value for 'return_date'


r937's suggestion does not work, still get all zeros unless I specify a real date