CODEWITHSUNDEEP
mysql
Altiano Gerung
Altiano Gerung

Reputation: 854

Possibly Mysql 5.6.20 bug. Date - now()?

date_newOrd, now(), date_newOrd-now() AS `time`

this is my query. date_newOrd is type date. I try to calculate the time remaining for next arrival of order. I better show you the screenshot:

enter image description here

enter image description here

enter image description here

the result is doesn't make any sense. What am i supposed to do>

Upvotes: 1

Views: 81

Answers (2)

spencer7593
spencer7593

Reputation: 108420

When you do a subtraction, MySQL is going to evaluate NOW() in a numeric context, it returns a numeric value.

SELECT NOW()+0 

20150602135210.000000

So, your statement is doing a subtraction of numbers, not doing a DATE calculation.

Some possibilities:

You could convert the datetime values into unix_timestamp values, (UNIX_TIMESTMAP() function) and then do a subtraction of those to get a difference in integer seconds.

The DATEDIFF() function would get you a difference in integer days. (That operates only on the date portion, it ignores the time... so that probably doesn't give you the resolution you are looking for.)

The TIMESTAMPDIFF() and TIMEDIFF() functions are also available. (The TIMEDIFF functions returns a TIME datatype value; the maximum value of that datatype is 838:59:59, so that's limited to just under 35 days elapsed).

For example:

SELECT UNIX_TIMESTAMP('2015-06-03') - UNIX_TIMESTAMP(NOW()) AS secs

secs
------- 
  35856

Upvotes: 0

Marc B
Marc B

Reputation: 360732

You cannot subtract dates like that:

mysql> select '2015-06-01 18:20:03' - now();
+-------------------------------+
| '2015-06-01 18:20:03' - now() |
+-------------------------------+
|               -20150602073525 |
+-------------------------------+

While that may look (vaguely) like a date, it's really an integer, and can't be used for further date math without extra processing.

You have to use datediff() or timediff():

mysql> select timediff('2015-06-01 18:20:03', now()) as td, datediff('2015-06-01 18:20:03', now()) as dd;
+-----------+------+
| td        | dd   |
+-----------+------+
| -13:37:47 |   -1 |
+-----------+------+

note that datediff deals only with DATES, and timediff deals with datetime values.

Upvotes: 5

Related Questions