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)STR_TO_DATE()
returns a
DATETIME
value if the format string contains both date and time parts, or a
DATE
or
TIME
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 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 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 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)
> 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
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 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)
> 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 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 + or - 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 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 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 COMPARISON
This would do it if you are just comparing two dates: $max_date = ($date1>$date2?$date1:$date2); It's just using the one line form of an if statement. Note that the function is incorrect if they date are equal. On Tuesday, July 15, 2003, at 12:36 PM, Miguel Perez wrote: I was wondering if there is a date function to get the older date between two dates?. Or smething that indicates me that one date is older than the other one. I know that I have the function YEAR,MONTH,DAY and I can use them, but I don't know if exists a function that can do the same. -- 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 COMPARISON
Miguel - You can compare dates directly using "<" and ">" - no need for a special function. Dates are older when they are less than other dates. Cheers. Jeff On Tuesday, July 15, 2003, at 09:36 AM, Miguel Perez wrote: Hi: I was wondering if there is a date function to get the older date between two dates?. Or smething that indicates me that one date is older than the other one. I know that I have the function YEAR,MONTH,DAY and I can use them, but I don't know if exists a function that can do the same. Greetings everyone. Thnx in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.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 comparison assistance please
Paul, Friday, August 16, 2002, 1:19:15 AM, you wrote: PM> I have a column defined in a table as type date. I desire to include a date PM> comparison in the WHERE clause of the following statement. The following PM> statement is not working correctly, I suspect that there is a formatting PM> issue. The statement returns true even if USER.elig_date has an earlier date PM> then CURDATE(). PM> Here is my sql query: PM> select user_id from USER where USER.elig_date >= CURDATE() ; What is the version of MySQL server you use? What is the table structure? It's a very simple query and it should work. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date comparison assistance please
describe user. Paul Maine wrote: >I have a column defined in a table as type date. I desire to include a date >comparison in the WHERE clause of the following statement. The following >statement is not working correctly, I suspect that there is a formatting >issue. The statement returns true even if USER.elig_date has an earlier date >then CURDATE(). > >Here is my sql query: >select user_id from USER where USER.elig_date >= CURDATE() ; > >Thank You > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Comparison Problem...
>Just when I thought I was getting the hang of this mysql stuff, the simplest
>of tasks
>is causing me to think about pulling my hair out.
>
>My table:
>
>NEWS {
> newsID (INT(3), AUTO-INCREMENT),
> newsTitle (VARCHAR (50)),
> newsText (BLOB),
> newsDate (DATE)
>}
It's better to use copy and paste when reporting code - what you
show there is illegal syntax.
>
>I just want to filter OUT any rows that have a 'future' date value in
>'newsDate' column.
>(ie. Don't select any news that hasn't happened yet!)
>
>Query, I gather the WHERE clause deals with -MM-DD formatted dates:
>
>SELECT * FROM news
> WHERE newsDate < DATE_ADD(CUR_DATE(), INTERVAL 1 DAY)
> ORDER BY newsDate DESC
That's illegal, too. There is no CUR_DATE() function, it's CURDATE().
Other than that, it looks okay. If it produces no rows, I would assume
that means that you have no news that is not in the future, but I guess
that's probably not true. You can see what dates are being compared
like this, which may help:
SELECT newsDate, DATE_ADD(CURDATE(),INTERVAL 1 DAY) FROM news;
Also, I expect that your query would be simpler like this:
SELECT * FROM news WHERE newsDate <= CURDATE() ORDER BY newsDate DESC;
(Note the <= rather than <...)
>
>If I take out the WHERE clause the whole table gets SELECT-ed, it works
>fine:
>
>SELECT * FROM news
> ORDER BY newsDate DESC
>
>Any assistance gratefully Rx'd. I have DuBois's MySQL, it's obviously too
>good for me.
>Also tried various searches on MySQL.com, with little joy, aahh!
>
>Tom.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Comparison Issue
At 8:22 PM -0700 9/15/01, Carl Schrader wrote: >MYSQL > >I have a table with a date field. I have a query that needs to output >only if NOW() >The issue is this: >select * from thesites where ED3>NOW() > >ED3='1999-12-31 00:00:00' > >the above query will output when ED3 is as above. NOW()='2001-09-15 >18:10:44' > >??? Doesn't sound right. Show us the output from the query, and the output from "DESCRIBE thesites". -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: date comparison?
Steve Ruby writes: >Ignore my hasty and wrong response from moments ago. you should be fine >if date_resolved is a datetime type column if it is only a date you >will need to use date_format to convert your date_sub(now()) to a date, >otherwise the later returns datetime and cannot be compared to just date. I'll try that, but it was my understanding from the manual that the date functions were able to adapt between date, datetime, & timestamp automagically? -- Cindy [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: date comparison?
Cindy wrote: > > SELECT >Foster_Dogs.dog_name, >Foster_Dogs.dog_status, >Foster_Dogs.date_resolved, >Foster_Dogs.adopting_family, >Foster_Dogs.foster_paperwork, >Foster_Dogs.dog_id, >Adoption_Applications.firstname, >Adoption_Applications.lastname > FROM Foster_Dogs, Adoption_Applications > WHERE dog_status = "Adopted" AND adopting_family = app_id >AND (date_resolved > DATE_SUB(NOW(), INTERVAL 6 MONTH)) <<< bad line > ORDER BY dog_name > Ignore my hasty and wrong response from moments ago. you should be fine if date_resolved is a datetime type column if it is only a date you will need to use date_format to convert your date_sub(now()) to a date, otherwise the later returns datetime and cannot be compared to just date. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: date comparison?
Cindy wrote: > > > SELECT >Foster_Dogs.dog_name, >Foster_Dogs.dog_status, >Foster_Dogs.date_resolved, >Foster_Dogs.adopting_family, >Foster_Dogs.foster_paperwork, >Foster_Dogs.dog_id, >Adoption_Applications.firstname, >Adoption_Applications.lastname > FROM Foster_Dogs, Adoption_Applications > WHERE dog_status = "Adopted" AND adopting_family = app_id >AND (date_resolved > DATE_SUB(NOW(), INTERVAL 6 MONTH)) <<< bad line > ORDER BY dog_name > you can't compare time date fields directly like that, you need to convert them both to days or minutes or some common single number AND ( to_days(date_resolved) > to_days(date_sub(now(),interval 6 month)) ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
