日期/时间或时间戳的mySQL字符串 [英] mySQL String to date/time or timestamp

问题描述

在我的数据集中，任务的开始和结束时间以字符串形式给出.字符串包含:

In my data set, the start and end time for a task is given as a string. The string contains:

'Day, Date Month YYYY HH:MM:SS GMT'
'Wed, 18   Oct   2017 10:11:03 GMT'

以前关于stackoverflow的问题没有这种格式的数据，我一直在努力将其转换为日期/时间或时间戳.任何建议，将不胜感激！

The previous questions on stackoverflow do not have data in this format and I have been struggling how to convert it into date/time or timestamp. Any advice on this would be greatly appreciated!

这篇文章很相关，但仍然不能满足我的需要，因为两种情况下字符串的格式都不同.

This post was quite relevant but still does not meet my needs, as the format of the string is different in both cases.

在MySQL中将日期/时间字符串转换为Unix时间戳

总的来说，我想实现一个变量'time_on_task'，该变量将每个人的start_time和end_time之间的差额计算在内.因此，对于以下数据:

Overall, I want to achieve a variable 'time_on_task' which takes the difference per person between their start_time and end_time. Thus, for the following data:

Person  TaskID   Start_time                      End_time
Alpha   1       'Wed, 18 Oct 2017 10:10:03 GMT' 'Wed. 18 Oct 2017 10:10:36 GMT'
Alpha   2       'Wed, 18 Oct 2017 10:11:16 GMT' 'Wed, 18 Oct 2017 10:11:28 GMT'
Beta    1       'Wed, 18 Oct 2017 10:12:03 GMT' 'Wed, 18 Oct 2017 10:12:49 GMT'
Alpha   3       'Wed, 18 Oct 2017 10:12:03 GMT' 'Wed, 18 Oct 2017 10:13:13 GMT'
Gamma   1       'Fri, 27 Oct 2017 22:57:12 GMT' 'Sat, 28 Oct 2017 02:00:54 GMT'
Beta    2       'Wed, 18 Oct 2017 10:13:40 GMT' 'Wed, 18 Oct 2017 10:14:03 GMT' 

所需的输出将是这样的:

The required output would be something like this:

Person  TaskID Time_on_task
Alpha   1      0:00:33   #['Wed, 18 Oct 2017 10:10:36 GMT' - 'Wed, 18 Oct 2017 10:10:03 GMT']
Alpha   2      0:00:12   #['Wed, 18 Oct 2017 10:11:28 GMT' - 'Wed, 18 Oct 2017 10:11:16 GMT']
Beta    1      0:00:46   #['Wed, 18 Oct 2017 10:12:49 GMT' - 'Wed, 18 Oct 2017 10:12:03 GMT']
Alpha   3      0:01:10   #['Sat, 18 Nov 2017 10:13:13 GMT' - 'Sat, 18 Nov 2017 10:12:03 GMT']
Gamma   1      3:03:42   #['Sat, 28 Oct 2017 02:00:54 GMT' - 'Fri, 27 Oct 2017 22:57:12 GMT']
Beta    2      0:00:23   #['Wed, 18 Oct 2017 10:14:03 GMT' - 'Wed, 18 Oct 2017 10:13:40 GMT']

推荐答案

您需要 STR_TO_DATE()才能将字符串转换为日期.考虑:

You need STR_TO_DATE() to convert the string to a date. Consider:

select str_to_date(
      'Wed, 18   Oct   2017 10:11:03 GMT',
      '%a, %d %b %Y %T GMT'
 )

收益:

2017-10-18 10:11:03

将字符串转换为日期后，可以使用 timestampdiff()计算它们之间的差，然后使用 sec_to_time()将结果返回到某个时间.:

Once you strings are converted to dates, you can use timestampdiff() to compute the difference between them, and turn the result back to a time using sec_to_time():

select
    person,
    taskID,
    sec_to_time(
            timestampdiff(
            second, 
            str_to_date(Start_time, '%a, %d %b %Y %T GMT'),
            str_to_date(End_time, '%a, %d %b %Y %T GMT')
        )
    ) time_on_task
from mytable

DB Fiddlde上的演示 :

Demo on DB Fiddlde:

| person | taskID | time_on_task |
| ------ | ------ | ------------ |
| Alpha  | 1      | 00:00:33     |
| Alpha  | 2      | 00:00:12     |
| Beta   | 1      | 00:00:46     |
| Alpha  | 3      | 00:01:10     |
| Gamma  | 1      | 03:03:42     |
| Beta   | 2      | 00:00:23     |

这篇关于日期/时间或时间戳的mySQL字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！