Re: Date comparison help
Hi Michael,
FYI: I'm using 5.6.13 and your query returns 0 for the third column with my
instance.
Cheers,
Sam
On Wed, Oct 23, 2013 at 2:35 AM, Michael Stroh st...@astroh.org wrote:
I recently upgraded a local MySQL installation to 5.5.32 and am trying to
figure out why the following query won't work as expected anymore. I'm just
trying to compare a set of dates to NOW() but since the upgrade, these
don't seem to work as expected.
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
NOW(),
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2
DAY)NOW()
For instance, when I run it on my system, I get 1 for the third column
even though comparing the two by eye it should be false.
Cheers,
Michael
Re: Date comparison help
Thanks Sam.
It turns out that if I put the DATE_ADD.. within DATE(), it works as expected.
That is sufficient for my goals, but it would be nice to understand this issue
in case there may be other cases that I need to watch out for.
Cheers,
Michael
On Oct 22, 2013, at 6:18 PM, kitlenv kitl...@gmail.com wrote:
Hi Michael,
FYI: I'm using 5.6.13 and your query returns 0 for the third column with my
instance.
Cheers,
Sam
On Wed, Oct 23, 2013 at 2:35 AM, Michael Stroh st...@astroh.org wrote:
I recently upgraded a local MySQL installation to 5.5.32 and am trying to
figure out why the following query won't work as expected anymore. I'm just
trying to compare a set of dates to NOW() but since the upgrade, these don't
seem to work as expected.
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
NOW(),
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2
DAY)NOW()
For instance, when I run it on my system, I get 1 for the third column even
though comparing the two by eye it should be false.
Cheers,
Michael
Re: Date comparison help
2013/10/22 12:20 -0400,
I recently upgraded a local MySQL installation to 5.5.32 and am trying to
figure out why the following query won't work as expected anymore. I'm just
trying to compare a set of dates to NOW() but since the upgrade, these don't
seem to work as expected.
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
NOW(),
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)NOW()
For instance, when I run it on my system, I get 1 for the third column even
though comparing the two by eye it should be false.
Well, show us all three columns
And with 5.5.8 I get the same third column as you. Has it worked?
And I found that changed to
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY) AS
A,
NOW(),
CAST(DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)
AS DATETIME)NOW() AS B
it works as hoped for--and it seems a bug to me, but probably an old one. It
seems to me that the outcome of DATE_ADD is DATE, not DATETIME, and the
comparison is numeric, with the six trailing 0s dropped. Quote about
STR_TO_DATE:
It takes a string str and a format string format.
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_str-to-dateSTR_TO_DATE()
returns a
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetimeDATETIME
value if the format string contains both date and time parts, or a
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetimeDATE
or
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#timeTIME
value if the string contains only date or time parts.
How really does it decide which type to return? It is wrong if the decision is
based whether all the hour, minute, and second are 0 or not.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: date-IFNULL-sum bug?
2012/10/08 14:52 -0700, Rick James Do not use + for DATE arithmetic! Use, for example + INTERVAL 1 YEAR No, those operations are well defined. Amongst the timestamp-functions there is constant reference to numeric context, and character context--and well there is, because there are no time-constants, only numerals and character strings taken for timestamps. It is also the only means of doing some things. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: date-IFNULL-sum bug?
Do not use + for DATE arithmetic!
Use, for example
+ INTERVAL 1 YEAR
-Original Message-
From: h...@tbbs.net [mailto:h...@tbbs.net]
Sent: Thursday, October 04, 2012 9:35 PM
To: mysql@lists.mysql.com
Subject: date-IFNULL-sum bug?
Can anyone explain this to me?
The first one seems quite wrong; the rest make perfect sense.
mysql select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
+--+
|11900 |
+--+
1 row in set (0.00 sec)
mysql select ifnull(date('1900/5/3'), date('1900/01/01'));
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) |
+--+
| 1900-05-03 |
+--+
1 row in set (0.00 sec)
mysql select date('1900/5/3') + 1;
+--+
| date('1900/5/3') + 1 |
+--+
| 19010503 |
+--+
1 row in set (0.00 sec)
mysql select date(date('1900/5/3') + 1);
++
| date(date('1900/5/3') + 1) |
++
| 1901-05-03 |
++
1 row in set (0.00 sec)
(5.5.8 under muSoft Windows)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: date comparison query
2012/03/16 13:30 -0400, Simon Wilkinson My query for this is as follows: select * from table where table.date1 - table.date2 between 28425600 and 29030400; I would not count on that subtraction s yielding a meaningful number: the types are not Unix timestamps. I would use TIMESTAMPDIFF, with a good unit. From the downloaded help file: Date arithmetic also can be performed using INTERVAL together with the file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_plus+ or file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_minus- operator: date + INTERVAL expr unit date - INTERVAL expr unit but subtracting timestamp from timestamp hoping for a while (interval) is not mentioned. If you want the subtraction to work, make them big integers and use UNIX_TIMESTAMP and FROM_UNIXTIME. See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: date comparison query
Simon, It's likely that when you specify the times as integer literals they are being converted to something you don't expect. You can use EXPLAIN EXTENDED followed by SHOW WARNINGS to see what's happening; it might be interesting and educational. I would specify the times you want as datetime literals, in 'quotes', to make sure the database interprets your values correctly. On Fri, Mar 16, 2012 at 1:30 PM, Simon Wilkinson simon.wilkin...@gmail.com wrote: Hi, I have a table that holds two datetime columns. I am trying to find values from this table that fall into specific time ranges, but am getting some strange results. For example, if I try to find rows where the difference between the two column is between 47 and 48 weeks, I get back a result where the actual difference is less than 1 month. My query for this is as follows: select * from table where table.date1 - table.date2 between 28425600 and 29030400; The result returns a row where date1 is 2010-10-31 18:24:49, and date2 is 2010-10-02 20:29:54. I seem to get proper results for some values (I am trying to find results that fall into different weekly ranges), but then some are just way off. Does anybody have any ideas for why this is happening? Thanks, Simon -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
What's your problem/reason with how it is? Andy On Sun, Jan 8, 2012 at 8:21 PM, Donovan Brooke li...@euca.us wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/**refman/5.1/en/date-and-time-**literals.htmlhttp://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? Thanks, Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
On 1/8/2012 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format date strings to the format MySQL uses. PB - Thanks, Donovan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
Peter Brawley wrote: On 1/8/2012 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format date strings to the format MySQL uses. PB - Thanks! Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
What's your problem/reason with how it is? I assume Andy means: leave it stored as a timestamp type or datetime type, and when you need to display it otherwise.. then covert with date() -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
On Jan 8, 2012, at 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? In DATE, DATETIME, and TIMESTAMP columns, you must specify the date part on year-month-day order. If you want to store a value in a different format, you must use some other data type such as VARCHAR. But then it won't be interpreted as a date. If you want to display a date from a DATE, etc. column in some other format, pass the value to DATE_FORMAT(). http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format If you want to reformat a date value in some other format to put it in year-month-day format so that you can store it in a DATE, etc. column, STR_TO_DATE() might be helpful. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE() can be useful, for example, when loading non year-month-day data into a table with LOAD DATA. You can use STR_TO_DATE() to reformat the values on the fly. LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t (name,@date,value) SET date = STR_TO_DATE(@date,'%m/%d/%y'); -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
leave it stored as a timestamp type or datetime type, and when you need to display it otherwise.. then covert with date() oops, Paul's post reminded me I was suggesting a PHP function here ^^^ ... and this is the MySQL list. -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date Translation Issues
mysql desc maintenance; ++--+--+-++---+ | Field | Type | Null | Key | Default| Extra | ++--+--+-++---+ | indate | date | YES | | NULL | | | contract | char(25) | NO | MUL | NULL | | | cstatus| char(20) | YES | | NULL | | | customer | char(35) | YES | | NUCO INC | | | party | int(11) | YES | | NULL | | | city | varchar(125) | YES | | NULL | | | state | varchar(50) | YES | | NULL | | | country| varchar(50) | YES | | NULL | | | serial | char(25) | YES | | NULL | | | model | char(25) | YES | | NULL | | | mdesc | char(50) | YES | | NULL | | | service| char(50) | YES | | NULL | | | qty| int(11) | YES | | NULL | | | amc| int(11) | YES | | NULL | | | sdate | date | YES | | NULL | | | edate | date | YES | | NULL | | | cdate | date | YES | | NULL | | | days | int(11) | YES | | NULL | | | due| int(11) | YES | | NULL | | | comments | char(50) | YES | | NULL | | | lstatus| char(20) | YES | | Installed | | | b2customer | char(50) | YES | | NULL | | | descr | char(50) | YES | | NULL | | | amlp | int(11) | YES | | NULL | | | rcsdate| date | YES | | NULL | | | rcedate| date | YES | | NULL | | | contractid | int(11) | YES | | NULL | | | refresh| char(8) | YES | | NULL | | ++--+--+-++---+ 28 rows in set (0.02 sec) I cleaned up the insert statement to push the dates as strings: $dbh-do(insert into maintenance (indate, b2customer, contract, cstatus, customer, party, city, state, serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate, days, contractid) values (CURDATE(),\'$b2customer\', \'$contract\', \'$status\', \'$incustomer\', $party, \'$city\', \'$state\', \'$serial\', \'$model\', \'$mdesc\', \'$descr\', $qty, $amlp, \'$sdate\', \'$edate\', \'$rcsdate\', \'$rcedate\', $days, $contractid)); Here are two rows from the spreadsheet: NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX APM00060300673 CX300-FD CX300 W 2GB FIELD INSTALL PREMIUM HARDWARE SUPPORT 1 $1,400.04 70.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,408.48 612 742892 NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX 0 CX-2G10-146 146GB 10K 2GB FC PREMIUM HARDWARE SUPPORT 15 $1,926.00 70.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,937.61 612 742892 Here is the print statement from the perl script: +++ Bill to Customer: NUCO INC Contract: 61420644H Status: ACTIVE Install Customer: NUCO RICHARDSON (TACSUN) Party ID: 8200137 City: RICHARDSON State: TX Serial Number: APM00060300673 Model: CX300-FD Model Description: CX300 W 2GB FIELD INSTALL Description: PREMIUM HARDWARE SUPPORT Quantity: 1 Annual List Price: 1400.04 Discount: 0.7 Start Date: 2006-1-28 End Date: 2009-1-27 Coverage Start Date: 2009-1-28 Coverage End Date: 2010-10-1 Pro-Rated Maintenance: 1408.47859726027 Days: 612 Contract ID: 742892 +++ +++ Bill to Customer: NUCO INC Contract: 61420644H Status: ACTIVE Install Customer: NUCO RICHARDSON (TACSUN) Party ID: 8200137 City: RICHARDSON State: TX Serial Number: 0 Model: CX-2G10-146 Model Description: 146GB 10K 2GB FC Description: PREMIUM HARDWARE SUPPORT Quantity: 15 Maintenance List Price: 1926 Discount: 0.7 Start Date: 2006-1-28 End Date: 2009-1-27 Coverage Start Date: 2009-1-28 Coverage End Date: 2010-10-1 Pro-Rated Maintenance: 1937.60876712329 Days: 612 Contract ID: 742892 +++ Here is a select * for these two records: mysql select * from maintenance where contract='61420644H'; ++---+-+---+ -++---+-++-+ ---+-+--+--+++-- -+--+--+--+---+---+-
Re: Date Translation Issues
Hagan, Close but not quite what I asked for. The schema for your table is what you get from SHOW CREATE TABLE `mytable`. It will show all the indexes and, most imporatantly, make it trivial for someone trying to assist you in your investigation to re-create your problem. It is good to see the quotes in your perl script, but what I was hoping for was the query itself after perl has rendered it, again, so it can be run against a database without me having to write a perl-wrapper to do so. On Tue, Jul 14, 2009 at 4:02 PM, Hagen Finleyfinha...@comcast.net wrote: mysql desc maintenance; ++--+--+-++---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-++---+ | indate | date | YES | | NULL | | | contract | char(25) | NO | MUL | NULL | | | cstatus | char(20) | YES | | NULL | | | customer | char(35) | YES | | NUCO INC | | | party | int(11) | YES | | NULL | | | city | varchar(125) | YES | | NULL | | | state | varchar(50) | YES | | NULL | | | country | varchar(50) | YES | | NULL | | | serial | char(25) | YES | | NULL | | | model | char(25) | YES | | NULL | | | mdesc | char(50) | YES | | NULL | | | service | char(50) | YES | | NULL | | | qty | int(11) | YES | | NULL | | | amc | int(11) | YES | | NULL | | | sdate | date | YES | | NULL | | | edate | date | YES | | NULL | | | cdate | date | YES | | NULL | | | days | int(11) | YES | | NULL | | | due | int(11) | YES | | NULL | | | comments | char(50) | YES | | NULL | | | lstatus | char(20) | YES | | Installed | | | b2customer | char(50) | YES | | NULL | | | descr | char(50) | YES | | NULL | | | amlp | int(11) | YES | | NULL | | | rcsdate | date | YES | | NULL | | | rcedate | date | YES | | NULL | | | contractid | int(11) | YES | | NULL | | | refresh | char(8) | YES | | NULL | | ++--+--+-++---+ 28 rows in set (0.02 sec) I cleaned up the insert statement to push the dates as strings: $dbh-do(insert into maintenance (indate, b2customer, contract, cstatus, customer, party, city, state, serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate, days, contractid) values (CURDATE(),\'$b2customer\', \'$contract\', \'$status\', \'$incustomer\', $party, \'$city\', \'$state\', \'$serial\', \'$model\', \'$mdesc\', \'$descr\', $qty, $amlp, \'$sdate\', \'$edate\', \'$rcsdate\', \'$rcedate\', $days, $contractid)); Here are two rows from the spreadsheet: NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX APM00060300673 CX300-FD CX300 W 2GB FIELD INSTALL PREMIUM HARDWARE SUPPORT 1 $1,400.04 70.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,408.48 612 742892 NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX 0 CX-2G10-146 146GB 10K 2GB FC PREMIUM HARDWARE SUPPORT 15 $1,926.00 70.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,937.61 612 742892 Here is the print statement from the perl script: +++ Bill to Customer: NUCO INC Contract: 61420644H Status: ACTIVE Install Customer: NUCO RICHARDSON (TACSUN) Party ID: 8200137 City: RICHARDSON State: TX Serial Number: APM00060300673 Model: CX300-FD Model Description: CX300 W 2GB FIELD INSTALL Description: PREMIUM HARDWARE SUPPORT Quantity: 1 Annual List Price: 1400.04 Discount: 0.7 Start Date: 2006-1-28 End Date: 2009-1-27 Coverage Start Date: 2009-1-28 Coverage End Date: 2010-10-1 Pro-Rated Maintenance: 1408.47859726027 Days: 612 Contract ID: 742892 +++ +++ Bill to Customer: NUCO INC Contract: 61420644H Status: ACTIVE Install Customer: NUCO RICHARDSON (TACSUN) Party ID: 8200137 City: RICHARDSON State: TX Serial Number: 0 Model: CX-2G10-146 Model Description: 146GB 10K 2GB FC Description: PREMIUM HARDWARE SUPPORT Quantity: 15 Maintenance List Price: 1926 Discount: 0.7 Start Date:
Re: Date Translation Issues
Micheal, Your query trouble shooting tip showed me my error - see below: Sorry here is the SHOW CREATE TABLE: mysql show create table `maintenance`; +-+- + | Table | Create Table | +-+- + | maintenance | CREATE TABLE `maintenance` ( `indate` date DEFAULT NULL, `contract` char(25) NOT NULL, `cstatus` char(20) DEFAULT NULL, `customer` char(35) DEFAULT 'CISCO SYSTEMS, INC', `party` int(11) DEFAULT NULL, `city` varchar(125) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `country` varchar(50) DEFAULT NULL, `serial` char(25) DEFAULT NULL, `model` char(25) DEFAULT NULL, `mdesc` char(50) DEFAULT NULL, `service` char(50) DEFAULT NULL, `qty` int(11) DEFAULT NULL, `amc` int(11) DEFAULT NULL, `sdate` date DEFAULT NULL, `edate` date DEFAULT NULL, `cdate` date DEFAULT NULL, `days` int(11) DEFAULT NULL, `due` int(11) DEFAULT NULL, `comments` char(50) DEFAULT NULL, `lstatus` char(20) DEFAULT 'Installed', `b2customer` char(50) DEFAULT NULL, `descr` char(50) DEFAULT NULL, `amlp` int(11) DEFAULT NULL, `rcsdate` date DEFAULT NULL, `rcedate` date DEFAULT NULL, `contractid` int(11) DEFAULT NULL, `refresh` char(8) DEFAULT NULL, KEY `contract` (`contract`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-+- + 1 row in set (0.00 sec) Here are the two select statements per the two records below: insert into maintenance (indate, b2customer, contract, cstatus, customer, party, city, state, serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate, days, contractid) values (CURDATE(),\'CISCO SYSTEMS INC\', \'61420644H\', \'ACTIVE', \'CISCO SYSTEMS RICHARDSON (TACSUN)\', 8200137, \'RICHARDSON\', \'TX\', \'APM00060300673\', \'CX300-FD\', \'CX300 W 2GB FIELD INSTALL\', \'PREMIUM HARDWARE SUPPORT\', 1, 1400.04, \'38744\', \'39839\', \'39840\', \'40451\', 612, 742892) Doesn't look quite right - does it? ;-). Well, great trouble shooting lesson to resolve a pretty stupid error - I was using the $sdate variable for my insert which was pre-Julian reformat. I needed $sdate1 for the insert. Here is the corrected query: insert into maintenance (indate, b2customer, contract, cstatus, customer, party, city, state, serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate, days, contractid) values (CURDATE(),\'CISCO SYSTEMS INC\', \'61420644H\', \'ACTIVE', \'CISCO SYSTEMS RICHARDSON (TACSUN)\', 8200137, \'RICHARDSON\', \'TX\', \'APM00060300673\', \'CX300-FD\', \'CX300 W 2GB FIELD INSTALL\', \'PREMIUM HARDWARE SUPPORT\', 1, 1400.04, \'2006-1-28\', \'2009-1-27\', \'2009-1-28\', \'2010-10-1\', 612, 742892) I appear to be getting the proper dates in my table now. Thanks for your help Michael! Hagen On 7/14/09 2:08 PM, Michael Dykman mdyk...@gmail.com wrote: Hagan, Close but not quite what I asked for. The schema for your table is what you get from SHOW CREATE TABLE `mytable`. It will show all the indexes and, most imporatantly, make it trivial for someone trying to assist you in your investigation to re-create your problem. It is good to see the quotes in your perl script, but what I was hoping for was the query itself after perl has rendered it, again, so it can be run against a database without me having to write a perl-wrapper to do so. On Tue, Jul 14, 2009 at 4:02 PM, Hagen Finleyfinha...@comcast.net wrote: mysql desc maintenance; ++--+--+-++---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-++---+ | indate | date | YES | | NULL | | | contract | char(25) | NO | MUL | NULL | | | cstatus | char(20) | YES | | NULL | | | customer | char(35) | YES | | NUCO INC | | | party | int(11) | YES | | NULL | | | city | varchar(125) | YES | | NULL | | | state | varchar(50) | YES | | NULL | | | country | varchar(50) | YES | | NULL | | | serial | char(25) | YES | | NULL | | | model | char(25) | YES | | NULL | | | mdesc | char(50) | YES | | NULL | | | service | char(50) | YES | | NULL | | | qty | int(11) | YES | | NULL | | | amc | int(11) |
Re: Date Time
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009',
'%a %b %e %H:%i:%s + %Y'));
mysql select * from t;
+-+
| d |
+-+
| 2009-05-21 03:15:28 |
+-+
1 row in set (0.01 sec)
On Thu, 2009-05-21 at 15:19 -0600, John Meyer wrote:
Is Thu May 21 03:15:28 + 2009 a valid date/time string?
--
Best Regards,
-Janek Bogucki, CMDEV 5.0.
StudyLink. Helping People Realise Their Potential.
http://studylink.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Date Time
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009',
'%a %b %e %H:%i:%s + %Y'));
Thanks. That'll work.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Date Time
mysql create table t(d datetime);
Query OK, 0 rows affected (0.08 sec)
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009', '%a
%b %e %H:%i:%s + %Y'));
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| d |
+-+
| 2009-05-21 03:15:28 |
+-+
most of us have contacts in europe who dont use EDT,CDT,MDT or PDT so
i *was hoping* to get confirmation on CONVERT_TZ(date,from_tz,to_tz) works?
Thanks,
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
Date: Fri, 22 May 2009 07:14:58 -0600
From: john.l.me...@gmail.com
To: janek.bogu...@studylink.com
CC: mysql@lists.mysql.com
Subject: Re: Date Time
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 +
2009', '%a %b %e %H:%i:%s + %Y'));
Thanks. That'll work.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
_
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage1_052009
Re: Date Time
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009',
'%a %b %e %H:%i:%s + %Y'));
BTW, how would you work that with offsets that were a different value
(say +0700).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Date Time
On Fri, May 22, 2009 at 12:42 PM, John Meyer john.l.me...@gmail.com wrote:
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 +
2009', '%a %b %e %H:%i:%s + %Y'));
BTW, how would you work that with offsets that were a different value (say
+0700).
I don't think you do. You would have to get your application to parse
out the date into something that MySQL can negotiate. The only reason
this works at all in your case is because you happen to have +
which we can safely ignore.
--
- michael dykman
- mdyk...@gmail.com
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Date Time
mysql select sysdate() from DUAL; +-+ | sysdate() | +-+ | 2009-05-21 17:37:13 | +-+ i would get the proprt format is i could CONVERT_TZ to work can you get CONVERT_TZ to work ? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Thu, 21 May 2009 15:19:16 -0600 From: john.l.me...@gmail.com To: mysql@lists.mysql.com Subject: Date Time Is Thu May 21 03:15:28 + 2009 a valid date/time string? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® has ever-growing storage! Don’t worry about storage limits. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage1_052009
Re: Date sorting problem with Date_Format?
Hello mysql list, As is very often the case, five minutes after I posted this, I found the problem or solution, not sure it was the problem as I am not convinced that mysql ought to get confused so easily. I changed DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS Date, DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS FmtDate, and used the FmtDate reference to display the query results and it worked fine. -- Best regards, mikeszmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Baron Baron, Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. Thanks, cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On Aug 14, 2007, at 8:38 AM, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over- samples-that-wrap/ Baron Baron, Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. Thanks, cj Hi Christian, I don't know if you are in control of the data, But would it be possible to add a column to the database something like Read and have the value either 1 or 0? then do something like: Select * from view ordered by date where Read=1;?That way you wouldn't have any gaps for fields and could then just do the math fairly easily I think... But I'm just starting out with MySQL so I may have made a huge mistake :) In fact... It's quite probable :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
Hi, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. I misunderstood your question. I think what you need is something like the following. Working from the inside out, 1) find the most recent date previous to the current date select max(date) from t1 as inner_t1 where inner_t1.date ? This query will not run efficiently; MySQL can't yet optimize it. A logical equivalent that will be fast, if date is indexed, will be: select date from t1 as inner_t1 where inner_t1.date ? order by date desc limit 1 That's the most recent date before any given date and will be the inmots query. Now we need to find the corresponding scale_reading: select scale_reading from t1 as mid_t1 where mid_t1.date = ( ... inmost query ... ) That's the middle query. Now you can place that in a subquery: select date, scale_reading - ( ... middle query ... ) from t1 as outer_t1 Finally, resolve the ? reference in the correlated subquery: select date, scale_reading - ( select scale_reading from t1 as mid_t1 where mid_t1.date = ( select date from t1 as inner_t1 where inner_t1.date outer_t1.date order by date desc limit 1 ) ) from t1 as outer_t1 I'm not attempting to run this, just reasoning about it, so I might be wrong or there might be a syntax error. The next issue is t1 is a view -- it might perform terribly. You might be better off doing it another way, either querying the tables directly, or using a user variable: set @most_recent_reading := null; select date, scale_reading - @most_recent_reading, @most_recent_reading := scale_reading from t1; Again untested, but hopefully you get the idea. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. I misunderstood your question. I think what you need is something like the following. Working from the inside out, 1) find the most recent date previous to the current date select max(date) from t1 as inner_t1 where inner_t1.date ? This query will not run efficiently; MySQL can't yet optimize it. A logical equivalent that will be fast, if date is indexed, will be: select date from t1 as inner_t1 where inner_t1.date ? order by date desc limit 1 That's the most recent date before any given date and will be the inmots query. Now we need to find the corresponding scale_reading: select scale_reading from t1 as mid_t1 where mid_t1.date = ( ... inmost query ... ) That's the middle query. Now you can place that in a subquery: select date, scale_reading - ( ... middle query ... ) from t1 as outer_t1 Finally, resolve the ? reference in the correlated subquery: select date, scale_reading - ( select scale_reading from t1 as mid_t1 where mid_t1.date = ( select date from t1 as inner_t1 where inner_t1.date outer_t1.date order by date desc limit 1 ) ) from t1 as outer_t1 I'm not attempting to run this, just reasoning about it, so I might be wrong or there might be a syntax error. The next issue is t1 is a view -- it might perform terribly. You might be better off doing it another way, either querying the tables directly, or using a user variable: set @most_recent_reading := null; select date, scale_reading - @most_recent_reading, @most_recent_reading := scale_reading from t1; Again untested, but hopefully you get the idea. Baron Barron, That is exactly what I was looking for. I can work out whatever bugs or syntax errors there may be I just couldn't get my head completely around the logic. Thank you for your time. Cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
Hi Christian, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? cj I have read through Baron's solution and I think I can help you do it just a bit quicker. It won't all be in a single statement, although it could be written that way. I am assuming that you are going to need to so this for a range of dates @start_date to @end_date. The variables aren't important except to help us limit how much work we need to do. step 1 - Start off by capturing a list of all of the dates and scale_readings between @start_date and @end_date. The last reading will have nothing to be subtracted from as the next date may not yet exist (if @end_date is today) so this table will serve as our source data for the next steps. This means that step 2 will have the smallest possible JOIN to perform. CREATE TEMPORARY TABLE tmp_source SELECT `date`, `scale_reading` FROM sourcetableorview -- this is wherever your data is coming from, now. WHERE `date` BETWEEN @start_date and @end_date; step2 - Build a pair table of dates. We need to index the tmp_source.`date` to speed this up. This step will become geometrically slower the more dates you want to process at once. The `scale_reading` is part of the index in order to speed up step 3. ALTER TABLE tmp_source ADD KEY(`date`,`scale_reading`); CREATE TEMPORARY TABLE tmp_datepairs SELECT t_s1.`date` as date1, MIN(t_s2.`date`) as date2 FROM tmp_source t_s1 INNER JOIN tmp_source t_s2 ON t_s1.`date` t_s2.`date` GROUP BY t_s1.`date`; step 3 - Use the tables we generated from steps 1 and 2 to build your final report. I indexed both columns on tmp_datepairs in order to speed this up. ALTER TABLE tmp_datepairs ADD KEY(date1), ADD KEY(date2); SELECT td.`date1` as startdate , td.`date2` as enddate , ts1.scale_reading as startwt , ts2.scale_reading as endwt , ts2.scale_reading - ts1.scale_reading as diff FROM tmp_datepairs td INNER JOIN tmp_source ts1 on td.date1 = ts1.`date` INNER JOIN tmp_source ts2 on ts.date2 = ts2.`date` step 4 - always (!!) clean up after yourself DROP TEMPORARY TABLE IF EXISTS tmp_source, tmp_datepairs; If you have any questions about the logic, please feel free to ask. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date function question
# of days since the first of the month from last month datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01'))) the # of days since the end of last month (e.g. from 5/31/07) datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL 1 DAY)) PB - Andrey Dmitriev wrote: Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date function question
There is also a LAST_DAY() function that returns the last day of the month: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Peter Brawley wrote: # of days since the first of the month from last month datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01'))) the # of days since the end of last month (e.g. from 5/31/07) datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL 1 DAY)) PB - Andrey Dmitriev wrote: Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Best here is to just use a DATE field, then use DATE_FORMAT when you want to pull up the customized date. It will get stored as a timestamp (integer), so you really won't notice that much of a storage difference. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. On 1/15/07 11:37 AM, Chris White [EMAIL PROTECTED] wrote: Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Best here is to just use a DATE field, then use DATE_FORMAT when you want to pull up the customized date. It will get stored as a timestamp (integer), so you really won't notice that much of a storage difference. - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Olaf Stein wrote: Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. Pass the date in format (-MM-00). -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Assign all dates to have a day of 01 Store in a date field, use DATE_FORMAT to just extract the MM and . As you don't have the real day information it doesn't matter what day is used, so long as it present in all months. Hope this helps Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
OK, thank you. How is the speed of this index compared with an indexed date column if I do: year_number='x' and month_number='y' and day_number='z'; They should have about the same cardinality, right? Thanks, Anders Chris wrote: Anders Lundgren wrote: One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- Anders Lundgren Viba IT Handelsbolag Web: http://www.vibait.se E-mail: [EMAIL PROTECTED] Cell: +46 (0)70-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on = first day of the month and the first day of the next month. That will use an index. - Original Message - From: Anders Lundgren [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Thomas Bolioli [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- 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: Date v. DateTime index performance
Yes, of course. Thank you! - Anders Brent Baisley wrote: Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on = first day of the month and the first day of the next month. That will use an index. - Original Message - From: Anders Lundgren [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Thomas Bolioli [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Anders Lundgren wrote: One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- 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: Date comparisons
I've found something that works (in MySQL 5, anyway), but I don't know whether it's accepted practice. If I want to find all records with a date in, say, March 2006, it works if I use datefield like '2006-03%' because it's a string. This seems kind of obvious and a lot tidier than doing datefield = '2006-03-01' and datefield = '2006-03-31', but are there pitfalls I should know about? Speed would be my first thought... I'd time them. I'd also prepend 'explain' as I'm guessing the first won't use an index and the second will (assuming you have indexes). -p -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problems
I put one select on each question. wizard007 [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I have just started with MYSQL and am building a racing site using PHP. I have Date, Course, Time, Horse, Odds, Result as my fields and the database is poulated with the data. I'm having problems with the formatting of data when it is output. Problem 1. I want to display the date in the format dd/mm/. I know you can use the DATE_FORMAT command but I can't seem to get it to work. Can anyone give me the exact script I need to write. *** select date_format(Date,%d/%m/%Y) as Date from table; *** Problem 2. I also want to display the results in the last 7 days in descending order by date. *** select date_format(Date,%d/%m/%Y) as Date from table where datediff(date_format(now(),\%Y-%m-%d %H:%i:%s\),date_format(Date,\%Y-%m-%d %H:%i:%s\))=7 order by Date desc; *** I can't seem to work out a way of doing that either. I'm a newb so please be gentle! :-) -- View this message in context: http://www.nabble.com/Date-Problems-tf1937078.html#a5307385 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problems
Hi, Just tried the date format script posted but it returns the following error: Parse error: parse error, unexpected '%' in /homepages/7/d123417448/htdocs/PayGo/results_14days.php on line 33 I used $query_Recordset1 = SELECT date_format(Date, %d/%m/%Y) as Date, Course, Time, Horse, Odds1, Odds2, `Result` FROM Results; -- View this message in context: http://www.nabble.com/Date-Problems-tf1937078.html#a5310495 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problems
On 13 Jul 2006 at 8:27, wizard007 wrote: I used $query_Recordset1 = SELECT date_format(Date, %d/%m/%Y) as Date, Course, Time, Horse, Odds1, Odds2, `Result` FROM Results; Hi, That's a PHP error because you have a double quotes: %d/%m/%Y within double quotes: $query_Recordset1 = SELECT .. .FROM Results; You need to escape the quotes in your statement. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date functions
From: Chris W Sent: 07 July 2006 09:23 It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. SELECT * FROM t WHERE TimeCol DATE_SUB(CURDATE(), INTERVAL 60*60*24*3 SECOND); http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date functions
Try this: SELECT * FROM t where TimeCol date_sub( now(), INTERVAL x SECOND ) Dan On 7/7/06, Chris W [EMAIL PROTECTED] wrote: It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. -- Chris W KE5GIX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date functions
The INTERVAL command is what you are looking for. It doesn't have to be SECOND (with no S), you could use day, hour ,week, etc. SELECT * FROM t WHERE TimeCol(now() - INTERVAL X SECOND) http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html - Original Message - From: Chris W [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, July 07, 2006 4:23 AM Subject: Date functions It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- 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: Date functions
Addison, Mark wrote: From: Chris W Sent: 07 July 2006 09:23 It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. SELECT * FROM t WHERE TimeCol DATE_SUB(CURDATE(), INTERVAL 60*60*24*3 SECOND); Maybe it was just too late at night but I read about the DATE_SUB function in the manual and got the impression that it ignored the time part of a date time field so I could not use it for finding records only a few hours old. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- 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: 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
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: snip 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
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]
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 |
| Com_alter_db | 0 |
| Com_alter_table| 45 |
| Com_analyze| 1 |
| Com_backup_table | 0 |
| Com_begin
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 do this? And why does it change when I run a repair
table?
(show status:
mysql show
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_function
Re: DATE problem
Hello. MySQL doesn't support this syntax. See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html If you want to automatically extract the year (month, day) part from the inserted value, you may want to use TRIGGERS, however, in my opinion, it is better to redesign your table structure. You can use VIEWS to make the design of your database more flexible. See: http://dev.mysql.com/doc/refman/5.0/en/views.html http://dev.mysql.com/doc/refman/5.0/en/triggers.html Mester József wrote: Hy all I would like to get date in my web page. There are three different fields. YEAR MONTH and DAY I thought in SQL possible create table like this /*from my book*/ create table dates ( input_year DATETIME YEAR TO YEAR, input_month DATETIME MONTH TO MONTH, input_day DATETIME DAY TO DAY); In this example I can store year,month and day values in different fields. Mysql doesn't accept this create statement. Joe - Yahoo! Messenger NEW - crystal clear PC to PC calling worldwide with voicemail -- 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: date index question
Hello. Add composite index (tValidFrom, tValidTo) and use constant or variable instead of now(). Force MySQL to use this composite index. Mattias Håkansson wrote: Hello People, I have some indexing problem on using the fieldtype 'date' as restriction in a query. I use MySQL Server version: 4.0.20 The table I have consists of roughly over 200.000 rows about 37 fields and it looks sort of like this: mysql desc the_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | theID | int(11) | | PRI | NULL| auto_increment | | contract | varchar(20) | | MUL | || ... some varchars ... | rate1 | double(10,2) | | | 0.00 || ... twenty other doubles ... | routing| varchar(100) | | | || | cNotes | text | | | || | tValidfrom | date | | MUL | -00-00 || | tValidto | date | | MUL | -00-00 || | iStatus| int(11) | | | 0 || | iEnteredby | int(11) | | | 0 || | tEntered | datetime | | | -00-00 00:00:00 || | iUpdatedby | int(11) | | | 0 || | tUpdated | datetime | | | -00-00 00:00:00 || ++--+--+-+-++ 37 rows in set (0.00 sec) I then run this to create the index I want. mysql CREATE INDEX IX_test ON the_table(tValidfrom,tValidto); Query OK, 204657 rows affected (1 min 6.08 sec) Records: 204657 Duplicates: 0 Warnings: 0 The created index shows this info: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 1 Column_name: tValidfrom Collation: A Cardinality: 75 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 2 Column_name: tValidto Collation: A Cardinality: 131 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 27 rows in set (0.00 sec) So now I want to take advantage of this index, but my query is still slow: mysql SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); | USBDA | | USIND | | USHSV | +-+ 402 rows in set (2.80 sec) Then I do an explain to try to find out if it is using my index. mysql explain SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now() \G; *** 1. row *** table: the_table type: ALL possible_keys: IX_test key: NULL key_len: NULL ref: NULL rows: 204657 Extra: Using where; Using temporary 1 row in set (0.00 sec) So it's not even using my index =( And if I force it: mysql SELECT DISTINCT cOrigin from the_table FORCE INDEX (IX_test) WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); . . | IDPJG | | JPSHI | | INICD | | CNJIU | | USHSV | +-+ 402 rows in set (4.27 sec) It is even slower. I have done a check table, analyze table etc. If you have any suggestions please let me know, thanks for your precious time! Best Regards, Mattias -- 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: Date increment
Simply: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html Just check the DATE_ADD part. On 11/30/05, Peter Lauri [EMAIL PROTECTED] wrote: Best group member, I have a field called expiredate of type 'date'. I would like to add 17 days to the expiredate without doing any scripting, is that possible? Example: Expiredate is 2005-11-30 and I want to extend the expiredate with 17 days. Is there any function in MySQL that adds days to a date? A solution for this would be to write a PHP script that takes the date and adds 17 days to it (checking month overlap and stuff), but I want to skip scripting if there is an solution within MySQL that does it for me. /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date increment
SELECT DATE_ADD(expiredate, INTERVAL 17 DAYS) http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Peter Lauri [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Best group member, I have a field called expiredate of type 'date'. I would like to add 17 days to the expiredate without doing any scripting, is that possible? Example: Expiredate is 2005-11-30 and I want to extend the expiredate with 17 days. Is there any function in MySQL that adds days to a date? A solution for this would be to write a PHP script that takes the date and adds 17 days to it (checking month overlap and stuff), but I want to skip scripting if there is an solution within MySQL that does it for me. /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date increment
You could use something like from_unixtime(to_unixtime(dateField)+(86400*17)) I store dates in unix timestamp format in bigints whenever possible for this very reason. =C= Peter Lauri wrote: Best group member, I have a field called expiredate of type ‘date’. I would like to add 17 days to the expiredate without doing any scripting, is that possible? Example: Expiredate is 2005-11-30 and I want to extend the expiredate with 17 days. Is there any function in MySQL that adds days to a date? A solution for this would be to write a PHP script that takes the date and adds 17 days to it (checking month overlap and stuff), but I want to skip scripting if there is an solution within MySQL that does it for me. /Peter -- | | Cal Evans | http://www.calevans.com | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date increment
Peter Is there any function in MySQL that adds days to a date? See ADDDATE(...) at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html. PB - Peter Lauri wrote: Best group member, I have a field called expiredate of type date. I would like to add 17 days to the expiredate without doing any scripting, is that possible? Example: Expiredate is 2005-11-30 and I want to extend the expiredate with 17 days. Is there any function in MySQL that adds days to a date? A solution for this would be to write a PHP script that takes the date and adds 17 days to it (checking month overlap and stuff), but I want to skip scripting if there is an solution within MySQL that does it for me. /Peter No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/189 - Release Date: 11/30/2005 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/189 - Release Date: 11/30/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date storage format
Can anyone tell me what advantages there are in keeping dates and times in a MySQL DateTime field, as opposed to storing its string equivalent in a Varchar field ? Decent sorting, validity checking, being able to use the DATE and TIME functions etc etc... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date storage format
Hello. For example, the size of the field. DATETIME uses only 8 bytes. See: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html Sinang, Danny wrote: Hello, Can anyone tell me what advantages there are in keeping dates and times in a MySQL DateTime field, as opposed to storing its string equivalent in a Varchar field ? Regards, Danny -- 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: Date Ranges
---snip--
Given any two dates, MySQL can tell if a third date is within
that range.
That's easy.
To actually return a list of all dates between any arbitrary
pair of dates requires some form of loop (v5.0+) or a lookup
into a table populated with all possible dates (any version
that supports joins). It's possible to get MySQL to give you
a list of dates but not as a native function. There is just
no facility built into the system to return that list. Sorry!
Have you seen such a function before? If so, where and what
was it called?
Most of the times when people ask this question, they have a
report they want to write and need to generate blank rows
for dates that aren't in the data. Is that what you need or
is there some other purpose to your question?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Indeed, we were asked this question on the freenode IRC channel a few weeks
ago - somebody wanted to calculate how many days between two dates fell
within a weekend, where there were possibly gaps within the data that they
had. I came up with the following procedure which gives an example of how to
do it with a loop and a temporary table, then some other little date
statistics for the given date range. The main point of interest, as Shawn
noted, is the loop that creates the dates within the range, and inserts them
in to a temporary table:
DROP PROCEDURE date_stats//
CREATE PROCEDURE date_stats ( IN sdate DATE, IN edate DATE)
BEGIN
DECLARE dates_done INT DEFAULT 0;
CREATE TEMPORARY TABLE date_range ( tdate DATE );
dates: LOOP
IF dates_done = 1 THEN
LEAVE dates;
END IF;
CASE WHEN sdate edate
THEN INSERT INTO date_range VALUES (sdate);
ELSE SET dates_done = 1;
END CASE;
SET sdate = sdate + INTERVAL 1 DAY;
END LOOP dates;
SELECT SUM(IF(WEEKDAY(tdate) IN (0,1,2,3,4),1,0)) as week_day_cnt,
SUM(IF(WEEKDAY(tdate) IN (5,6),1,0)) as weekend_day_cnt
FROM date_range;
SELECT ROUND(COUNT(*)/7) as number_of_weeks,
COUNT(*) as number_of_days
FROM date_range;
SELECT TIMESTAMPDIFF(SECOND,MIN(tdate),MAX(tdate)) as seconds_diff,
TIMESTAMPDIFF(MINUTE,MIN(tdate),MAX(tdate)) as minutes_diff,
TIMESTAMPDIFF(HOUR,MIN(tdate),MAX(tdate)) as hours_diff
FROM date_range;
DROP TEMPORARY TABLE date_range;
END;
//
CALL date_stats('2005-01-01','2005-02-01')//
+--+-+
| week_day_cnt | weekend_day_cnt |
+--+-+
| 21 | 10 |
+--+-+
1 row in set (2.78 sec)
+-++
| number_of_weeks | number_of_days |
+-++
| 4 | 31 |
+-++
1 row in set (2.78 sec)
+--+--++
| seconds_diff | minutes_diff | hours_diff |
+--+--++
| 2592000 |43200 |720 |
+--+--++
1 row in set (2.78 sec)
Query OK, 0 rows affected (2.97 sec)
Hope this helps,
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
http://leithal.cool-tools.co.uk
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Ranges
At 20:47 +0100 9/6/05, Shaun wrote: Hi, Given two dates, can Mysql calculate and return all the dates that occur between them? No. Given two dates, MySQL can determine which of a set of already-existing dates stored in a table occur between them and return those. -- 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: Date Ranges
Shaun [EMAIL PROTECTED] wrote on 09/06/2005 03:47:25 PM: Hi, Given two dates, can Mysql calculate and return all the dates that occur between them? Thanks for your advice. Given any two dates, MySQL can tell if a third date is within that range. That's easy. To actually return a list of all dates between any arbitrary pair of dates requires some form of loop (v5.0+) or a lookup into a table populated with all possible dates (any version that supports joins). It's possible to get MySQL to give you a list of dates but not as a native function. There is just no facility built into the system to return that list. Sorry! Have you seen such a function before? If so, where and what was it called? Most of the times when people ask this question, they have a report they want to write and need to generate blank rows for dates that aren't in the data. Is that what you need or is there some other purpose to your question? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Date arithmetic: 2005-08-31 - 1
Barbara,
... I don't know if I'm being asked to add or subtract days...
Why would you want to know that? ADDDATE() doesn't care:
SET @x = -1;
SELECT ADDDATE('1975-1-1', INTERVAL @x DAY);
+--+
| ADDDATE('1975-1-1', INTERVAL @x DAY) |
+--+
| 1974-12-31 |
+--+
PB
-
In ADDDATE( date_value, INTERVAL expr DAYS), 'expr' can resolve to a
positive or negative int.
PB
Barbara Deaton wrote:
Unfortunately no, because I don't know if I'm being asked to add or subtract days. I'm just given a value, and have to transform that into something that can be added or subtracted.
So for example, all I get with is value 1 meaning 1 day and I need to do something with a date, for db2 through some calculations I turn this into 0001. and my resulting SQL statement then becomes:
select count(*) from cwdd where col2 - 0001. = {d '2005-06-07'}
And yes, the period is required for DB2.
I'm trying to figure out what calculation or modifications I need to do to the value passed in, in this case 1. So that I can turn it into something I can pass down for MySQL to do the math on.
Thanks for the suggestion though, it just won't work in this case.
-Barb.
-Original Message-
From: Freddie Sorensen [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 23, 2005 3:45 PM
To: Barbara Deaton; mysql@lists.mysql.com
Subject: AW: Date arithmetic: 2005-08-31 - 1
Barbara,
Can't you use the ADDDATE function ?
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
Freddie
-Ursprngliche Nachricht-
Von: Barbara Deaton [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 23. August 2005 21:37
An: mysql@lists.mysql.com
Betreff: Date arithmetic: 2005-08-31 - 1
All,
I know MySQL comes with all sorts of wonderful functions to do date
arithmetic, the problem is the context that my application is being
called in I don't know if a user wants me to add or subtract days.
I'm just given the number of days that need to be either added or
subtracted from the date given.
So for example, if your table was
mysql select * from dtinterval;
+
| datecol
+
2005-09-01
2005-08-30
2005-08-31
+--
a user could enter:
select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
Which is our applications SQL, my part of the product is only give the
value 1, I have to transform that into something MySQL will understand
as 1 day and then pass that back into the SQL statement to be passed
down to the MySQL database. I transform our applications SQL into
select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =
'1974-12-04'
I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
and that just gives me a year
mysql select '2005-08-31' - 1;
+--+
| '2005-08-31' - 1 |
+--+
| 2004 |
+--+
What do I need to translate the 1 into in order to get back the value
'2005-08-30' ?
Thanks for your help.
Barbara
--
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]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date arithmetic: 2005-08-31 - 1
Do You know about INTERVAL? Use it in an exprecssion or funtion as ..INTERVAL expr type where expr is any numerical value * The INTERVAL keyword and the type specifier are not case sensitive. The following table shows how the type and expr arguments are related: type Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND 'MINUTES.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND 'HOURS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE 'HOURS:MINUTES' DAY_MICROSECOND 'DAYS.MICROSECONDS' DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS HOURS' YEAR_MONTH 'YEARS-MONTHS' mysql select min(addr_id) from addresses; +--+ | min(addr_id) | +--+ |2 | +--+ 1 row in set (0.00 sec) mysql select now() + INTERVAL min(addr_ID) Day from addresses; +---+ | now() + INTERVAL min(addr_ID) Day | +---+ | 2005-08-25 15:38:15 | +---+ 1 row in set (0.00 sec) mysql select now() - ; +-+ | now() | +-+ | 2005-08-23 15:38:31 | +-+ 1 row in set (0.00 sec) -Original Message- From: Barbara Deaton [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 23, 2005 2:37 PM To: mysql@lists.mysql.com Subject: Date arithmetic: 2005-08-31 - 1 All, I know MySQL comes with all sorts of wonderful functions to do date arithmetic, the problem is the context that my application is being called in I don't know if a user wants me to add or subtract days. I'm just given the number of days that need to be either added or subtracted from the date given. So for example, if your table was mysql select * from dtinterval; + | datecol + 2005-09-01 2005-08-30 2005-08-31 +-- a user could enter: select count(*) from dtinterval where datecol - 1 = '30AUG2005'd; Which is our applications SQL, my part of the product is only give the value 1, I have to transform that into something MySQL will understand as 1 day and then pass that back into the SQL statement to be passed down to the MySQL database. I transform our applications SQL into select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) = '1974-12-04' I know that just doing the -1 is wrong, since select '2005-08-31' - 1 and that just gives me a year mysql select '2005-08-31' - 1; +--+ | '2005-08-31' - 1 | +--+ | 2004 | +--+ What do I need to translate the 1 into in order to get back the value '2005-08-30' ? Thanks for your help. Barbara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date arithmetic: 2005-08-31 - 1
Barbara Deaton [EMAIL PROTECTED] wrote on 08/23/2005 03:36:52 PM: All, I know MySQL comes with all sorts of wonderful functions to do date arithmetic, the problem is the context that my application is being called in I don't know if a user wants me to add or subtract days. I'm just given the number of days that need to be either added or subtracted from the date given. So for example, if your table was mysql select * from dtinterval; + | datecol + 2005-09-01 2005-08-30 2005-08-31 +-- a user could enter: select count(*) from dtinterval where datecol - 1 = '30AUG2005'd; Which is our applications SQL, my part of the product is only give the value 1, I have to transform that into something MySQL will understand as 1 day and then pass that back into the SQL statement to be passed down to the MySQL database. I transform our applications SQL into select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) = '1974-12-04' I know that just doing the -1 is wrong, since select '2005-08-31' - 1 and that just gives me a year mysql select '2005-08-31' - 1; +--+ | '2005-08-31' - 1 | +--+ | 2004 | +--+ What do I need to translate the 1 into in order to get back the value '2005-08-30' ? Thanks for your help. Barbara You are right. There are a lot of date functions in MySQL. However, I am not sure exactly what your needs are... I think you are trying to compute date intervals (did you NOT see the INTERVAL keyword when you RTFM?). Most of the date functions currently supported are for v4.1 or newer and you didn't say which version you are on. The pre-v4.1 way of doing date math is to convert your datetime values into seconds, add or subtract some number of seconds, then convert your answer back into a date. To do that you need the UNIX_TIMESTAMP()/FROM_UNIXTIME() pair of functions ( http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html) The pre v4.1 way of computing a date range (it even works with non-integral ranges as in my example) SET @basedate = '2005-05-03 16:00:00', @daysplusminus = 1.5; SET @baseunix = UNIX_TIMESTAMP(@basedate), @secsplusminus = [EMAIL PROTECTED]; SET @startdate = FROM_UNIXTIME(@baseunix - @secsplusminus), @enddate = FROM_UNIXTIME(@baseunix + @secsplusminus); SELECT @startdate, @enddate; +-+-+ | @startdate | @enddate| +-+-+ | 2005-05-02 04:00:00 | 2005-05-05 04:00:00 | +-+-+ 1 row in set (0.00 sec) The post v4.1 way (uses only integral days): SET @basedate = '2005-05-31', @daysplusminus = 1.5; SET @startdate = @basedate - interval @daysplusminus day, @enddate = @basedate + interval @daysplusminus day; SELECT @startdate, @enddate; +++ | @startdate | @enddate | +++ | 2005-05-30 | 2005-06-01 | +++ 1 row in set (0.00 sec) Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Date arithmetic: 2005-08-31 - 1
Unfortunately no, because I don't know if I'm being asked to add or subtract
days. I'm just given a value, and have to transform that into something that
can be added or subtracted.
So for example, all I get with is value 1 meaning 1 day and I need to do
something with a date, for db2 through some calculations I turn this into
0001. and my resulting SQL statement then becomes:
select count(*) from cwdd where col2 - 0001. = {d '2005-06-07'}
And yes, the period is required for DB2.
I'm trying to figure out what calculation or modifications I need to do to the
value passed in, in this case 1. So that I can turn it into something I can
pass down for MySQL to do the math on.
Thanks for the suggestion though, it just won't work in this case.
-Barb.
-Original Message-
From: Freddie Sorensen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 23, 2005 3:45 PM
To: Barbara Deaton; mysql@lists.mysql.com
Subject: AW: Date arithmetic: 2005-08-31 - 1
Barbara,
Can't you use the ADDDATE function ?
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
Freddie
-Ursprüngliche Nachricht-
Von: Barbara Deaton [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 23. August 2005 21:37
An: mysql@lists.mysql.com
Betreff: Date arithmetic: 2005-08-31 - 1
All,
I know MySQL comes with all sorts of wonderful functions to do date
arithmetic, the problem is the context that my application is being
called in I don't know if a user wants me to add or subtract days.
I'm just given the number of days that need to be either added or
subtracted from the date given.
So for example, if your table was
mysql select * from dtinterval;
+
| datecol
+
2005-09-01
2005-08-30
2005-08-31
+--
a user could enter:
select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
Which is our applications SQL, my part of the product is only give the
value 1, I have to transform that into something MySQL will understand
as 1 day and then pass that back into the SQL statement to be passed
down to the MySQL database. I transform our applications SQL into
select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =
'1974-12-04'
I know that just doing the -1 is wrong, since select '2005-08-31' - 1
and that just gives me a year
mysql select '2005-08-31' - 1;
+--+
| '2005-08-31' - 1 |
+--+
| 2004 |
+--+
What do I need to translate the 1 into in order to get back the value
'2005-08-30' ?
Thanks for your help.
Barbara
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date arithmetic: 2005-08-31 - 1
Barbara Deaton wrote: All, I know MySQL comes with all sorts of wonderful functions to do date arithmetic, the problem is the context that my application is being called in I don't know if a user wants me to add or subtract days. I'm just given the number of days that need to be either added or subtracted from the date given. So for example, if your table was mysql select * from dtinterval; + | datecol + 2005-09-01 2005-08-30 2005-08-31 +-- a user could enter: select count(*) from dtinterval where datecol - 1 = '30AUG2005'd; Which is our applications SQL, my part of the product is only give the value 1, I have to transform that into something MySQL will understand as 1 day and then pass that back into the SQL statement to be passed down to the MySQL database. I transform our applications SQL into select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) = '1974-12-04' I know that just doing the -1 is wrong, since select '2005-08-31' - 1 and that just gives me a year mysql select '2005-08-31' - 1; +--+ | '2005-08-31' - 1 | +--+ | 2004 | +--+ What do I need to translate the 1 into in order to get back the value '2005-08-30' ? Thanks for your help. Barbara The answer is contained in the previous replies, and in the referenced manual page. Translate the 1 into INTERVAL 1 DAY So your query becomes select COUNT(*) from `dtinterval` where `dtinterval`.`datecol` - INTERVAL 1 DAY = '1974-12-04'; In general, n days becomes INTERVAL n DAY, so the query becomes select COUNT(*) from `dtinterval` where `dtinterval`.`datecol` - INTERVAL n DAY = '1974-12-04'; That isn't the right way to do it, however. This query compares a value which depends on the row with a constant. No index on datecol can be used to satisfy this query. You get a full-table scan, with the date calculation done on every row. Always write your WHERE clauses to avoid calculations involving row values, if possible. In this case, your query should be select COUNT(*) from `dtinterval` where `dtinterval`.`datecol` = '1974-12-04' + INTERVAL n DAY; Adding n days to the constant date on the right results in a constant, so it can be done once, then the resulting constant can be compared with the values of datecol. In this case, an index on datecol can be used to make this quick. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date arithmetic: 2005-08-31 - 1
[EMAIL PROTECTED] wrote: snip You are right. There are a lot of date functions in MySQL. However, I am not sure exactly what your needs are... I think you are trying to compute date intervals (did you NOT see the INTERVAL keyword when you RTFM?). Most of the date functions currently supported are for v4.1 or newer and you didn't say which version you are on. The pre-v4.1 way of doing date math is to convert your datetime values into seconds, add or subtract some number of seconds, then convert your answer back into a date. To do that you need the UNIX_TIMESTAMP()/FROM_UNIXTIME() pair of functions ( http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html) snip Time to reread the fine manual. DATE_ADD() and DATE_SUB() existed in mysql 3.23. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date comparison question
Kapoor, Nishikant [EMAIL PROTECTED] wrote on
08/01/2005 09:55:21 AM:
I am probably missing something very simple, but appreciate it if
someone could point me to that.
I am doing this query on following table to fetch recs for a month:
SELECT fName, lName, acctOpenDate FROM test WHERE acctOpenDate =
'2005-07-01' AND acctOpenDate = '2005-07-31';
I expect to see all 4 rows, but I see only the latter two. The ones
dated '2005-07-31' are not listed even though I am using
acctOpenDate = '2005-07-31'.
fName lName acctOpenDate
Ccc Cc 2005-07-30 21:08:51
Ddd Dd 2005-07-30 05:05:48
I am using mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu
(i586)
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`fName` varchar(10) NOT NULL default '',
`lName` varchar(10) NOT NULL default '',
`acctOpenDate` datetime NOT NULL default '-00-00 00:00:00'
) TYPE=MyISAM;
# Dumping data for table `test`
INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Aaa',
'Aa', '2005-07-31 20:56:06');
INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Bbb',
'Bb', '2005-07-31 10:51:06');
INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Ccc',
'Cc', '2005-07-30 21:08:51');
INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Ddd',
'Dd', '2005-07-30 05:05:48');
Thanks much,
Nishi
Yes, it is something simple that you are missing. Don't feel bad, this
same simple thing has thrown me for a loop a time or two.
It's in your comparison with your end date. What you are comparing it is
= 'some date value'. When you do that, the value of 'some date value' is
expanded to include the time value of '00:00:00' so that what you are
doing is comparing your data to the extreme earliest START of the date.
Anything logged within that date (say at 9AM) will not be found with this
comparison.
Two options:
a) explicity state the latest possible time for your ending date:
AND acctOpenDate = '2005-07-31 23:59:59'
b) look for values LESS than the start of the NEXT day:
AND acctOpenDate '2005-08-01'
make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Date/Time Problem with V5.0.6 views
Hello. You said that you had created a view, but you continued using Tab_A instead of Tab_A_View in your next queries. Did you want to use view Tab_A_View? On my MySQL 5.0.9 all queries works both with view and original table. See: mysql desc Tab_A; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | datetime_field | datetime | NO | | | | | num_field | bigint(20) | NO | | | | +++--+-+-+---+ mysql desc Tab_A_View; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | datetime_field | datetime | NO | | -00-00 00:00:00 | | | num_field | bigint(20) | NO | | 0 | | +++--+-+-+---+ mysql Select datetime_field, num_field from Tab_A_View where datetime_field='2005-03-10' andnum_field = 1234; +-+---+ | datetime_field | num_field | +-+---+ | 2005-03-10 00:00:00 | 1234 | | 2005-03-10 00:00:00 | 1234 | +-+---+ mysql Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' andnum_field = 1234; +-+---+ | datetime_field | num_field | +-+---+ | 2005-03-10 00:00:00 | 1234 | | 2005-03-10 00:00:00 | 1234 | +-+---+ [EMAIL PROTECTED] wrote: Hello everyone, I cannot figure this out. I have a table like the following: Tab_A datetime_field datetime not null, num_field bigint not null I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; I return 2 rows correctly. I then create the view: Create view Tab_A_View as select * from Tab_A; I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; 0 rows returned! HOWEVER, IF I do the query as such: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10 00:00:00' and num_field = 1234; 2 rows returned correctly. Is there an implementation difference? Regards, George __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- 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: Date validation using mysql
Anoop kumar V [EMAIL PROTECTED] wrote on 04/29/2005 11:00:11 AM: Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop What the user cannot disable and what you have full control over is the page that receives the request from the user (the page that processes the form the user submits). Use that page to validate your user's submission and either kick the error(s) back to the user (possibly allowing them to re-enter the information) or move on to generating the results (assuming everything was OK). It's a wise idea to validate any user-provided information anyway before sending it to the server. That way you have the ability to head off a SQL server error (or SQL injection attack) before it ever happens. It not only keeps your server more responsive (it doesn't have to spend the time tell you it was bad data) but you have greater control over the entire process. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Date validation using mysql
So is there a function in mysql that I can call to validate dates?? Or do I need to build it? Thanks, Anoop On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Anoop kumar V [EMAIL PROTECTED] wrote on 04/29/2005 11:00:11 AM: Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop What the user cannot disable and what you have full control over is the page that receives the request from the user (the page that processes the form the user submits). Use that page to validate your user's submission and either kick the error(s) back to the user (possibly allowing them to re-enter the information) or move on to generating the results (assuming everything was OK). It's a wise idea to validate any user-provided information anyway before sending it to the server. That way you have the ability to head off a SQL server error (or SQL injection attack) before it ever happens. It not only keeps your server more responsive (it doesn't have to spend the time tell you it was bad data) but you have greater control over the entire process. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Thanks and best regards, Anoop
Re: Date validation using mysql
You could try checkdate()... chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
You can, sort of. You can use a type of query like this: select if(now()'2005-04-28', 1, 0); Which will return a 1 or a 0 if the date is greater than the current date/time. But that's a very weak comparison, prone to error, since the date must be in the MySQL readable format. So while you could use MySQL to validate a date, I wouldn't. Use javascript validation only as a user interface convenience. Never rely on in for validating input to your database. Always assume the data you are receiving did not come from something you made. You should use the programming or scripting language (i.e. PHP, Perl) you are using, to validate the data and format it properly. On Apr 29, 2005, at 11:00 AM, Anoop kumar V wrote: Is it possible to do a date field validation using an sql query. Its like we have an html field- its a free form text field and the end user should type in a valid date, of course in a predefined format only (MM/dd/yyy). I tried using javascript but either it works on some browsers and not in others or the user can just disable javascript in eth browser. SO I was hoping that I can use an sql query (some function in mysql) which will return true or false based on whether the date is in the future, valid (considering leap years etc..) and in the predefined format. Is this possible - is it a viable thing to do - all I want is a foolproof way of validating the date input field. -- Thanks and best regards, Anoop -- 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: Date validation using mysql
Anoop snip You could try checkdate()... /snip Apologies for erroneous advice - I am evidently suffering from list psychosis... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
No problem - I followed up and found out that this function is not available. THought probably you were referring to a later version of Mysql.. anyways... We use Java - and maybe I could use that - I was just wondering if I could help reinventing something already there. Thanks so much for the suggestions/ Anoop On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote: Anoop snip You could try checkdate()... /snip Apologies for erroneous advice - I am evidently suffering from list psychosis... Chris -- Thanks and best regards, Anoop
Re: Date validation using mysql
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day), interval
1 day),'%Y%m%d') = date_format('yourdate','%Y%m%d');
It will give you 1 if date is valid.
Best regards,
Mikhail.
- Original Message -
From: Anoop kumar V [EMAIL PROTECTED]
To: Chris Ramsay [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, April 29, 2005 6:10 PM
Subject: Re: Date validation using mysql
No problem - I followed up and found out that this function is not
available. THought probably you were referring to a later version of Mysql..
anyways...
We use Java - and maybe I could use that - I was just wondering if I could
help reinventing something already there.
Thanks so much for the suggestions/
Anoop
On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote:
Anoop
snip
You could try checkdate()...
/snip
Apologies for erroneous advice - I am evidently suffering from list
psychosis...
Chris
--
Thanks and best regards,
Anoop
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
Woww - this is great - I think this is exactly what I wanted!!
Thanks a Ton Mikhail. :-)
Anoop
On 4/29/05, Mikhail Entaltsev [EMAIL PROTECTED] wrote:
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day),
interval
1 day),'%Y%m%d') = date_format('yourdate','%Y%m%d');
It will give you 1 if date is valid.
Best regards,
Mikhail.
- Original Message -
From: Anoop kumar V [EMAIL PROTECTED]
To: Chris Ramsay [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, April 29, 2005 6:10 PM
Subject: Re: Date validation using mysql
No problem - I followed up and found out that this function is not
available. THought probably you were referring to a later version of
Mysql..
anyways...
We use Java - and maybe I could use that - I was just wondering if I could
help reinventing something already there.
Thanks so much for the suggestions/
Anoop
On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote:
Anoop
snip
You could try checkdate()...
/snip
Apologies for erroneous advice - I am evidently suffering from list
psychosis...
Chris
--
Thanks and best regards,
Anoop
--
Thanks and best regards,
Anoop
Re: DATE TIME
On Wed, Apr 06, 2005 at 12:06:37PM +0200, Hans Bernard wrote: i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- Use date_format(, %d-%b-%Y %H:%m) in your SELECT? bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
- Original Message - From: Hans Bernard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 6:06 AM Subject: DATE TIME Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help I am 99% sure that you cannot actually store dates or times in the formats that you want. However, I am also 99% sure that you *can* retrieve them in those formats by reformatting them when you retrieve them. I think you will find all of the information you need under functions in the MySQL manual. Unfortunately, the server appears to be down at the moment so I can't give you a link to the appropriate page. Also, I suspect the reason that you are seeing dates of '-00-00' is that you are supplying the dates in the wrong format within your INSERT statements and that MySQL is storing '-00-00' as the default. If you supply the dates in the format MySQL is expecting, you should get back the dates that you actually stored. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
thanks for the hints date_format() solved my problem my select query in php is now the following: $query = SELECT id, calltt, date_format(calldate, '%d-%b-%Y') AS calldate2, date_format(calltime, '%H:%i') AS calltime2,area, problem, solution, assignto, status FROM ticketing ; hans Rhino wrote: - Original Message - From: Hans Bernard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 6:06 AM Subject: DATE TIME Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help I am 99% sure that you cannot actually store dates or times in the formats that you want. However, I am also 99% sure that you *can* retrieve them in those formats by reformatting them when you retrieve them. I think you will find all of the information you need under functions in the MySQL manual. Unfortunately, the server appears to be down at the moment so I can't give you a link to the appropriate page. Also, I suspect the reason that you are seeing dates of '-00-00' is that you are supplying the dates in the wrong format within your INSERT statements and that MySQL is storing '-00-00' as the default. If you supply the dates in the format MySQL is expecting, you should get back the dates that you actually stored. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- 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: Date Functions in MySQL
Clarence wrote: I have a log table that records certain transactions on one of my sites. I'm using a timestamp field to mark the date/time of each transaction. I'm trying to run a query that will display the transactions by date using the following SQL: SELECT COUNT(log_id) AS total, WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d'), 3) AS week, MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d')) AS month, FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y') AS year FROM log However, when I run this query I get very strange results for the week and month. They don't seem to match up. The TIMESTAMP column type is NOT a UNIX timestamp...! It is a 'normal' datetime column, you should use just WEEK(cdate), MONTHNAME(cdate) and so on. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Functions in MySQL
Shoot me now, please. Thanks - I don't know how I missed that! Thanks - re-ran the query and things seem to be a-ok! On Wed, 23 Feb 2005 18:42:17 +0100, Roger Baklund [EMAIL PROTECTED] wrote: Clarence wrote: I have a log table that records certain transactions on one of my sites. I'm using a timestamp field to mark the date/time of each transaction. I'm trying to run a query that will display the transactions by date using the following SQL: SELECT COUNT(log_id) AS total, WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d'), 3) AS week, MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d')) AS month, FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y') AS year FROM log However, when I run this query I get very strange results for the week and month. They don't seem to match up. The TIMESTAMP column type is NOT a UNIX timestamp...! It is a 'normal' datetime column, you should use just WEEK(cdate), MONTHNAME(cdate) and so on. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DATE problem
From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql =SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE problem
Mike Johnson wrote: From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! It will work, but you don't want to do that. As soon as you run your column through a function, you can no longer use the index on that column to choose rows. In other words, this query results in a full table scan. Mysql will have to execute DATE_FORMAT() on table.created for every single row to make the comparison. Instead, you should always compare columns to constants, if possible. That's not as bad as it may sound, since functions of constants are constants. In this case (assuming no rows with created in the future), you should use SELECT COUNT(table.id) FROM table WHERE table.created = CURDATE(); Mysql will calculate CURDATE() once, convert it to a DATETIME by adding zeros, then select matching rows using the index on table.created. Another example: Say you wanted the rows which were created in the last 72 hours. Here are some equivalent conditions: WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(created) = 72*60*60 WHERE created + INTERVAL 72 HOUR = NOW() WHERE created = NOW() - INTERVAL 72 HOUR Only the last one can use an index on created, however. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion on Mysql 4.0.2
Minh La wrote: So far the hours that I have spent have been in vain. Next time a couple of minutes with the Fine Manual instead? :-) I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? Quoting the FM: STR_TO_DATE() is available as of MySQL 4.1.1. FWIW, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion on Mysql 4.0.2
Minh La wrote: Hi, Can some help me with a date conversion problme that I am having. I have a date data in the following format: 'Month Days Year Hour:Minute AM/PM' Example: 'Aug 21, 2004 2:00 PM' So far the hours that I have spent have been in vain. I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? Minh: Your options are: * upgrade to 4.1 * parse and convert the date in your applications * use an ugly combination of SUBSTRING() and CASE to parse out the date * write a UDF implementing STR_TO_DATE() * backport STR_TO_DATE() to 4.0 -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date query and date removal
- Original Message - From: darrell troth [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 26, 2004 3:29 PM Subject: Date query and date removal This will seem an easy question, but I cannot find a sample anywhere: I have a database of bands appearing at a club. I want to update the list sorted by date and have the results only show current date and beyond (i.e. - remove band that played last night from results page). This query is driving me nuts and only thing left to finish a site. It should be a pretty straight-forward query: just compare the performance date to the current date in your WHERE clause. Something like this: select band from performances where performance_date = current_date(); Have you tried that? If so, what error did you get? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date query and date removal
- Original Message -
From: darrell troth [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, November 26, 2004 3:29 PM
Subject: Date query and date removal
This will seem an easy question, but I cannot find a sample anywhere:
I have a database of bands appearing at a club. I want to update the list
sorted by date and have the results only show current date and beyond
(i.e. - remove band that played last night from results page). This query is
driving me nuts and only thing left to finish a site.
I just realized that I misread your initial post; you don't just want to
*see* future performances, you want to delete past ones.
Here is a quick script I knocked together to demonstrate the solution. When
I run the first SELECT, I get all of the performances listed in
chronological order. Then, the delete gets rid of all performances that have
already taken place. When I run the second SELECT, I get only the
performances that occur today or in the future, specifically Pat Metheny and
Yes, listed in chronological order.
---
use tmp;
drop table if exists performances;
create table if not exists performances
(performer char(30) not null,
performance_date date not null,
primary key(performer, performance_date));
insert into performances values ('Pink Floyd', '2004-11-04');
insert into performances values ('Pat Metheny', '2004-11-26');
insert into performances values ('Yes', '2004-11-30');
select * from performances
order by performance_date;
delete from performances
where performance_date current_date();
select *
from performances
order by performance_date;
---
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date type select
- Original Message - From: Jerry Swanson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 11:20 AM Subject: date type select I have comlumn in mysql type datetime. I need to get the date in DATE format. select DATE(reg_date) as test from a1; It gives me an error. How to select date in different date format? RTFM? http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date type select
What version of MySQL are you running? If you are on a version of 4.1 or higher, you have many more options than if you aren't. (I would refer you to the online manual but it seems to be busy right now and I can't get to the URL I need.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jerry Swanson [EMAIL PROTECTED] wrote on 10/26/2004 11:20:16 AM: I have comlumn in mysql type datetime. I need to get the date in DATE format. select DATE(reg_date) as test from a1; It gives me an error. How to select date in different date format? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date as Primary ID
Ferhat BINGOL wrote: Hi Scoot, I do my table structure like that CREATE TABLE `test_table` ( `timestamp` date NOT NULL default '-00-00', `data` varchar(5) NOT NULL default '', PRIMARY KEY (`timestamp`), KEY `timestamp` (`timestamp`) ) TYPE=MyISAM; Correct me if I'm wrong, but I think the second index is absolutely useless. It will just use more space. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date as Primary ID
Scott Hamm wrote: How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? You can't. Timstamps are only unique down to 1 second. It is quite possible to insert hundreds, if not thousands of records in that length of time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date as Primary ID
Ok, so that means what Jay Blanchard just now said recently wasn't possible? I'm trying to figure out a way to ensure that date is not duplicated i.e.: create performance ( `DateID` whatever, `Projected_Num` int(7), `Actual_Num` int(7), primary key [something to prevent DateID duplication] ); -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 9:42 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Date as Primary ID Scott Hamm wrote: How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? You can't. Timstamps are only unique down to 1 second. It is quite possible to insert hundreds, if not thousands of records in that length of time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date as Primary ID
Hi Scoot, I do my table structure like that CREATE TABLE `test_table` ( `timestamp` date NOT NULL default '-00-00', `data` varchar(5) NOT NULL default '', PRIMARY KEY (`timestamp`), KEY `timestamp` (`timestamp`) ) TYPE=MyISAM; Than I send a query as below INSERT INTO `test_table` ( `timestamp` , `data` ) VALUES ( CURDATE( ) , 'test1' ); and this is the result SQL-query: SELECT * FROM `test_table` LIMIT 0, 30; Rows: 1 timestamp data 2004-10-18test1 Cheers, Ferhat - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:20 PM Subject: Date as Primary ID How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? -- 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]
