Re: Creating Table with a Default Datetime field
At 10:38 27/06/2001 -0700, you wrote: Sorry, for quoting the manual but this might be just another example where it could have the answer right away: Hannes http://www.mysql.com/doc/D/A/DATETIME.html On my own mysql version : Date TIMESTAMP default now(), Works correctly, the date is MMDDHHmmss format, but the values are set automattically and correctly, so, could it be your mysql version ? -- System Info : Red Hat Linux release 6.2 (Zoot) Kernel 2.2.14-5.0 on an i686 Mysql Ver 11.12 Distrib 3.23.32 java version 1.3.0 Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0) Java HotSpot(TM) Client VM (build 1.3.0, mixed mode) JDBC Driver : mm.mysql-2.0.4-bin.jar Tomcat 3.2 beta 3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Table with a Default Datetime field
At 11:57 AM +0200 6/28/01, Kaneda K wrote: At 10:38 27/06/2001 -0700, you wrote: Sorry, for quoting the manual but this might be just another example where it could have the answer right away: Hannes http://www.mysql.com/doc/D/A/DATETIME.html On my own mysql version : Date TIMESTAMP default now(), Works correctly, the date is MMDDHHmmss format, but the values are set automattically and correctly, so, could it be your mysql version ? It works as you seem to expect, but not for the reason you think. If you try SHOW COLUMNS FROM tbl_name, I expect you'll find that MySQL ignored your default value specification and set it to NULL. In any case now() is a string, not a function call. What you've really attempted to do is set the default value to a literal string, not a function that evaluates to the current date and time. -- System Info : Red Hat Linux release 6.2 (Zoot) Kernel 2.2.14-5.0 on an i686 Mysql Ver 11.12 Distrib 3.23.32 java version 1.3.0 Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0) Java HotSpot(TM) Client VM (build 1.3.0, mixed mode) JDBC Driver : mm.mysql-2.0.4-bin.jar Tomcat 3.2 beta 3 -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Table with a Default Datetime field
TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Table with a Default Datetime field
Sorry, for quoting the manual but this might be just another example where it could have the answer right away: Hannes http://www.mysql.com/doc/D/A/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: *The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. *The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) *You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later: *Let MySQL set the column when the row is created. This will initialize it to the current date and time. *When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates. On 6/27/01 8:54 AM, Pete Harlan [EMAIL PROTECTED] wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http
Re: Creating Table with a Default Datetime field
I, c. Then you just have to INSERT INTO it NOW() manually, that works On Wed, 27 Jun 2001, Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Table with a Default Datetime field
Well, almost true. On insert, all timestamp fields are updated. On update, only the first timestamp field is updated. So, If you have two timestamp fields, you can get the result you want. Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Creating Table with a Default Datetime field
hi. an alternate solution is to have a table like this: Create table( UpdatedDatetimestamp(14), CreationDatetimestamp(14), Infovarchar(100) ); as mysql only automatically updates the _first_ timestamp column in a table. hth. -ravi. -Original Message- From: Sherzod Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 27, 2001 1:40 PM To: Pete Harlan Cc: Ravi Raman; Richard Bates; [EMAIL PROTECTED] Subject: Re: Creating Table with a Default Datetime field I, c. Then you just have to INSERT INTO it NOW() manually, that works On Wed, 27 Jun 2001, Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Creating Table with a Default Datetime field
Wait, I didn't get the following: an alternate solution is to have a table like this: Create table( UpdatedDatetimestamp(14), CreationDatetimestamp(14), Infovarchar(100) ); as mysql only automatically updates the _first_ timestamp column in a table. . Could you tell me more about it? And how does it solve thr problem? hth. -ravi. -Original Message- From: Sherzod Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 27, 2001 1:40 PM To: Pete Harlan Cc: Ravi Raman; Richard Bates; [EMAIL PROTECTED] Subject: Re: Creating Table with a Default Datetime field I, c. Then you just have to INSERT INTO it NOW() manually, that works On Wed, 27 Jun 2001, Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Creating Table with a Default Datetime field
hi. assuming all inserts and updates never specify a value for UpdatedDate or CreationDate, on the initial insert: mysql insert into table (Info) values ('blahblahblah'); they will both be set to '20010627125429' or something. any subsequent updates: mysql update table set Info='somethingelse' where Info='blahblahblah'; will leave CreationDate as its original value, and change UpdatedDate to whatever the current timestamp is because it is first in the table description/create definition. hth. -ravi. -Original Message- From: Sherzod Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 27, 2001 2:10 PM To: Ravi Raman Cc: Pete Harlan; Richard Bates; [EMAIL PROTECTED] Subject: RE: Creating Table with a Default Datetime field Wait, I didn't get the following: an alternate solution is to have a table like this: Create table( UpdatedDatetimestamp(14), CreationDatetimestamp(14), Infovarchar(100) ); as mysql only automatically updates the _first_ timestamp column in a table. . Could you tell me more about it? And how does it solve thr problem? hth. -ravi. -Original Message- From: Sherzod Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 27, 2001 1:40 PM To: Pete Harlan Cc: Ravi Raman; Richard Bates; [EMAIL PROTECTED] Subject: Re: Creating Table with a Default Datetime field I, c. Then you just have to INSERT INTO it NOW() manually, that works On Wed, 27 Jun 2001, Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Creating Table with a Default Datetime field
Thanks, ravi. Have I missed that in the documentation? If it is not there, could you guys include it please. I think It's a good one -sherzodR On Wed, 27 Jun 2001, Ravi Raman wrote: hi. assuming all inserts and updates never specify a value for UpdatedDate or CreationDate, on the initial insert: mysql insert into table (Info) values ('blahblahblah'); they will both be set to '20010627125429' or something. any subsequent updates: mysql update table set Info='somethingelse' where Info='blahblahblah'; will leave CreationDate as its original value, and change UpdatedDate to whatever the current timestamp is because it is first in the table description/create definition. hth. -ravi. -Original Message- From: Sherzod Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 27, 2001 2:10 PM To: Ravi Raman Cc: Pete Harlan; Richard Bates; [EMAIL PROTECTED] Subject: RE: Creating Table with a Default Datetime field Wait, I didn't get the following: an alternate solution is to have a table like this: Create table( UpdatedDatetimestamp(14), CreationDatetimestamp(14), Infovarchar(100) ); as mysql only automatically updates the _first_ timestamp column in a table. . Could you tell me more about it? And how does it solve thr problem? hth. -ravi. -Original Message- From: Sherzod Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 27, 2001 1:40 PM To: Pete Harlan Cc: Ravi Raman; Richard Bates; [EMAIL PROTECTED] Subject: Re: Creating Table with a Default Datetime field I, c. Then you just have to INSERT INTO it NOW() manually, that works On Wed, 27 Jun 2001, Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request
Re: Creating Table with a Default Datetime field
Only the first timestamp is updated by MYSQL. Create two timestamp fields. MySQL will maintain the first one. Set timestamp2 to null when the record is created. Regards, Rich At 10:54 AM 6/27/01 -0500, Pete Harlan wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating Table with a Default Datetime field
How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Creating Table with a Default Datetime field
hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php