2

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'

The previous questions on Stack Overflow 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:
Converting date/time string to unix timestamp in MySQL

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']
informatik01
  • 16,038
  • 10
  • 74
  • 104
Sandy
  • 1,100
  • 10
  • 18
  • As you've discovered, it really makes things difficult when your timestamps are not stored properly. You should really save them in the default `YYYY-MM-DD HH:MM:SS` format then convert them to what you want after retrieving them. – EternalHour Oct 14 '19 at 23:44
  • Unfortunately,I only have access to the data and not the database. – Sandy Oct 14 '19 at 23:47

1 Answers1

2

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'
 )

Yields:

2017-10-18 10:11:03

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

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     |
GMB
  • 216,147
  • 25
  • 84
  • 135
  • many thanks for the solution. I have another quick question. If I wanted to change the code so that whenever the person, let's say Alpha appears, we calculate the time between attempts as: – Sandy Oct 15 '19 at 00:23
  • 1
    @Sadiaz: welcome! Well, what you are asking for is a different question, that would probably involve using window functions. You would better ask a new question... – GMB Oct 15 '19 at 00:28
  • https://stackoverflow.com/questions/58375139/time-difference-between-per-person-between-consecutive-rows/58375699?noredirect=1#comment103103626_58375699 This is the other aspect of the problem I want to address. @GMB, can you please have a look at the above. – Sandy Oct 15 '19 at 00:33