CODEWITHSUNDEEP
mysql
Americo
Americo

Reputation: 919

MySQL - Truncating Date/Time and Subtracting from Each Other

I have the following query which subtracts the original purchase time from the expires time to come up with a number of days:

select date(expires_dtime) - date(original_purchase_dtime)
from sl_player_subscription
where player_uuid = '9c61411c-54b4-45bd-8d07-264b1c2e4249'

player_uuid that = '9c61411c-54b4-45bd-8d07-264b1c2e4249' has an original purchase time of 2013-03-06 11:36:46 and an expire time of 2013-04-05 12:36:46.

I want the output to read "30" which is the number of days between the purchase time and the expire time, but instead the output is 99. Any suggestions?

Upvotes: 0

Views: 119

Answers (1)

Kermit
Kermit

Reputation: 34055

You can use DATEDIFF:

SELECT DATEDIFF('2013-04-05 12:36:46', '2013-03-06 11:36:46')

Result

30

Using your columns:

SELECT DATEDIFF(expires_dtime, original_purchase_dtime)

See the demo

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.

Documentation: Date and Time functions

Upvotes: 4

Related Questions