CODEWITHSUNDEEP
sqlmysql
omg
omg

Reputation: 140122

How to calculate interval between datetime in MySQL?

For example,how to calculate the interval between these two datetime:

2009-09-18 00:00:00

2009-10-17 00:00:00

EDIT

I mean to get the interval in the format of year-month-day hour:min:seconds

Upvotes: 12

Views: 26705

Answers (4)

Guohua
Guohua

Reputation: 191

Or use timestampdiff() like this:

timestampdiff(microsecond,yourtime,now())

the first parameter is mysql datetime unit, can be "microsecond, second, minute, hour, day, year", and this function means the 3rd parameter datetime minus the 2nd parameter datetime.

mysql> select timestampdiff(second, "2009-09-18 00:00:00", "2009-10-17 00:00:00");
+---------------------------------------------------------------------+
| timestampdiff(second, "2009-09-18 00:00:00", "2009-10-17 00:00:00") |
+---------------------------------------------------------------------+
|                                                             2505600 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 6

Pascal MARTIN
Pascal MARTIN

Reputation: 401182

What about using datediff :

mysql> select abs(datediff('2009-09-18 00:00:00', '2009-10-17 00:00:00'));
+-------------------------------------------------------------+
| abs(datediff('2009-09-18 00:00:00', '2009-10-17 00:00:00')) |
+-------------------------------------------------------------+
|                                                          29 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Quoting the manual :

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions.
Only the date parts of the values are used in the calculation.

Upvotes: 14

Janis Veinbergs
Janis Veinbergs

Reputation: 6988

You may try out DATEDIFF or TIMEDIFF

Upvotes: 5

Kevin Peterson
Kevin Peterson

Reputation: 7297

You can just subtract datetimes and it will return the value:

select (now() - interval 2 day) - (now() - interval 5 day);

The result isn't a datetime (it's some decimal coded date -- 3000000 for three days in this case), but it isn't correct to say consider a datetime and an interval as the same datatype.

Upvotes: 1

Related Questions