Re: setting the default of a date field
On 1/27/2012 6:00 PM, Peter Brawley wrote: On 1/27/2012 2:24 PM, william drescher wrote: On 1/27/2012 3:21 PM, Peter Brawley wrote: On 1/27/2012 11:18 AM, william drescher wrote: Is there a way to set the default of a date field to the date the record is generated ? bill Use a TIMESTAMP. Default DATE(TIME) columns with Triggers. PB When I try ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` `lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP I get: #1067 - Invalid default value for 'lastQuarterlyReview' Yes, if you want a default value use a timestamp column. Thanks Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: setting the default of a date field
On 1/27/2012 2:24 PM, william drescher wrote: On 1/27/2012 3:21 PM, Peter Brawley wrote: On 1/27/2012 11:18 AM, william drescher wrote: Is there a way to set the default of a date field to the date the record is generated ? bill Use a TIMESTAMP. Default DATE(TIME) columns with Triggers. PB When I try ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` `lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP I get: #1067 - Invalid default value for 'lastQuarterlyReview' Yes, if you want a default value use a timestamp column. PB - bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: setting the default of a date field
On 1/27/2012 3:21 PM, Peter Brawley wrote: On 1/27/2012 11:18 AM, william drescher wrote: Is there a way to set the default of a date field to the date the record is generated ? bill Use a TIMESTAMP. Default DATE(TIME) columns with Triggers. PB When I try ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` `lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP I get: #1067 - Invalid default value for 'lastQuarterlyReview' bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: setting the default of a date field
On 1/27/2012 11:18 AM, william drescher wrote: Is there a way to set the default of a date field to the date the record is generated ? bill Use a TIMESTAMP. Default DATE(TIME) columns with Triggers. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
' . . ' can't insert into a date field,but update can
Hi,
table table1 int1 int auto_increament ,
date date not null default '-00-00'
1) insert into table1 (date) values('. . ')
// shows Incorrect date value: '. . ' for column date' at row 1
// '. . ' ==> space(4)+"."+space(2)+"."+space(2)
2) update table1 set date=' . . '
// this done .
I hope can insert a '. . ' value to a date field .
Mysql 5.0.51
Regards!
Shuming Wang
Re: optional date field and NULL
This always happens to me. I end up solving my own problem a second
after I hit the send button to the mailing list.
The answer was that I needed single quotes around my first parameter
'{date_of_birth}' like this:
INSERT INTO enrollments (Name, DateOfBirth)
VALUES ('Joe', (SELECT IF(LENGTH('{date_of_birth}') = 0, NULL,
'{date_of_birth}')))
Thanks again,
Jim
James Neff wrote:
I have a table like so:
enrollments
-
ID - INT
Name - VARCHAR(45)
DateOfBirth - DateTime
The DateOfBirth field should be optional and allow NULLs if there is
nothing present.
How do I do this in my INSERT?
I've tried:
INSERT INTO enrollments (Name, DateOfBirth)
VALUES ('Joe', (SELECT IF(LENGTH({date_of_birth}) = 0, NULL,
'{date_of_birth}')))
The {date_of_birth} is a variable and is provided by the client
application. I can assure that is either a valid date format or empty
string.
Error I am getting:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ') = 0, NULL, '')),
Is there a better way to handle optional dates that I am missing?
Constructive criticism welcome.
Thanks,
--
James Neff
Technology Specialist
Tethys Health Ventures
4 North Park Drive, Suite 203
Hunt Valley, MD 21030
office: 410.771.0692 x103
cell:443.865.7874
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optional date field and NULL
I have a table like so:
enrollments
-
ID - INT
Name - VARCHAR(45)
DateOfBirth - DateTime
The DateOfBirth field should be optional and allow NULLs if there is
nothing present.
How do I do this in my INSERT?
I've tried:
INSERT INTO enrollments (Name, DateOfBirth)
VALUES ('Joe', (SELECT IF(LENGTH({date_of_birth}) = 0, NULL,
'{date_of_birth}')))
The {date_of_birth} is a variable and is provided by the client
application. I can assure that is either a valid date format or empty
string.
Error I am getting:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ') = 0,
NULL, '')),
Is there a better way to handle optional dates that I am missing?
Constructive criticism welcome.
Thanks,
--
James Neff
Technology Specialist
Tethys Health Ventures
4 North Park Drive, Suite 203
Hunt Valley, MD 21030
office: 410.771.0692 x103
cell:443.865.7874
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Defaulting Date Field
Jesse, a TIMESTAMP column in your table can be set to have the 'now' value when a row is inserted, or updated, or both. http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html Note the warning about TIMESTAMP behavior differing significantly prior to 4.1. Dan Jesse wrote: What is the best way to default a date/time field to the current date/time? I've tried using Now(), but I get an error of course. The only other way I know of to do this is to add a trigger, which I can do, but I find them bothersome, because they don't tend to backup and restore properly. Is there another way to do this? Thanks, Jesse -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Defaulting Date Field
What is the best way to default a date/time field to the current date/time? I've tried using Now(), but I get an error of course. The only other way I know of to do this is to add a trigger, which I can do, but I find them bothersome, because they don't tend to backup and restore properly. Is there another way to do this? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to default ... DATE column to another column date field, without using a trigger?
I have a similar desire like my first question regarding a DATETIME field. I have a DATETIME field in my db that I would like to DEFAULT to the exact value of another DATETIME field in the same table... However, if I write a trigger for this and the application or the user or someone accessing the table specifies a value for the second DATETIME field then I imagine the trigger would always overwrite it well I imagine if I take advantage of the mew.field_name and old.field_name values of the db, I could find away to allow someone to specify ... But I guess again, it seems as though something like this should be able to be configured on the command line when inititally creating the talbe or the column without having to manually write a trigger for it... youshould be able to just say something like, for instance, " ALTER TABLE tablename ADD COLUMN start_date DATE, cut_off_date DATE DEFAULT start_date " It would be cool if you could do that. Ferindo On 5/19/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote: Thanks Jay. Yeah, I'll just write a trigger... It sure would be cool though if you could say something like..." ADD COLUMN new_column VARCHAR(100) DEFAULT LOWER(new_column) " and then be done without having to explicitly define a trigger for it. This is for a userid field. Userid values at my org must be lowercase so we can ensure that the userid values are portable and can be used exactly as caputred within the db accross all our various systems... I just didn't want developers/admins to have to worry about making/forcing this integrity checks within the application and even if there's some person that accidentally INSERTs records on the command line without following this all-lowercase requirement. Ferindo On 5/19/06, Jay Pipes <[EMAIL PROTECTED]> wrote: > You could always use a trigger on BEFORE UPDATE/BEFORE INSERT which > changes NEW.column to LOWER(column). I know it's not declarative, but > it works. > > sheeri kritzer wrote: > > Not that I know of, but if you don't do binary (case-sensitive) > > searching then does it really matter?? You can retrieve with LOWER, or > > put it in your application, if your application needs to display it > > that way. > > > > -Sheeri > > > > On 5/18/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote: > >> I have column and I want to make sure the db is always making sure > the > >> value > >> that gets input into this VARCHAR() column is always lowercase; > >> > >> Is there a way to set the value of a column within a table to > >> automatically > >> be lowercase. I know how to use the LOWER() function when performing > >> queries > >> but is there a way to define LOWER( ) within the definition of the > table > >> column itself without having the application specify LOWER( ) to any > >> value > >> passed to this column or if a record had to be manually input and the > > >> admin > >> forgot to make sure all the characters were lowercase. > >> > >> Ferindo > >> > >> > > > > -- > Jay Pipes > Community Relations Manager, North America, MySQL Inc. > Roaming North America, based in Columbus, Ohio > email: [EMAIL PROTECTED]mob: +1 614 406 1267 > > Are You MySQL Certified? http://www.mysql.com/certification > Got Cluster? http://www.mysql.com/cluster -- Ferindo Middleton Technical Lead - Research and AUI Infrastructure Development Sleekcollar Software
Re: Date Field Reverting to 0000-00-00 Format
Michael Stassen wrote: So, take a look at yarn_date.txt and let us know. Yes, you're right . . . there was an extra tab stop. When I deleted the extra tab, the date field were retained successfully. Thanks! -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Field Reverting to 0000-00-00 Format
Lola J. Lee Beno wrote: Here's an example of one of the tables where the date was retained successfully, with the string to the right of the decimal point being trimmed: 1Cobweb2005-01-13 15:21:50.654149 2Lace Weight2005-01-13 15:21:50.654149 3Sock2005-01-13 15:21:50.654149 And the CREATE query for this table: CREATE TABLE StandardWeightType ( standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_desc varchar(50) NULL, standard_wt_lud datetime NULL, PRIMARY KEY (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; select * from standardweighttype: +-+--+-+ | standard_wt_type_id | standard_wt_desc | standard_wt_lud | +-+--+-+ | 1 | Cobweb | 2005-01-13 15:21:50 | | 2 | Lace Weight | 2005-01-13 15:21:50 | | 3 | Sock | 2005-01-13 15:21:50 | | 4 | Fingering| 2005-01-13 15:21:50 | | 5 | Baby | 2005-01-13 15:21:50 | | 6 | Sport| 2005-01-13 15:21:50 | | 7 | Double Knitting | 2005-01-13 15:21:50 | | 8 | Worsted | 2005-01-13 15:21:50 | | 9 | Aran | 2005-01-13 15:21:50 | +-+--+-+ I'm not sure why it worked for this table and not the Yarn table. I'll try out the proposed solutions later today. I do need to be able to use the date column since I will be inserting new records into the database via a web application that I will be developing; I am moving the data over from a PostgreSQL database which I'd developed earlier since I've decided to concentrate on MySQL for the time being. I take it back. It is true that the all-zero datetime is what you get for invalid input, so I jumped to the conclusion (sorry) that mysql was treating your datetimes with decimals as invalid. Your reply prompted me to try it, and I found, to my surprise, that mysql simply dropped the decimals. So, why isn't this happening for the yarn data? My best guess is that there is an extra tab right before the datetimes in yarn_date.txt. When you do the import, does mysql report any warnings? With an extra tab in each row of your 3-line sample data, I get Query OK, 3 rows affected, 6 warnings (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 6 at the end. If you have mysql 4.1 or higher, you can run SHOW WARNINGS; to get the details. With one extra tab per row, I got +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 1 | | Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns | | Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 2 | | Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns | | Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 3 | | Warning | 1262 | Row 3 was truncated; it contained more data than there were input columns | +-+--+---+ 6 rows in set (0.00 sec) So, take a look at yarn_date.txt and let us know. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Field Reverting to 0000-00-00 Format
Michael Stassen wrote: Just a quick reply for now . . . Ummm, if you delete the numbers to the *left* of the decimal point, "2005-01-15 10:15:42.41837" will turn into ".41837", which is still not a valid datetime. You need to delete the numbers to the *right* of the decimal point (which I expect you meant), *and* you need to delete the decimal point. Then you'll have a valid datetime (e.g. "2005-01-15 10:15:42"). I meant to say to the *right*, including the decimal point. I still get the date column set to -00-00 when I upload the edited file. Here's an example of one of the tables where the date was retained successfully, with the string to the right of the decimal point being trimmed: 1 Cobweb 2005-01-13 15:21:50.654149 2 Lace Weight 2005-01-13 15:21:50.654149 3 Sock2005-01-13 15:21:50.654149 And the CREATE query for this table: CREATE TABLE StandardWeightType ( standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_desc varchar(50) NULL, standard_wt_lud datetime NULL, PRIMARY KEY (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; select * from standardweighttype: +-+--+-+ | standard_wt_type_id | standard_wt_desc | standard_wt_lud | +-+--+-+ | 1 | Cobweb | 2005-01-13 15:21:50 | | 2 | Lace Weight | 2005-01-13 15:21:50 | | 3 | Sock | 2005-01-13 15:21:50 | | 4 | Fingering| 2005-01-13 15:21:50 | | 5 | Baby | 2005-01-13 15:21:50 | | 6 | Sport| 2005-01-13 15:21:50 | | 7 | Double Knitting | 2005-01-13 15:21:50 | | 8 | Worsted | 2005-01-13 15:21:50 | | 9 | Aran | 2005-01-13 15:21:50 | +-+--+-+ I'm not sure why it worked for this table and not the Yarn table. I'll try out the proposed solutions later today. I do need to be able to use the date column since I will be inserting new records into the database via a web application that I will be developing; I am moving the data over from a PostgreSQL database which I'd developed earlier since I've decided to concentrate on MySQL for the time being. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Field Reverting to 0000-00-00 Format
Lola J. Lee Beno wrote:
I have a bunch of data where one of the columns is a date field. Here's
a sample of the data that I have:
141415010001 02005-01-15 10:15:42.41837
281512010002 02005-01-15 10:22:37.756594
361635020004 02005-01-15 10:27:26.559838
When I run this query:
LOAD DATA LOCAL INFILE
'/users/lolajl/documents/development/knitlib/datafiles/yarn_date.txt'
INTO TABLE yarn
(yarn_id, standard_wt_type_id, brand_id, yarn_yardage, mfr_id,
yarn_meters, yarn_putup, yarn_wt_g, yarn_wt_oz, yarn_discontinued_flg,
yarn_lud);
The dates all get set to:
-00-00 00:00:00
-00-00 00:00:00 is what you get when you try to insert an invalid datetime.
Valid datetimes don't have decimals. See the manual for details
<http://dev.mysql.com/doc/refman/5.0/en/datetime.html>.
As you can see:
| 1 | 4 | 14 | 150 | 1 | 0 | 0 | 0 | 1 | 0 | -00-00 00:00:00 |
| 2 | 8 | 15 | 120 | 1 | 0 | 0 | 0 | 2 | 0 | -00-00 00:00:00 |
| 3 | 6 | 16 | 350 | 2 | 0 | 0 | 0 | 4 | 0 | -00-00 00:00:00 |
(I've deleted as many spaces as I could so as to make this more readable.)
When I delete the numbers to the left of the decimal point in the date
field in yarn_date.txt, it still gets set to the above format. I tried
setting the yarn_lud column to NULL and still the same thing.
Ummm, if you delete the numbers to the *left* of the decimal point,
"2005-01-15 10:15:42.41837" will turn into ".41837", which is still not a valid
datetime. You need to delete the numbers to the *right* of the decimal point
(which I expect you meant), *and* you need to delete the decimal point. Then
you'll have a valid datetime (e.g. "2005-01-15 10:15:42").
Alternatively, you can import your data into a table with a string column in the
place of yarn_lud. Something like
datestring CHAR(28)
should do. Then you can set yarn_lud to
LEFT(datestring, 19)
or, if necessary,
LEFT(datestring, LOCATE('.', datestring) - 1)
Adding NULL to the definition of yarn_lud only means that NULLs are allowed. It
has no bearing on correct datetime format, nor on the default value for invalid
datetimes.
Here is the query that creates this table:
CREATE TABLE Yarn (
yarn_id int UNSIGNED NOT NULL AUTO_INCREMENT,
standard_wt_type_id int UNSIGNED NULL,
brand_idint UNSIGNED NULL,
yarn_yardage int NULL,
mfr_id int UNSIGNED NULL,
yarn_meters int NULL,
yarn_putup varchar(35) NULL,
yarn_wt_gint NULL,
yarn_wt_oz int NULL,
yarn_discontinued_flg tinyint NULL,
yarn_lud datetime NULL,
PRIMARY KEY (yarn_id),
CONSTRAINT `fk_yarn_brandid` FOREIGN KEY (brand_id)
REFERENCES Brand (brand_id),
CONSTRAINT `fk_yarn_mfrid` FOREIGN KEY (mfr_id)
REFERENCES Manufacturer (mfr_id),
CONSTRAINT `fk_yarn_stwgttypid` FOREIGN KEY (standard_wt_type_id)
REFERENCES StandardWeightType
(standard_wt_type_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
The odd thing is that I have three tables with a column for the date and
the dates are retained properly. What could be causing the dates to be
converted to the -00-00 format automatically?
I'm not sure what you think is odd about datetime columns behaving as expected,
but I am sure you're getting the zero datetime because of invalid input.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Field Reverting to 0000-00-00 Format
I have a bunch of data where one of the columns is a date field. Here's a sample of the data that I have: 1 4 14 150 1 0 0 0 1 0 2005-01-15 10:15:42.41837 2 8 15 120 1 0 0 0 2 0 2005-01-15 10:22:37.756594 3 6 16 350 2 0 0 0 4 0 2005-01-15 10:27:26.559838 When I run this query: LOAD DATA LOCAL INFILE '/users/lolajl/documents/development/knitlib/datafiles/yarn_date.txt' INTO TABLE yarn (yarn_id, standard_wt_type_id, brand_id, yarn_yardage, mfr_id, yarn_meters, yarn_putup, yarn_wt_g, yarn_wt_oz, yarn_discontinued_flg, yarn_lud); The dates all get set to: -00-00 00:00:00 As you can see: | 1 | 4 |14 | 150 | 1 | 0 | 0| 0 | 1 |0 | -00-00 00:00:00 | | 2 | 8 |15 | 120 | 1 | 0 | 0| 0 | 2 |0 | -00-00 00:00:00 | | 3 | 6 |16 | 350 | 2 | 0 | 0| 0 | 4 |0 | -00-00 00:00:00 | (I've deleted as many spaces as I could so as to make this more readable.) When I delete the numbers to the left of the decimal point in the date field in yarn_date.txt, it still gets set to the above format. I tried setting the yarn_lud column to NULL and still the same thing. Here is the query that creates this table: CREATE TABLE Yarn ( yarn_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_type_id int UNSIGNED NULL, brand_idint UNSIGNED NULL, yarn_yardage int NULL, mfr_id int UNSIGNED NULL, yarn_meters int NULL, yarn_putup varchar(35) NULL, yarn_wt_gint NULL, yarn_wt_oz int NULL, yarn_discontinued_flg tinyint NULL, yarn_lud datetime NULL, PRIMARY KEY (yarn_id), CONSTRAINT `fk_yarn_brandid` FOREIGN KEY (brand_id) REFERENCES Brand (brand_id), CONSTRAINT `fk_yarn_mfrid` FOREIGN KEY (mfr_id) REFERENCES Manufacturer (mfr_id), CONSTRAINT `fk_yarn_stwgttypid` FOREIGN KEY (standard_wt_type_id) REFERENCES StandardWeightType (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; The odd thing is that I have three tables with a column for the date and the dates are retained properly. What could be causing the dates to be converted to the -00-00 format automatically? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE field key depends on value?
Thanx, that's exactly it. I feel a bit embarassed as this came up on
the list about 2-3 weeks ago, and I found the answer as I was waiting
for the replies.
-Sheeri
On 1/20/06, gerald_clark <[EMAIL PROTECTED]> wrote:
> sheeri kritzer wrote:
>
> >Hi folks,
> >
> >I'm attempting to optimize a query -- it's quite a simple one, actually.
> >
> >SELECT uid from Bill_Sales WHERE startDate > '[some date]';
> >
> >mysql> show create table Bill_Sales\G
> >*** 1. row ***
> > Table: Bill_Sales
> >Create Table: CREATE TABLE `Bill_Sales` (
> > `sales_id` int(4) unsigned NOT NULL auto_increment,
> > `uid` int(10) unsigned NOT NULL default '0',
> > `created` datetime NOT NULL default '-00-00 00:00:00',
> > `modified` timestamp NOT NULL default '-00-00 00:00:00',
> > `startDate` date NOT NULL default '-00-00',
> > `endDate` date NOT NULL default '-00-00',
> > `typesale` enum('pos','void','chargeback','refunded') default NULL,
> > PRIMARY KEY (`sales_id`),
> > KEY `uid` (`uid`),
> > KEY `startDate` (`startDate`,`endDate`,`typesale`),
> > KEY `endDate` (`endDate`,`startDate`,`typesale`)
> >) ENGINE=MyISAM DEFAULT CHARSET=latin1
> >1 row in set (0.00 sec)
> >
> >mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-22';
> >++-++--+---+--+-+--+-+-+
> >| id | select_type | table | type | possible_keys | key |
> >key_len | ref | rows| Extra |
> >++-++--+---+--+-+--+-+-+
> >| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
> >NULL | NULL | 1028766 | Using where |
> >++-++--+---+--+-+--+-+-+
> >1 row in set (0.00 sec)
> >
> >mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-23';
> >++-++---+---+---+-+--++-+
> >| id | select_type | table | type | possible_keys | key |
> >key_len | ref | rows | Extra |
> >++-++---+---+---+-+--++-+
> >| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
> > 3 | NULL | 192022 | Using where |
> >++-++---+---+---+-+--++-+
> >1 row in set (0.00 sec)
> >
> >The cutoff date for using the index versus not using the index is
> >around 2 months ago!
> >
> >This always happens on the production server, but I cannot get it to
> >work if I recreate the table without all the data. However, we've
> >replicated the data to a few machines, and the explains are consistent
> >with the replicated data.
> >
> >So I run a REPAIR TABLE, which should fix the indexes. It definitely
> >changed something, because now the cutoff date is about a week ago.
> >
> >mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-13";
> >++-++--+---+--+-+--+-+-+
> >| id | select_type | table | type | possible_keys | key |
> >key_len | ref | rows| Extra |
> >++-++--+---+--+-+--+-+-+
> >| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
> >NULL | NULL | 1028777 | Using where |
> >++-++--+---+--+-+--+-+-+
> >1 row in set (0.00 sec)
> >
> >mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-14";
> >++-++---+---+---+-+--++-+
> >| id | select_type | table | type | possible_keys | key |
> >key_len | ref | rows | Extra |
> >++-++---+---+---+-+--++-+
> >| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
> > 3 | NULL | 190891 | Using where |
> >++-++---+---+---+-+--++-+
> >1 row in set (0.00 sec)
> >
> >Why would the query do this? And why does it change when I run a repair
> >table?
> >
> >(show status:
> >mysql> show status;
> >+++
> >| Variable_name | Value |
> >+++
> >| Aborted_clients| 74279 |
> >| Aborted_connects | 146|
> >| Binlog_cache_disk_use | 0 |
> >| Binlog_cache_use | 0 |
> >| Bytes_received | 1163526992 |
> >| Bytes_sent | 359522512 |
> >| Com_admin_commands | 0 |
>
Re: DATE field key depends on value?
sheeri kritzer wrote:
Hi folks,
I'm attempting to optimize a query -- it's quite a simple one, actually.
SELECT uid from Bill_Sales WHERE startDate > '[some date]';
mysql> show create table Bill_Sales\G
*** 1. row ***
Table: Bill_Sales
Create Table: CREATE TABLE `Bill_Sales` (
`sales_id` int(4) unsigned NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '-00-00 00:00:00',
`modified` timestamp NOT NULL default '-00-00 00:00:00',
`startDate` date NOT NULL default '-00-00',
`endDate` date NOT NULL default '-00-00',
`typesale` enum('pos','void','chargeback','refunded') default NULL,
PRIMARY KEY (`sales_id`),
KEY `uid` (`uid`),
KEY `startDate` (`startDate`,`endDate`,`typesale`),
KEY `endDate` (`endDate`,`startDate`,`typesale`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-22';
++-++--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows| Extra |
++-++--+---+--+-+--+-+-+
| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
NULL | NULL | 1028766 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)
mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-23';
++-++---+---+---+-+--++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-++---+---+---+-+--++-+
| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
3 | NULL | 192022 | Using where |
++-++---+---+---+-+--++-+
1 row in set (0.00 sec)
The cutoff date for using the index versus not using the index is
around 2 months ago!
This always happens on the production server, but I cannot get it to
work if I recreate the table without all the data. However, we've
replicated the data to a few machines, and the explains are consistent
with the replicated data.
So I run a REPAIR TABLE, which should fix the indexes. It definitely
changed something, because now the cutoff date is about a week ago.
mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-13";
++-++--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows| Extra |
++-++--+---+--+-+--+-+-+
| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
NULL | NULL | 1028777 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)
mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-14";
++-++---+---+---+-+--++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-++---+---+---+-+--++-+
| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
3 | NULL | 190891 | Using where |
++-++---+---+---+-+--++-+
1 row in set (0.00 sec)
Why would the query do this? And why does it change when I run a repair table?
(show status:
mysql> show status;
+++
| Variable_name | Value |
+++
| Aborted_clients| 74279 |
| Aborted_connects | 146|
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1163526992 |
| Bytes_sent | 359522512 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table| 45 |
| Com_analyze| 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 154039613 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create
Re: DATE field key depends on value?
Realized I should probably show the Bill_Sales table. . .
ls -lh Bill_Sales.*
-rw-rw 1 mysql mysql 104M Jan 20 15:11 Bill_Sales.MYD
-rw-rw 1 mysql mysql 97M Jan 20 15:11 Bill_Sales.MYI
-rw-rw 1 mysql mysql 9.2K Jan 3 13:43 Bill_Sales.frm
mysql> show table status like "Bill_Sales"\G
*** 1. row ***
Name: Bill_Sales
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 1028800
Avg_row_length: 105
Data_length: 108068128
Max_data_length: 4294967295
Index_length: 100814848
Data_free: 0
Auto_increment: 1058746
Create_time: 2006-01-03 13:43:04
Update_time: 2006-01-20 15:10:31
Check_time: 2006-01-20 14:48:01
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
On 1/20/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
> Hi folks,
>
> I'm attempting to optimize a query -- it's quite a simple one, actually.
>
> SELECT uid from Bill_Sales WHERE startDate > '[some date]';
>
> mysql> show create table Bill_Sales\G
> *** 1. row ***
>Table: Bill_Sales
> Create Table: CREATE TABLE `Bill_Sales` (
> `sales_id` int(4) unsigned NOT NULL auto_increment,
> `uid` int(10) unsigned NOT NULL default '0',
> `created` datetime NOT NULL default '-00-00 00:00:00',
> `modified` timestamp NOT NULL default '-00-00 00:00:00',
> `startDate` date NOT NULL default '-00-00',
> `endDate` date NOT NULL default '-00-00',
> `typesale` enum('pos','void','chargeback','refunded') default NULL,
> PRIMARY KEY (`sales_id`),
> KEY `uid` (`uid`),
> KEY `startDate` (`startDate`,`endDate`,`typesale`),
> KEY `endDate` (`endDate`,`startDate`,`typesale`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-22';
> ++-++--+---+--+-+--+-+-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows| Extra |
> ++-++--+---+--+-+--+-+-+
> | 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
> NULL | NULL | 1028766 | Using where |
> ++-++--+---+--+-+--+-+-+
> 1 row in set (0.00 sec)
>
> mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-23';
> ++-++---+---+---+-+--++-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> ++-++---+---+---+-+--++-+
> | 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
> 3 | NULL | 192022 | Using where |
> ++-++---+---+---+-+--++-+
> 1 row in set (0.00 sec)
>
> The cutoff date for using the index versus not using the index is
> around 2 months ago!
>
> This always happens on the production server, but I cannot get it to
> work if I recreate the table without all the data. However, we've
> replicated the data to a few machines, and the explains are consistent
> with the replicated data.
>
> So I run a REPAIR TABLE, which should fix the indexes. It definitely
> changed something, because now the cutoff date is about a week ago.
>
> mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-13";
> ++-++--+---+--+-+--+-+-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows| Extra |
> ++-++--+---+--+-+--+-+-+
> | 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
> NULL | NULL | 1028777 | Using where |
> ++-++--+---+--+-+--+-+-+
> 1 row in set (0.00 sec)
>
> mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-14";
> ++-++---+---+---+-+--++-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> ++-++---+---+---+-+--++-+
> | 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
> 3 | NULL | 190891 | Using where |
> ++-++---+---+---+-+--++-+
> 1 row in set (0.00 sec)
>
> Why would the query
DATE field key depends on value?
Hi folks,
I'm attempting to optimize a query -- it's quite a simple one, actually.
SELECT uid from Bill_Sales WHERE startDate > '[some date]';
mysql> show create table Bill_Sales\G
*** 1. row ***
Table: Bill_Sales
Create Table: CREATE TABLE `Bill_Sales` (
`sales_id` int(4) unsigned NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '-00-00 00:00:00',
`modified` timestamp NOT NULL default '-00-00 00:00:00',
`startDate` date NOT NULL default '-00-00',
`endDate` date NOT NULL default '-00-00',
`typesale` enum('pos','void','chargeback','refunded') default NULL,
PRIMARY KEY (`sales_id`),
KEY `uid` (`uid`),
KEY `startDate` (`startDate`,`endDate`,`typesale`),
KEY `endDate` (`endDate`,`startDate`,`typesale`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-22';
++-++--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows| Extra |
++-++--+---+--+-+--+-+-+
| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
NULL | NULL | 1028766 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)
mysql> explain SELECT uid from Bill_Sales WHERE startDate > '2005-11-23';
++-++---+---+---+-+--++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-++---+---+---+-+--++-+
| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
3 | NULL | 192022 | Using where |
++-++---+---+---+-+--++-+
1 row in set (0.00 sec)
The cutoff date for using the index versus not using the index is
around 2 months ago!
This always happens on the production server, but I cannot get it to
work if I recreate the table without all the data. However, we've
replicated the data to a few machines, and the explains are consistent
with the replicated data.
So I run a REPAIR TABLE, which should fix the indexes. It definitely
changed something, because now the cutoff date is about a week ago.
mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-13";
++-++--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows| Extra |
++-++--+---+--+-+--+-+-+
| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
NULL | NULL | 1028777 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)
mysql> explain SELECT uid from Bill_Sales WHERE startDate > "2006-01-14";
++-++---+---+---+-+--++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-++---+---+---+-+--++-+
| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
3 | NULL | 190891 | Using where |
++-++---+---+---+-+--++-+
1 row in set (0.00 sec)
Why would the query do this? And why does it change when I run a repair table?
(show status:
mysql> show status;
+++
| Variable_name | Value |
+++
| Aborted_clients| 74279 |
| Aborted_connects | 146|
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1163526992 |
| Bytes_sent | 359522512 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table| 45 |
| Com_analyze| 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 154039613 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function
RE: Format for saving date field.
What is the source of the data that is displayed on the screen. If it is
a field in a MySQL table and the data type for that field is either DATE
or DATETIME then it will intsert/update without any manipulation.
Try doing a
SELECT datefield
FROM table
Limit 15;
outside of your ASP.NET environment. {Command line, SQLYOG, Query
Browser etc.}
-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 10, 2005 1:49 PM
To: MySQL List
Subject: Format for saving date field.
When doing an update or insert into a database with a date field, the
format
for the data on the screen is m/d/. However, I believe that MySQL
is
expecting it in the format of -mm-dd. I'm using MySQL in an ASP.Net
application. Is there an EASY way to convert to the data to a format
that
MySQL will allow either with some MySQL function, or with an ASP.NET
function? I realize that I could "rip the data apart", and put it back
in
the same format that MySQL is looking for, but there's got to be some
easier
way.
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Format for saving date field.
When doing an update or insert into a database with a date field, the format for the data on the screen is m/d/. However, I believe that MySQL is expecting it in the format of -mm-dd. I'm using MySQL in an ASP.Net application. Is there an EASY way to convert to the data to a format that MySQL will allow either with some MySQL function, or with an ASP.NET function? I realize that I could "rip the data apart", and put it back in the same format that MySQL is looking for, but there's got to be some easier way. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LATEST N RECORDS from a table without date field
On Wed, 24 Aug 2005, Harald Fuchs wrote: "Praveen KS" <[EMAIL PROTECTED]> writes: Can anyone help with a query to retrieve latest N records. No auto_increment field. No date field. Primary key exists and is populated with random unique values. This means that the only possible definition for "latest" is "highest slno". Translated to SQL: SELECT slno, name FROM tbl ORDER BY slno DESC LIMIT no, it was stated "random unique values" those cannot be sorted. what was asked for was SELECT * FROM table ORDER BY __INSERT__ DESC LIMIT N; depending on your db config you might be able to do this outside of sql by parsing the binary log. It is a history of updates to the database, in chronological order. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LATEST N RECORDS from a table without date field
If you don't order your data but specified a LIMIT clause, what are the records returned ? 24 Aug 2005 17:26:27 +0200, Harald Fuchs <[EMAIL PROTECTED]>: > In article <[EMAIL PROTECTED]>, > "Praveen KS" <[EMAIL PROTECTED]> writes: > > > Can anyone help with a query to retrieve latest N records. > > No auto_increment field. > > No date field. > > Primary key exists and is populated with random unique values. > > This means that the only possible definition for "latest" is "highest slno". > Translated to SQL: > > SELECT slno, name > FROM tbl > ORDER BY slno DESC > LIMIT > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LATEST N RECORDS from a table without date field
In article <[EMAIL PROTECTED]>, "Praveen KS" <[EMAIL PROTECTED]> writes: > Can anyone help with a query to retrieve latest N records. > No auto_increment field. > No date field. > Primary key exists and is populated with random unique values. This means that the only possible definition for "latest" is "highest slno". Translated to SQL: SELECT slno, name FROM tbl ORDER BY slno DESC LIMIT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LATEST N RECORDS from a table without date field
On 24/08/2005, "Praveen KS" wrote: > Can anyone help with a query to retrieve latest N records. > > No auto_increment field. > No date field. > Primary key exists and is populated with random unique values. In a table, there are no such things as - the first record - the twenty third record - the last record unless you provide for some ordering yourself (e.g. by including a timestamp). -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LATEST N RECORDS from a table without date field
Can anyone help with a query to retrieve latest N records. No auto_increment field. No date field. Primary key exists and is populated with random unique values. Eg: slno int(10) primary key (populated with random unique values) name char(20) Thanks in advance. PraveenKumarKS http://www.geocities.com/praveen_manja http://travel.indiainfo.com/img/banner-coolwaters.gif"; width="600" height="40" alt="" border="0" usemap="#banner"> http://travel.indiainfo.com/destination/beaches/goa.html";> http://travel.indiainfo.com/packages/beaches/goa-holidayinn.html";> http://travel.indiainfo.com/packages/beaches/goa-majorda.html";> http://travel.indiainfo.com/packages/beaches/goa-oldanchor.html";> http://travel.indiainfo.com/packages/beaches/goa-kamath.html";> http://travel.indiainfo.com/booknow.html";>
Re: key on the month portion of a date field
Remember, a low cardinality index will possibly be ignored by the optimizer and an index on month will never have a cardinality of more than 12. For testing purposes, you might try added a column for month and populating it off your current data. update the_table set the_field=MONTH(the_field) Then, add an index on that column and test your queries against the new index. I'd be surprised if you saw much increase in speed, especially as your data set grows. Greg On Wednesday 09 February 2005 09:17 am, Gabriel B. wrote: > Short Version: > is there any way to make an index for a date field that appears in the > WHERE as a MONTH() argument? > > > I have a table with some hundreds of thousands of rows already, and > now i have the need to show upcoming birthdays to some users. > > the query uses WHERE MONTH(birthday).. the `birthday` field is of date > (-00-00) type > > It is not too slow this way since i started the WHERE with lots of > checks that cuts down to a medium of 200 rows that actualy gets to > this check, but i feel uncorfotable to not use a index. > > So, is there any way to make an index for that kind of query, or i > must re-estruct the table to have a month field? > > Thanks! > Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
On Wed, 9 Feb 2005 15:17:36 -0200, Gabriel B. <[EMAIL PROTECTED]> wrote: > It is not too slow this way since i started the WHERE with lots of > checks that cuts down to a medium of 200 rows that actualy gets to > this check, but i feel uncorfotable to not use a index. Isn't there a limit of 1 index per table in a query? If you're already using where statements to eliminate rows, I'm assuming that you're already using an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
At 15:37 -0200 2/9/05, Gabriel B. wrote: On Wed, 09 Feb 2005 17:24:10 +, love <[EMAIL PROTECTED]> wrote: alter table table_name add index (birthday); But would that index improve this kind of query? the docs talk about only direct matchs, like "birthday < now()" or" birthday between x and y". They're all full date values. It won't help except for queries that test the value of birthday directly. As soon as you use the column in a calculation such as a function call, the index can't be used. To use month in indexed fashion, you could store dates as separate year, month, and date columns. But that might be more of a hassle than you want to deal with. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
On Wed, 09 Feb 2005 17:24:10 +, love <[EMAIL PROTECTED]> wrote: > alter table table_name add index (birthday); But would that index improve this kind of query? the docs talk about only direct matchs, like "birthday < now()" or" birthday between x and y". They're all full date values. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
use below: alter table table_name add index (birthday); Love Kumar .. [EMAIL PROTECTED] wrote: Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding & co-operation.
key on the month portion of a date field
Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with date field
This is on a Win2003 server system, and MySql server 4.1 ""Markus Grossrieder"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > What's going on here? > God knows ! > Maybe providing some information (OS, version, host app(if any), code > example, db description, etc.) would permit > some humble humans to take a guess ... > > - Original Message - > From: "Steve Grosz" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, November 05, 2004 8:13 AM > Subject: Problem with date field > > > > Why am I having a problem getting a date field to hold date? I will > store > > 2004-10-15, save the record. Reopen the record, and there is just > > -00-00 > > > > What's going on here? > > > > Thanks > > Steve > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with date field
CREATE TABLE `events` ( `eventID` tinyint(4) NOT NULL auto_increment, `eventDate` date NOT NULL default '-00-00', `eventTitle` tinytext NOT NULL, `eventDesc` mediumtext NOT NULL, PRIMARY KEY (`eventID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | "Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi. > If you send us output of "show create table 'table_with_date_field'", > and queries, which you use to insert and retrieve date, > may be we will be able to help you. > > > "Steve Grosz" <[EMAIL PROTECTED]> wrote: > > Why am I having a problem getting a date field to hold date? I will store > > 2004-10-15, save the record. Reopen the record, and there is just > > -00-00 > > > > What's going on here? > > > > Thanks > > Steve > > > > > > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ><___/ www.mysql.com > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with date field
Hi. If you send us output of "show create table 'table_with_date_field'", and queries, which you use to insert and retrieve date, may be we will be able to help you. "Steve Grosz" <[EMAIL PROTECTED]> wrote: > Why am I having a problem getting a date field to hold date? I will store > 2004-10-15, save the record. Reopen the record, and there is just > -00-00 > > What's going on here? > > Thanks > Steve > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with date field
> What's going on here? God knows ! Maybe providing some information (OS, version, host app(if any), code example, db description, etc.) would permit some humble humans to take a guess ... - Original Message - From: "Steve Grosz" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 05, 2004 8:13 AM Subject: Problem with date field > Why am I having a problem getting a date field to hold date? I will store > 2004-10-15, save the record. Reopen the record, and there is just > -00-00 > > What's going on here? > > Thanks > Steve > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with date field
Why am I having a problem getting a date field to hold date? I will store 2004-10-15, save the record. Reopen the record, and there is just -00-00 What's going on here? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] setting DATE field with default NOW value?
If you update the row with NULL for the TIMESTAMP field it will enter the current date and time. On Feb 5, 2004, at 7:18 AM, Martijn Tonies wrote: you can use TIMESTAMP data type, which have default NOW() Not exactly. 1) TIMESTAMP also updates when you update the row, unless you set the column value to its current value. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] setting DATE field with default NOW value?
> you can use TIMESTAMP data type, which have default NOW() Not exactly. 1) TIMESTAMP also updates when you update the row, unless you set the column value to its current value. 2) not in "maxdb" mode. http://www.mysql.com/doc/en/DATETIME.html > > In a table definition, how do I get a date field to be > > assigned as DEFAULT the current date? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] setting DATE field with default NOW value?
you can use TIMESTAMP data type, which have default NOW() - Original Message - From: "Riaan Oberholzer" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 05, 2004 12:38 PM Subject: [Q] setting DATE field with default NOW value? > In a table definition, how do I get a date field to be > assigned as DEFAULT the current date? > > I tried: > > dateField DATE DEFAULT CURDATE() > > but that gives me syntax errors > > Help? > > > __ > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online. > http://taxes.yahoo.com/filing.html > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] setting DATE field with default NOW value?
Hi, > In a table definition, how do I get a date field to be > assigned as DEFAULT the current date? > > I tried: > > dateField DATE DEFAULT CURDATE() > > but that gives me syntax errors > > Help? According to the documentation for the DEFAULT clause, can only be a real value. Not a function. So basically, you're out of luck. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] setting DATE field with default NOW value?
In a table definition, how do I get a date field to be assigned as DEFAULT the current date? I tried: dateField DATE DEFAULT CURDATE() but that gives me syntax errors Help? __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default DATE field values
Hi, Firstly, thanks for the reply. I'm aware that MySQL requires that default values be constants without reference to expressions that need to be evaluated and the fields in question will never be updated - it's a set-once thing. The reason I asked is due to the information in the MySQL manual stating they were related. The only reason I didn't use TIMESTAMP from the outset is because we don't want the time part of the field. I think we can spare the extra few bytes per row though, so I'll probably use TIMESTAMP fields anyway. Thanks again! Regards, Chris On Sun, 2004-01-04 at 11:13, Matt W wrote: > Hi Chris, > > Nope, DEFAULT values have to be constants; no functions or anything. > :-/ > > What are you trying to do? And what's wrong with using TIMESTAMP since > you want a default of NOW()? If it's because you don't want it update > when you UPDATE the row, you can just set it to its current value, if > you weren't aware of that. > > > Matt > > > - Original Message - > From: "Chris Nolan" > Sent: Saturday, January 03, 2004 10:34 AM > Subject: Default DATE field values > > > > Hi all, > > > > Upon reading the funky manual, I have discovered the following things: > > > > 1. TIMESTAMP fields can be set so that their default value is NOW(). > > 2. DATE and TIMESTAMP fields are related. > > > > Given the two above facts, is there a way to set DATE columns so the > > default value is NOW()? My playing around seems to have not produced > any > > fruitful results. > > > > Regards, > > > > Chris > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default DATE field values
Hi Chris, Nope, DEFAULT values have to be constants; no functions or anything. :-/ What are you trying to do? And what's wrong with using TIMESTAMP since you want a default of NOW()? If it's because you don't want it update when you UPDATE the row, you can just set it to its current value, if you weren't aware of that. Matt - Original Message - From: "Chris Nolan" Sent: Saturday, January 03, 2004 10:34 AM Subject: Default DATE field values > Hi all, > > Upon reading the funky manual, I have discovered the following things: > > 1. TIMESTAMP fields can be set so that their default value is NOW(). > 2. DATE and TIMESTAMP fields are related. > > Given the two above facts, is there a way to set DATE columns so the > default value is NOW()? My playing around seems to have not produced any > fruitful results. > > Regards, > > Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default DATE field values
Hi all, Upon reading the funky manual, I have discovered the following things: 1. TIMESTAMP fields can be set so that their default value is NOW(). 2. DATE and TIMESTAMP fields are related. Given the two above facts, is there a way to set DATE columns so the default value is NOW()? My playing around seems to have not produced any fruitful results. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date Field
Eduardo
Use the format '-mm-dd' instead
By the way, there is an excellent manual on http://www.mysql.com including
all the field types and formats
Freddie
-Original Message-
From: Eduardo Melo [mailto:[EMAIL PROTECTED]
Sent: Sonntag, 26. Oktober 2003 16:15
To: [EMAIL PROTECTED]
Hi All !
I have a datetime field in a table. When I use insert into table
(aniversary) values ('24/10/2003'). The date is save as 20/10/2024. I am
using dd/mm/ What can I do to save the date correctely ?
best regards,
Eduardo F. Melo
_
MSN Messenger: converse com os seus amigos online.
http://messenger.msn.com.br
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Field
Hi All !
I have a datetime field in a table. When I use insert into table
(aniversary) values ('24/10/2003'). The date is save as 20/10/2024. I am
using dd/mm/ What can I do to save the date correctely ?
best regards,
Eduardo F. Melo
_
MSN Messenger: converse com os seus amigos online.
http://messenger.msn.com.br
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pull updated Records without a date field?
You could add a column 'is_updated' and set it to 'Y' when it is updated. But depends very much on what you want to achieve by it /rudy -Original Message- From: Keith Hamilton [mailto:[EMAIL PROTECTED] Sent: donderdag 10 juli 2003 18:39 To: MySQL Subject: Pull updated Records without a date field? Hello, Is there a way to pull all updated records without having a date field in each record? -- Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pull updated Records without a date field?
> Is there a way to pull all updated records without having a date field in each record? No. Generally speaking it's a good idea to have a timestamp field in these oft updated tables to perform just the kind of operation you describe. Cheers, Andrew -Original Message- From: Keith Hamilton [mailto:[EMAIL PROTECTED] Sent: Thursday 10 July 2003 17:39 To: MySQL Subject: Pull updated Records without a date field? Hello, Is there a way to pull all updated records without having a date field in each record? -- Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pull updated Records without a date field?
--- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 11:38 -0500 7/10/03, Keith Hamilton wrote: > >Hello, > > > > Is there a way to pull all updated records without having a date > >field in each record? > >-- Keith Can't you just do: select field from table where datefield > somedate; That way the date field won't be displayed in the output. I think that will work, Ryan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pull updated Records without a date field?
At 11:38 -0500 7/10/03, Keith Hamilton wrote: Hello, Is there a way to pull all updated records without having a date field in each record? How do you determine which records have been updated in that case? -- Keith -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pull updated Records without a date field?
Hello, Is there a way to pull all updated records without having a date field in each record? -- Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Year of Date Field using sql statement
Hakkan Lui wrote:
>Dear all,
>
>How can I change the year of the date field using sql statement?
>
>It means something like:
>update table1 set year(f1) = '2002' where year(f1) = '2003';
>
>
If you have a field for _just_ the year, you can use the above statement.
If you have the year embedded in a date field which has other data (eg
days and months) then you'll have to use the substring function (or you
could get fancy with some of mysql's date functions, but I wouldn't in
this case).
MySQL stores dates in the format -mm-dd, so you just have to do
something like:
update table1 set year(f1) = concat('2003' , '-',
substring(year(f1),6,5)) where substring(year(f1),1,4)='2002';
Or something. I haven't checked the above, so beware...
Read up on concat.
Also I would reconsider using those brackets in the year field:
year(f1). I don't know whether brackets in a field name are supported,
but brackets usually indicate a function.
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
-
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: Update Year of Date Field using sql statement
At 22:56 -0500 1/5/03, Bhavin Vyas wrote:
I don't think there is a way to do that. Unless some one says there is(and
tells us how to do it), I guess your only option is to write a script to do
that.
Here's one way:
UPDATE table1
SET f1 = CONCAT('2002','-',MONTH(f1),'-',DAYOFMONTH(f1))
WHERE YEAR(f1) = 2003;
That is, synthesize the new date using the desire year, plus the
other parts of the existing date.
If you just want to subtract a year, do this:
UPDATE table1
SET f1 = DATE_SUB(f1,INTERVAL 1 YEAR)
WHERE YEAR(f1) = 2003;
Regards,
Bhavin.
- Original Message -
From: "Hakkan Lui" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, January 05, 2003 10:12 PM
Subject: Update Year of Date Field using sql statement
Dear all,
How can I change the year of the date field using sql statement?
It means something like:
update table1 set year(f1) = '2002' where year(f1) = '2003';
Thanks for anyone's help.
Regards,
> Hakkan Lui
-
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: Update Year of Date Field using sql statement
I don't think there is a way to do that. Unless some one says there is(and tells us how to do it), I guess your only option is to write a script to do that. Regards, Bhavin. - Original Message - From: "Hakkan Lui" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, January 05, 2003 10:12 PM Subject: Update Year of Date Field using sql statement > Dear all, > > How can I change the year of the date field using sql statement? > > It means something like: > update table1 set year(f1) = '2002' where year(f1) = '2003'; > > > Thanks for anyone's help. > > > > Regards, > Hakkan Lui > > > - > 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
Update Year of Date Field using sql statement
Dear all, How can I change the year of the date field using sql statement? It means something like: update table1 set year(f1) = '2002' where year(f1) = '2003'; Thanks for anyone's help. Regards, Hakkan Lui - 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: Date field
Hello. On Sun 2002-12-08 at 16:03:22 -0500, [EMAIL PROTECTED] wrote: > > I am trying to store a date in the following format '08/12/2002' pr > '08/12/2002' or any other format if I have to ...on SQLServer 7. This is the mysql list. What has SQLServer 7 to do with MySQL? > Basically I want to use > > TodaysDate = Date' Date is the system date function > > strSQL = "SELECT * FROM CallBacks WHERE CallDate =" & TodaysDate > > Is there any good way to follow please ?? Probably any RDBMS has a built-in date type. Declare CallDate to be of that type. For MySQL that would be DATE. Then you can use something like SELECT * FROM CallBacks WHERE CallDate = CURDATE() If you are really referring to a different RDBMS, CURDATE() may have a different name. HTH, Benjamin. -- [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
Date field
Hi. I am trying to store a date in the following format '08/12/2002' pr '08/12/2002' or any other format if I have to ...on SQLServer 7. Basically I want to use TodaysDate = Date' Date is the system date function strSQL = "SELECT * FROM CallBacks WHERE CallDate =" & TodaysDate Is there any good way to follow please ?? Many thanks in advance. Sam - 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: date field - default value = current date
At 1:32 -0700 9/18/02, neal wrote: >Is there a wat to have mySQL auto fill in the current date into my >dateCreated field? > >In SQLServer I would simply specify the getDate() method as a default value. >I presume I would use the analogous MySQL function curDate() but it does not >appear to work for me. It's better to read the manual than to presume. In this case, the presumption is incorrect. Default values in MySQL must be constants. However, you might find the TIMESTAMP column type helpful. I'll let you read about it in the manual. :-) http://www.mysql.com/doc/en/DATETIME.html > >Any suggestions? - 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: date field - default value = current date
neal, Wednesday, September 18, 2002, 11:32:39 AM, you wrote: n> Is there a wat to have mySQL auto fill in the current date into my n> dateCreated field? n> In SQLServer I would simply specify the getDate() method as a default value. n> I presume I would use the analogous MySQL function curDate() but it does not n> appear to work for me. You can't use function as a default value. May be TIMESTAMP column type is what you want, look at: http://www.mysql.com/doc/en/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - 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: date field - default value = current date
Check TIMESTAMP type in the documentation. Best regards, Mikhail. - Original Message - From: "neal" <[EMAIL PROTECTED]> To: "mySQL" <[EMAIL PROTECTED]> Sent: Wednesday, September 18, 2002 10:32 AM Subject: date field - default value = current date > Is there a wat to have mySQL auto fill in the current date into my > dateCreated field? > > In SQLServer I would simply specify the getDate() method as a default value. > I presume I would use the analogous MySQL function curDate() but it does not > appear to work for me. > > Any suggestions? > > > > - > 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
date field - default value = current date
Is there a wat to have mySQL auto fill in the current date into my dateCreated field? In SQLServer I would simply specify the getDate() method as a default value. I presume I would use the analogous MySQL function curDate() but it does not appear to work for me. Any suggestions? - 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: Default created date field
But timestamp type will change on every update. I only want the default value to be the current time/date when the row is inserted and then not change. >From: Georg Richter <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: [EMAIL PROTECTED], "Shawn Poulson" <[EMAIL PROTECTED]>, >[EMAIL PROTECTED] >Subject: Re: Default created date field >Date: Mon, 15 Jul 2002 17:56:18 +0200 > >On Monday, 15. July 2002 16:51, Shawn Poulson wrote: > >Hi, > > > I'm having trouble doing this in MySQL, but it keeps complaining. All I > > have to work on is: > > create table mytable ( > >createddate datetime not null default now() > > ) > > > >Just use timestamp instead datetime. > >Regards > >Georg > >mysql,query --- Shawn Poulson [EMAIL PROTECTED] _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - 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: Default created date field
On Monday, 15. July 2002 16:51, Shawn Poulson wrote: Hi, > I'm having trouble doing this in MySQL, but it keeps complaining. All I > have to work on is: > create table mytable ( >createddate datetime not null default now() > ) > Just use timestamp instead datetime. Regards Georg mysql,query - 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: Default created date field
Shawn, Monday, July 15, 2002, 5:51:01 PM, you wrote: SP> I'm new to this list and MySQL, so I hope this question isn't too SP> rudimentary. SP> In SQL Server I was able to create a datetime field that used the default SP> getdate() function so that I woudl get timestamps on all created rows SP> automatically, like: SP> create table mytable ( SP>createddate datetime not null default getdate() SP> ) SP> I'm having trouble doing this in MySQL, but it keeps complaining. All I SP> have to work on is: SP> create table mytable ( SP>createddate datetime not null default now() SP> ) SP> But it doesn't like now(). I can't find anything else to try in the MySQL SP> docs. You can't use functions for the default values. If you want to specify current date and time as a default value, take a look at TIMESTAMP column type: http://www.mysql.com/doc/D/A/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - 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
Default created date field
Hello all, I'm new to this list and MySQL, so I hope this question isn't too rudimentary. In SQL Server I was able to create a datetime field that used the default getdate() function so that I woudl get timestamps on all created rows automatically, like: create table mytable ( createddate datetime not null default getdate() ) I'm having trouble doing this in MySQL, but it keeps complaining. All I have to work on is: create table mytable ( createddate datetime not null default now() ) But it doesn't like now(). I can't find anything else to try in the MySQL docs. Any ideas? --- Shawn Poulson [EMAIL PROTECTED] _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - 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: MIN/MAX Date Field
Ok, well, I thought this was going to help me with my problem, but unfortunately, it didn't. That's not to say that the answer was incorrect. Here's what I'm trying to do (with a little background). I run a stats page for SETI@Home. It's mySQL driven, with perl generating static html pages. I store UserId, TotalWu, Last24Wu, Avg7Day, and DateStamp. Sometimes, however, the SETI servers are unreachable, and I have my stats pages insert default values if they can't be contacted. If this happens, then the Avg7Day field somehow gets corrupted (for a short time), and so the wrong information is displayed. I want to write a script that will update all the records Avg7Day field. Basically, the Avg7Day field should equal this formula: (TotalWu (from current DateStamp) - TotalWu (from DateStamp - 7 Days)) / 7 Which is simple enough. However, if there are less than 7 days of data (or we just haven't gotten to the 7th day yet, it should then be: (TotalWu (from current DateStamp) - TotalWu (from MIN DateStamp)) / TO_DAYS(currentDateStamp - MIN DateStamp) Can anyone shed some light on my situation? Thanks in advance! -Original Message- From: Alain Fontaine [mailto:[EMAIL PROTECTED]] Sent: Friday, July 05, 2002 11:01 AM To: Solsberry, Glendon; [EMAIL PROTECTED] Subject: RE: MIN/MAX Date Field How about: select some_data from the_table order by the_date ASC limit 1 ? -Message d'origine- De : Solsberry, Glendon [mailto:[EMAIL PROTECTED]] Envoye : vendredi 5 juillet 2002 16:52 A : [EMAIL PROTECTED] Objet : MIN/MAX Date Field Is there a way to select some data from a table where the date field is the MIN() of that date column? Aka, if I have 100 rows of data, and the MIN() date is 1/1/2000, then I want to select the data that is from 1/1/2000. Can this be done without doing a multi-table join, or is that the only way to handle this??? Mysql, table - Glendon Solsberry Internet Programmer Tricon Global Restaurants tel. (502) 874-6736 fax (502) 874-8818 This communication is confidential and may be legally privileged. If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system. Failure to follow this process may be unlawful. Thank you for your cooperation. - 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 This communication is confidential and may be legally privileged. If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system. Failure to follow this process may be unlawful. Thank you for your cooperation. - 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: MIN/MAX Date Field
Solsberry, Glendon wrote: > Is there a way to select some data from a table where the date field is > the MIN() of that date column? Aka, if I have 100 rows of data, and the > MIN() date is 1/1/2000, then I want to select the data that is from > 1/1/2000. Can this be done without doing a multi-table join, or is that > the only way to handle this??? > This sounds similar to what's described here: http://www.mysql.com/doc/e/x/example-Maximum-row.html Hope this helps, Richard -- / \ Richard M Davis / Digital Archives \ University of London Computer Centre / Tel: +44 (0) 20 7692 1350 \ mailto: [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: MIN/MAX Date Field
How about: select some_data from the_table order by the_date ASC limit 1 ? -Message d'origine- De : Solsberry, Glendon [mailto:[EMAIL PROTECTED]] Envoye : vendredi 5 juillet 2002 16:52 A : [EMAIL PROTECTED] Objet : MIN/MAX Date Field Is there a way to select some data from a table where the date field is the MIN() of that date column? Aka, if I have 100 rows of data, and the MIN() date is 1/1/2000, then I want to select the data that is from 1/1/2000. Can this be done without doing a multi-table join, or is that the only way to handle this??? Mysql, table - Glendon Solsberry Internet Programmer Tricon Global Restaurants tel. (502) 874-6736 fax (502) 874-8818 This communication is confidential and may be legally privileged. If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system. Failure to follow this process may be unlawful. Thank you for your cooperation. - 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
MIN/MAX Date Field
Is there a way to select some data from a table where the date field is the MIN() of that date column? Aka, if I have 100 rows of data, and the MIN() date is 1/1/2000, then I want to select the data that is from 1/1/2000. Can this be done without doing a multi-table join, or is that the only way to handle this??? Mysql, table - Glendon Solsberry Internet Programmer Tricon Global Restaurants tel. (502) 874-6736 fax (502) 874-8818 This communication is confidential and may be legally privileged. If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system. Failure to follow this process may be unlawful. Thank you for your cooperation. - 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: Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?
Sorry for the long reply: At 02:57 PM 6/10/2002 -0500, you wrote: >[snip] >SELECT > head.po >FROM > head > LEFT JOIN line ON (head.sn=line.snHead) >WHERE > head.po > 1 > AND line.dateETA<='2002-06-10' >LIMIT 50 > >As is, this query is very fast (0.01 seconds when there are 25,000 records >in 'head', and 50,000 records in 'line'). However, when I change the query >to search on line.dateETA ">=" or even "=" [somedate], the query takes a >long time to return (2-3 seconds). > >The line.dateETA field is indexed. And, for both "<=" and ">=" queries, >EXPLAIN returns the same information. >[/snip] > >Since head.po is the return item here, indexing it will speed up the query. >HTH! >Jay >sql, mysql, query head.po is already indexed (as are head.sn and line.snHead), but I'm not so sure that indexing the "other" fields is the issue since the query is very fast in the "<=" case. Thanks, Matt - 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: Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?
[snip] SELECT head.po FROM head LEFT JOIN line ON (head.sn=line.snHead) WHERE head.po > 1 AND line.dateETA<='2002-06-10' LIMIT 50 As is, this query is very fast (0.01 seconds when there are 25,000 records in 'head', and 50,000 records in 'line'). However, when I change the query to search on line.dateETA ">=" or even "=" [somedate], the query takes a long time to return (2-3 seconds). The line.dateETA field is indexed. And, for both "<=" and ">=" queries, EXPLAIN returns the same information. [/snip] Since head.po is the return item here, indexing it will speed up the query. HTH! Jay sql, mysql, query "Say sql, my sql at the beginning of that well known prose...and see the reaction." - 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
Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?
Hi List, I sure do appreciate this list. I'm stumped on the following query: SELECT head.po FROM head LEFT JOIN line ON (head.sn=line.snHead) WHERE head.po > 1 AND line.dateETA<='2002-06-10' LIMIT 50 As is, this query is very fast (0.01 seconds when there are 25,000 records in 'head', and 50,000 records in 'line'). However, when I change the query to search on line.dateETA ">=" or even "=" [somedate], the query takes a long time to return (2-3 seconds). The line.dateETA field is indexed. And, for both "<=" and ">=" queries, EXPLAIN returns the same information. The only thing I can think of is that NULL values are allowed in line.dateETA, and about half the values are NULL (22,000 lines). However, I tested changing the line.dateETA field to NOT NULL, and I didn't get any better performance. Is there a known speed issue in searching on date fields with "<=" versus ">="? Thanks, Matt - 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: Date Field + Time Field = Datetime Field?
Alex, Further to Anvar's suggestions: - in addition to time_to_sec() recommend you look at sec_to_time() - which saves the repeated division to produce 'elapsed time' - alternatively take a look at unix_timestamp() and from_unixtime() which would allow the recording of all times to the second/as seconds, and convert to time/date presentation formats as required - regardless, unless you want large values expressed as hours (even beyond 24 hours) all elapsed time calculations must be divided by 86400 so that the integral part can be passed into a 'date' function, and the remainder into a 'time' function Times/dates can be stored ready for calculation or ready for display - it is always easier to convert a calculated field for display, than to perform arithmetic on a formatted-for-display field! Regards, =dn - Original Message - From: "Anvar Hussain K.M." <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: 11 January 2002 04:05 Subject: Re: Date Field + Time Field = Datetime Field? > Hi Alex, > > I don't think your problem will solved by making the time columns to full > datetime columns as there is no > functions to subtract two datetime values directly. > > But you can can keep the time columns and go on like this: > > convert the time into seconds using time_to_sec function. > subtract fromtime from totime. this will yield the difference in seconds > now you can take the elapsed time in hours,minutes or seconds. > > The sql might be something like: > > Select (time_to_sec(totime) - time_to_sec(fromtime)) / 60 / 60 as hours > from table. > > Section 7.4.11 of the manual explains date and time functions. > > Anvar > > > > At 06:00 PM 10/01/2002 +, you wrote: > >I've been working with a timesheet database, where all the employees of my > >small business enter in the hours they work on projects. I've been > >storing, for each record, a date of work, a start time, and a finish time. > >When I attempted to write a Perl script to display invoices, though, I ran > >into the issue that subtracting one time from another yields inconsistent > >results. Thus, I'm going to convert to datetime fields, which would store > >just the start and finish times as datetime. > >I'd like to automate the switchover, so I don't have to go through and > >maually update. I thought at first that "UPDATE time_worked SET dtstart = > >concat(datework, start);" would work, but that gave me a syntax error. I > >can't seem to find anything in the manual that would help, either. > >Is there any way to do this without going through and manually updating? > >I'd really appreciate any hints you could give. FYI, I'm running MySQL > >3.23.37. > >Thanks, > >Alex Kirk > > > >- > >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: Date Field + Time Field = Datetime Field?
Hi Alex, I don't think your problem will solved by making the time columns to full datetime columns as there is no functions to subtract two datetime values directly. But you can can keep the time columns and go on like this: convert the time into seconds using time_to_sec function. subtract fromtime from totime. this will yield the difference in seconds now you can take the elapsed time in hours,minutes or seconds. The sql might be something like: Select (time_to_sec(totime) - time_to_sec(fromtime)) / 60 / 60 as hours from table. Section 7.4.11 of the manual explains date and time functions. Anvar At 06:00 PM 10/01/2002 +, you wrote: >I've been working with a timesheet database, where all the employees of my >small business enter in the hours they work on projects. I've been >storing, for each record, a date of work, a start time, and a finish time. >When I attempted to write a Perl script to display invoices, though, I ran >into the issue that subtracting one time from another yields inconsistent >results. Thus, I'm going to convert to datetime fields, which would store >just the start and finish times as datetime. >I'd like to automate the switchover, so I don't have to go through and >maually update. I thought at first that "UPDATE time_worked SET dtstart = >concat(datework, start);" would work, but that gave me a syntax error. I >can't seem to find anything in the manual that would help, either. >Is there any way to do this without going through and manually updating? >I'd really appreciate any hints you could give. FYI, I'm running MySQL >3.23.37. >Thanks, >Alex Kirk > >- >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
Date Field + Time Field = Datetime Field?
I've been working with a timesheet database, where all the employees of my small business enter in the hours they work on projects. I've been storing, for each record, a date of work, a start time, and a finish time. When I attempted to write a Perl script to display invoices, though, I ran into the issue that subtracting one time from another yields inconsistent results. Thus, I'm going to convert to datetime fields, which would store just the start and finish times as datetime. I'd like to automate the switchover, so I don't have to go through and maually update. I thought at first that "UPDATE time_worked SET dtstart = concat(datework, start);" would work, but that gave me a syntax error. I can't seem to find anything in the manual that would help, either. Is there any way to do this without going through and manually updating? I'd really appreciate any hints you could give. FYI, I'm running MySQL 3.23.37. Thanks, Alex Kirk - 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: 0000-00-00 vs NULL in Date Field
>Here's what I did: > >mysql> create database testnull; >Query OK, 1 row affected (0.05 sec) > >mysql> use testnull; >Database changed > >mysql> create table info (birth date); >Query OK, 0 rows affected (0.03 sec) > >mysql> show tables; >++ >| Tables_in_testnull | >++ >| info | >++ >1 row in set (0.00 sec) > >mysql> describe info; >+---+--+--+-+-+---+ >| Field | Type | Null | Key | Default | Extra | >+---+--+--+-+-+---+ >| birth | date | YES | | NULL| | >+---+--+--+-+-+---+ >1 row in set (0.01 sec) > >mysql> describe info; >+---+--+--+-+-+---+ >| Field| Type | Null | Key | Default | Extra | >+---+--+--+-+-+---+ >| birth | date | YES | | NULL || >| death| date | YES | | NULL| | >+---+--+--+-+-+---+ >2 rows in set (0.00 sec) How come two identical commands show different results here? > >mysql> load data local infile "date.txt" into table info; >Query OK, 4 rows affected (0.01 sec) >Records: 4 Deleted: 0 Skipped: 0 Warnings: 1 > >mysql> select * from info; >+++ >| birth | death | >+++ >| 1999-01-01 | -00-00 | >| 1990-05-21 | -00-00 | >| 1989-12-20 | -00-00 | >| -00-00 | NULL | >+++ >4 rows in set (0.00 sec) > >date.txt is as follows: > >1999-01-01 /N (date followed by tab followed by /N) >1990-05-21 NULL(date followed by tab followed by NULL) >1989-12-20 (date followed by tab with nothing beyond) >(single tab - no data) > > >Any idea of why this works as it does? Because you used /N rather than \N ... -- 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: 0000-00-00 vs NULL in Date Field
Here's what I did: mysql> create database testnull; Query OK, 1 row affected (0.05 sec) mysql> use testnull; Database changed mysql> create table info (birth date); Query OK, 0 rows affected (0.03 sec) mysql> show tables; ++ | Tables_in_testnull | ++ | info | ++ 1 row in set (0.00 sec) mysql> describe info; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | birth | date | YES | | NULL| | +---+--+--+-+-+---+ 1 row in set (0.01 sec) mysql> describe info; +---+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | birth | date | YES | | NULL || | death| date | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql> load data local infile "date.txt" into table info; Query OK, 4 rows affected (0.01 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 1 mysql> select * from info; +++ | birth | death | +++ | 1999-01-01 | -00-00 | | 1990-05-21 | -00-00 | | 1989-12-20 | -00-00 | | -00-00 | NULL | +++ 4 rows in set (0.00 sec) date.txt is as follows: 1999-01-01 /N (date followed by tab followed by /N) 1990-05-21 NULL(date followed by tab followed by NULL) 1989-12-20 (date followed by tab with nothing beyond) (single tab - no data) Any idea of why this works as it does? - 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: 0000-00-00 vs NULL in Date Field
> If I read a file into a database with a \N in an empty date field > the field > is populated with -00-00. If I insert a row into the same > database with > NULL in the date field it is populated with NULL. > > Programmatically is there a difference between the two? > The field is proably defined with a default of -00-00, which MySQL then uses when you input no data. That doesn't stop you from entering NULL specifically, unless the field is declared NOT NULL. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: 0000-00-00 vs NULL in Date Field
At 9:06 AM -0400 10/2/01, Frank J. Schmuck wrote: >If I read a file into a database with a \N in an empty date field the field >is populated with -00-00. If I insert a row into the same database with >NULL in the date field it is populated with NULL. > >Programmatically is there a difference between the two? If you're using LOAD DATA to read the file with \N, you should get a NULL in the field. Here's a test data file: \N 1999-12-31 Here's my set of test statements: mysql> create table t (d date); Query OK, 0 rows affected (0.00 sec) mysql> load data local infile 'junk' into table t; Query OK, 2 rows affected (0.04 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t; ++ | d | ++ | NULL | | 1999-12-31 | ++ 2 rows in set (0.00 sec) If the DATE column had been declared NOT NULL, then the \N turns into -00-00 instead. But it sounds from your description that the column does allow NULL. > >Thanks >Frank -- 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
0000-00-00 vs NULL in Date Field
If I read a file into a database with a \N in an empty date field the field is populated with -00-00. If I insert a row into the same database with NULL in the date field it is populated with NULL. Programmatically is there a difference between the two? Thanks Frank - 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: Formatting the DATE field on a web page
Hi As the Perl hackers say, "tmtowtdt" (there's more than one way to do this) - you are free to do format your dates wherever you like. If you're that way inclined you could even do the formatting on the client side in JavaScript ;-) IIRC correctly PHP offers a lot of inbuilt date formatting wotsits. In SQL (at least MySQL, database developers also believe tmtowtdt ;-) you can use the DATE_FORMAT function, see: http://www.mysql.com/doc/D/a/Date_and_time_functions.html HTH Ian Barwick [EMAIL PROTECTED] On Monday 20 August 2001 18:02, pc wrote: > Hi people > > I am using mySQL and PHP for my web page database management thingy. I've > managed to get my date field from my Table into my page ($myDate). I was > wondering, how would I format the date from -MM-DD format to something > a bit nicer like DD/MM/YY ? > > Is this meant to be a SQL side thing or a server side language formatting > thingy? -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de "To query tables in a MySQL database is more fun than eating spam" - 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: Formatting the DATE field on a web page
Hi Steve, have a look at the DATE_FORMAT(date,format) function. You can retrieve the date in exactly the format you want doing it on the SQL side. Thomas On Mon, 20 Aug 2001, pc wrote: > Hi people > > I am using mySQL and PHP for my web page database management thingy. I've > managed to get my date field from my Table into my page ($myDate). I was > wondering, how would I format the date from -MM-DD format to something a > bit nicer like DD/MM/YY ? > > Is this meant to be a SQL side thing or a server side language formatting > thingy? > > Thanxs > > Steve Griff. > > > - > 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
Formatting the DATE field on a web page
Hi people I am using mySQL and PHP for my web page database management thingy. I've managed to get my date field from my Table into my page ($myDate). I was wondering, how would I format the date from -MM-DD format to something a bit nicer like DD/MM/YY ? Is this meant to be a SQL side thing or a server side language formatting thingy? Thanxs Steve Griff. - 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: Date Field Help
On 23-Jul-2001 Mike Mike wrote: > Hi Folks, > I'm trying to create a counter for my website. I'm > trying to pull the last 7 days worth of data out of > mysql. My sql statement is as follows. > > SELECT TheDate, Count(TheDate) AS CountOfdate > FROM Counter > WHERE (((TheDate)=Now()-7)) > GROUP BY TheDate; > When I do this Sql Commmand it returns 0 records. > Can someone help me with this statement. > Thank you -- Mike > run: mysql> select now() -7; a couple of times & you'll see why. use ' ... TheDate >= DATE_SUB(current_date, INTERVAL 7 DAY); And the 'WHERE (((TheDate)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: Date Field Help
Of course, 0 records are ok. Here is an example of mine : mysql> select now(), now()-7; +-++ | now() | now()-7| +-++ | 2001-07-23 21:45:53 | 20010723214546 | +-++ 1 row in set (0.05 sec) As you can see I subtracted 7 seconds not seven days as you wanted it to happen. The resulting relation will never fit any data. David At 11:02 23.07.01 -0700, Mike Mike wrote: >Hi Folks, >I'm trying to create a counter for my website. I'm >trying to pull the last 7 days worth of data out of >mysql. My sql statement is as follows. > >SELECT TheDate, Count(TheDate) AS CountOfdate >FROM Counter >WHERE (((TheDate)=Now()-7)) >GROUP BY TheDate; >When I do this Sql Commmand it returns 0 records. >Can someone help me with this statement. >Thank you -- Mike > >This is my data. >+---+++ >| Host | ID | TheDate| >+---+++ >| 192.168.0.140 | 1 | 2001-07-23 | >| 192.168.0.140 | 2 | 2001-07-23 | >| 192.168.0.140 | 3 | 2001-07-20 | >| 192.168.0.140 | 4 | 2001-07-21 | >+---+++ >4 rows in set (0.00 sec) > >__ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ > >- >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
Date Field Help
Hi Folks, I'm trying to create a counter for my website. I'm trying to pull the last 7 days worth of data out of mysql. My sql statement is as follows. SELECT TheDate, Count(TheDate) AS CountOfdate FROM Counter WHERE (((TheDate)=Now()-7)) GROUP BY TheDate; When I do this Sql Commmand it returns 0 records. Can someone help me with this statement. Thank you -- Mike This is my data. +---+++ | Host | ID | TheDate| +---+++ | 192.168.0.140 | 1 | 2001-07-23 | | 192.168.0.140 | 2 | 2001-07-23 | | 192.168.0.140 | 3 | 2001-07-20 | | 192.168.0.140 | 4 | 2001-07-21 | +---+++ 4 rows in set (0.00 sec) __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ - 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: DATE field with current date as default ?
Use the "timestamp" datatype instead of date. At 05:19 PM 1/13/2001 -0500, you wrote: >Hello, > >Is there a way to have a field of type DATE get the current date as its >default? > >Something like ... > >mydatefield DATE DEFAULT curdate() > >...which does not work. But that's the idea. - Scott Baker - Webster Internet - Network Technician 503.266.8253 - [EMAIL PROTECTED] "Always bear in mind that your own resolution to success is more important than any other one thing." - Abraham Lincoln - 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: DATE field with current date as default ?
>Hello, > >Is there a way to have a field of type DATE get the current date as its >default? No. Default values in MySQL must be constants. > >Something like ... > >mydatefield DATE DEFAULT curdate() > >...which does not work. But that's the idea. -- 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
DATE field with current date as default ?
Hello, Is there a way to have a field of type DATE get the current date as its default? Something like ... mydatefield DATE DEFAULT curdate() ...which does not work. But that's the idea. Best, Nick Didkovsky - 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
