Date comparison help
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
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
