Average time in seconds

Question

Average time in seconds

Asked 7 years, 1 month ago

Viewed 353 times

0

Hello, in my select I need to find the average service time (TMA) between two columns BIGINT format being them:

Date: Dtbegin (start) and Dtend (end)

They are of the format "dd/mm/yyyyyy hh:mm:ss", I want to know in seconds how long each service lasted, I can not touch anything of the bank want only in the view.

Create Table

Create Table Interaction 
    IDInteraction   bigint(20),
  DtBegin   datetime,
    DtEnd   datetime,
  IDuser    int(11),
  IDSupervisor  int(11),
  OperatorName  varchar(200),
  SupervisorName    varchar(200),
  IDSession bigint(20),
  IDMailingItem bigint(20),
  IDContact bigint(20),
  Notes longtext,
  IDResult  int(11),
  ResultDescription varchar(100),
  IDCampaign    int(11),
  CampaignDescription   varchar(200),
    IsAutomatic bit(1),
    IDChannel   int(11),    
    IsCallback  bit(1)`

Insert

INSERT INTO Interaction (417, 
'16/05/2016 20:00:40', 
'16/05/2016 20:01:18', 
1, 
2, 
'Fulano BR',
'SUPERVISOR 001',
 null,
 null,
4, 
 null,
2, 
'AfterCallEnd',
1, 
'Campanha SMS', 
1, 
9,
null);

Do you have any example of what this table is like with data and how it would be a result for example?

– novic

2017/05/24 at 17:46
@Virgilionovic added an image of my select the result would be the difference of seconds between the beginning and the end, rotates around 1.04, 0.03 and so on

– Kaleb Gabriel

2017/05/24 at 17:52
BIGINT columns in "dd/mm/yyyy hh:mm:ss" format? Columns are not DATETIME or VARCHAR?

– abfurlan

2017/05/24 at 18:10
Kaleb made an issue, take a look at the answer and if that’s it... !!! the average in seconds?

– novic

2017/05/24 at 18:12
@abfurlan are even bigint :/

– Kaleb Gabriel

2017/05/24 at 18:19
Kaled I asked you a question what would be the result of a line? and another by TIME_TO_SEC will not roll like this!

– novic

2017/05/24 at 18:36
The fields were not BIGINT? By the creation code of your table are datetime! :/

– abfurlan

2017/05/25 at 00:07
Managed to solve, I also made an issue take a look!

– novic

2017/05/25 at 12:35
@Virgilionovic I tried your code without the "from_unixtime" and it worked although I had already tried earlier kk thank you very much kk

– Kaleb Gabriel

2017/05/26 at 13:14

Show 4 more comments

3 answers

0

It would be a conversion to Datetime with str_to_date, then with unix_timestamp() for an integer value and a division by 2 which would be the average of the result:

SELECT dtEnd, dtEnd, 
       ((UNIX_TIMESTAMP(dtEnd) - UNIX_TIMESTAMP(dtBegin)) / 2) media
from tempo tempo

The average in this case has its output in seconds like 30 seconds, 19 seconds and so on.

_References:

It did not work, I believe it is by TIMESTAMP, because the fields are bigint, I got something more or less with TIME_TO_SEC, it returns the total duration in seconds, but in some cases it Buga and shows Null

– Kaleb Gabriel

2017/05/24 at 18:26
Know what you were not clear for example: what is the expected result for example: '16/05/2016 20:00:40', '16/05/2016 20:01:18' what is the expected result? @Kalebgabriel ???

– novic

2017/05/24 at 18:28
For example 18/05/2016 10:58:34,18/05/2016 11:16:40 = 3486

– Kaleb Gabriel

2017/05/24 at 18:42
Just to find out where you got that amount 3486 how it arrived at this value @Kalebgabriel?

– novic

2017/05/24 at 18:52
Look at my question up there I changed the image, penultimate line of a code I made of TEST using TIME_TO_SEC

– Kaleb Gabriel

2017/05/24 at 18:53
@Kalebgabriel’s wrong this value!!! see from 10:58 until 11:16 has 18 minutes (let’s do the values in minutes that also enters the scenario) then multiplying by 60 would be 1080 the difference from one to the other and the average 540 seconds. Or Not?

– novic

2017/05/24 at 18:56
Yes that same 1080, this wrong kkk, as you can see some are right, others null and some wrong :/, hard to work with Bigint

– Kaleb Gabriel

2017/05/24 at 18:59
I don’t understand BIGINT, it doesn’t matter. I’ll edit mine doing what I told you then test it! Cara can test the proposed way in this answer

– novic

2017/05/24 at 19:07
It is but nothing they are suggesting me this returning value only TIME_TO_SEC gave result even if wrong :/

– Kaleb Gabriel

2017/05/24 at 19:10
I don’t understand you, man, I did the math I gave you the code that does it and you say it’s not right, what do you mean? the code I gave you didn’t work?

– novic

2017/05/24 at 19:12
I didn’t say it was wrong, but it didn’t work, it only returns null fields, all empty.

– Kaleb Gabriel

2017/05/24 at 19:15
passes SQL here for me to see what you’re doing? with my example?

– novic

2017/05/24 at 19:16
as follows: SELECT
 DtBegin,
 DtEnd,
 IDInteraction,
 DATE_FORMAT(DtBegin, '%m-%Y') AS Mes_Ano,
 TIME_TO_SEC(DtEnd-DtBegin) as TESTE,
 ((UNIX_TIMESTAMP(STR_TO_DATE(dtEnd,'%d/%m/%Y %H:%i:%s')) - 
 UNIX_TIMESTAMP(STR_TO_DATE(dtBegin,'%d/%m/%Y %H:%i:%s'))) / 2) media
FROM Interaction
LIMIT 100;

– Kaleb Gabriel

2017/05/24 at 19:18
I need a copy of your table! because the command is correct! Have the layout of the table? and some data !

– novic

2017/05/24 at 19:19
I will post on the question

– Kaleb Gabriel

2017/05/24 at 19:43
@Kalebgabriel if your date is already a datetime you don’t need to use str_to_date, check now with this edition.

– novic

2017/05/24 at 20:15

Show 11 more comments

Browser other questions tagged mysql date bigint

You are not signed in. Login or sign up in order to post.

by Renato Lazaro • **309** points · Answer 1 · 2017-07-20T20:14:46+00:00

Buddy! You’re not getting the hang of it TIMESTAM why your data type is DATETIME. That is, your query is searching for a type of data and in the database is another.

by abfurlan • **10,570** points · Answer 2 · 2017-05-24T18:18:35+00:00

0

Use the function FROM_UNIXTIME to convert to date and to get the difference the function TIMESTAMPDIFF.

SELECT 
DtBegin,
DtEnd,
TIMESTAMPDIFF(SECOND,FROM_UNIXTIME(DtBegin),FROM_UNIXTIME(DtEnd)) AS TempoSegundos
from Interaction

SQLFIDDLE

They are BIGINT even, if I would not have used the TIMESTAMPDIFF, and the Date_format is right, I was testing the %s for seconds when I took the print, I use "%m-%y"

– Kaleb Gabriel

2017/05/24 at 18:22
@Is that what Kalebgabriel is? http://sqlfiddle.com/#! 9/da653/2

– abfurlan

2017/05/24 at 18:29
Works but not in my case, only returns null values, I think it is the format of the field TIMESTAMP does not work with BIGINT and I can not convert, it is client database.

– Kaleb Gabriel

2017/05/24 at 18:33
sorry now that I saw that you created the fields as Bigint, but I search and returns only null values

– Kaleb Gabriel

2017/05/24 at 18:38
@Kalebgabriel strange, pq in Fiddle worked by converting BIGINT

– abfurlan

2017/05/24 at 18:39
Is it not the format of the data you entered? I do not know how to enter date with time, but if it were only the date would do ('dd/mm/yyyy',)

– Kaleb Gabriel

2017/05/24 at 18:45
See here the example with the same data that returned NULL after your image. http://sqlfiddle.com/#! 9/95583/1.

– abfurlan

2017/05/24 at 18:59
Right, but it only worked because you used it unix_timestamp(str_to_date in Insert is not

– Kaleb Gabriel

2017/05/24 at 19:09
@Kalebgabriel Yes more to record in BIGINT you need to convert the date to timestamp, otherwise it would not be possible to save a date in a bigint field

– abfurlan

2017/05/24 at 19:19

Show 4 more comments