Default value for the Datetime data type...

[ClarkKent101]

Hey Everyone,

I have a datetime data type in one of my tables and i want it to set the current date to itself when a record is inserted into the table. I know i could use the timestamp data type and assign CURRENT_TIMESTAMP as the default, but can this be done with the datetime data type as well?

I already have a field with the timestamp data type, it is used to determine when the record was last updated. I want another record to do basically the same thing except it must only contain when the record was added to the database. I tried using timestamp with its default value but apparantly i can't do that, navicat won't let me assign more than one timestamp field a default value of CURRENT_TIMESTAMP... so i'm turning towards using the datetime data type to get this done, is there a default value i could use to allow the datetime data type to assign the current date and time to itself when a record is added to the table?

Thanks for your help ,

- CK

 

[ClarkKent101]

Hey Everyone,

I solved the problemmo, got a bit ahead of myself posting first... Instead of specifying a default value i had a MySQL function execute to add the current date when a record is added to a table in the database. Using the datetime data type i used the CURDATE() function to assign the current date to its corresponding field. I'll clarify further by providing an example...

INSERT INTO your_database_name (current_date, ...) VALUES (CURDATE(), ...)

The output of the CURDATE() function is '2003-12-31 01:02:03', i then applied a mask so it displays friendlier output to the user.

Hope this helps someone ,

Regards,

- CK

 

[r937]

The output of the CURDATE() function is '2003-12-31 01:02:03'

um, no it isn't


CURDATE() returns a date, not a datetime

INSERT INTO your_database_name should actually be your_table_name, right?

and of course you don't actually have a column called current_date, do you? because that's a reserved word

r937.com | rudy.ca

 

[ClarkKent101]

Hi r937,

CURDATE() does return only the date, but it also returns zeros after the date which is where the time is supposed to be, it returns the following '2003-12-31 00:00:00'; that's the output i got from that function. Committed a mistake where the time is supposed be due to rushing through ('01:02:03'), its supposed to be just zeros. Your second correction is correct, its supposed to be your_table_name... rushing through typing out posts isn't good!

current_date was the first name that popped into my mind, i don't actually have that as a column name - i in fact didn't know it was a reserved word... thanks for pointing that out.

Regards,

- CK

 

[ClarkKent101]

Just out of curiousity, why can't a person have two timestamp data type fields with the same default value? I'm using Navicat to interact with my database and i couldn't add a second field with the timestamp data type that has the same default value as my other timestamp data type field that is in the same table. Kept getting this error stating that i cannot have two timestamp data type fields with the same default value - that being CURRENT_TIMESTAMP.

Any ideas?

Thanks for your help,

- CK

 

[r937]

why? because you can't, that's why ;-)

see

http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

r937.com | rudy.ca

 

[KarveR]

because when you affect the row, both will be atomatically updated which would defeat the point of having two fields in the first place (and I won't even try to explain the time/space continuum paradox theory with regard to getting mysql to do just that).

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

You should get the following output from curdate(), curtime() and now():

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2006-08-09 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:46:43  |
+-----------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2006-08-09 14:46:47 |
+---------------------+
1 row in set (0.00 sec)

Anything else is weird.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ClarkKent101]

Thank you both for your help ,

- CK

 

[KarveR]

If you want to store creation date and time, use a datetime field and set it to now() on creation, then your timestamp will be modified on changes but creation date will remain OK.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ClarkKent101]

Thanks for the help KarveR , the output of the functions you displayed in your previous post are all correct and i get the same when using the MySQL Console - when using the CURDATE() function in my INSERT statement, which i have done, the output that is shown on my webpage as well as in the actual record includes the '00:00:00' after the date. It isn't much of a problem, applying a mask resolves that issue but it's like you said - '... wierd' .

 

[KarveR]

if you insert curdate() into a datetime field, the remaining time will be defaulted to 00:00:00, instead insert now() to receive a correct time as well.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ClarkKent101]

Done .

Thanks for the help,

- CK