计算MySQL中两个日期（String格式）之间的差异

My Client stores the visit time of a page in MySQL as varchar. He is taking the object of date class new date() from JS which is off the format

我的客户端将MySQL中页面的访问时间存储为varchar。他正在从JS中获取日期类new date()的对象,该格式不符合格式

Fri Nov 25 2016 12:09:34 GMT+0530 (India Standard Time)

2016年11月25日星期五12:09:34 GMT + 0530(印度标准时间)

I want to calculate the time difference of 2 variables as shown above. It's used for the analytics purpose, time spent in a page like.

我想计算2个变量的时差,如上所示。它用于分析目的,在页面中花费的时间。

I already tried this:

我已经尝试过了:

SELECT TIMESTAMPDIFF(SECOND, 'Fri Nov 25 2016 12:09:34 GMT+0530 (India Standard Time)', 
'Fri Nov 25 2016 12:20:34 GMT+0530 (India Standard Time)')

How can I Do it?

我该怎么做?

3 个解决方案

#1

You shoud really use mysql Date type. (Edit: Just saw in the comment why you're using varchar, so let's skip that ;) )

你真的使用mysql日期类型。 (编辑:刚刚在评论中看到你为什么要使用varchar,所以让我们跳过它;))

Anyway, you can try to cast your strings into dates using STR_TO_DATE() function

无论如何,您可以尝试使用STR_TO_DATE()函数将字符串转换为日期

SELECT TIMESTAMPDIFF(
    SECOND,
    STR_TO_DATE('Fri Nov 25 2016 12:09:34 GMT+0530 (India Standard Time)', '%a %b %d %Y %T'),
    STR_TO_DATE('Fri Nov 25 2016 12:20:34 GMT+0530 (India Standard Time)', '%a %b %d %Y %T')
)

#2

Try this :

尝试这个 :

SELECT TIMEDIFF('Nov 25 2016 12:09:34','Fri Nov 25 2016 12:20:34') as diff;

#3

$dat1 = "Fri Nov 26 2016 12:09:34 GMT+0530";
$dat2 = "Fri Nov 25 2016 12:20:34 GMT+0530";

$diff = abs(strtotime($dat2) - strtotime($dat1));

$years = floor($diff / (365*60*60*24));
$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));

printf("%d years, %d months, %d days\n", $years, $months, $days);

#1