Average time in seconds

Asked

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.

inserir a descrição da imagem aqui

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);
mysql date bigint

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

  • @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

  • BIGINT columns in "dd/mm/yyyy hh:mm:ss" format? Columns are not DATETIME or VARCHAR?

  • Kaleb made an issue, take a look at the answer and if that’s it... !!! the average in seconds?

  • @abfurlan are even bigint :/

  • 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!

  • The fields were not BIGINT? By the creation code of your table are datetime! :/

  • Managed to solve, I also made an issue take a look!

  • @Virgilionovic I tried your code without the "from_unixtime" and it worked although I had already tried earlier kk thank you very much kk

Show 4 more comments

3 answers

0

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.

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

  • 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 ???

  • For example 18/05/2016 10:58:34,18/05/2016 11:16:40 = 3486

  • Just to find out where you got that amount 3486 how it arrived at this value @Kalebgabriel?

  • Look at my question up there I changed the image, penultimate line of a code I made of TEST using TIME_TO_SEC

  • @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?

  • Yes that same 1080, this wrong kkk, as you can see some are right, others null and some wrong :/, hard to work with Bigint

  • 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

  • It is but nothing they are suggesting me this returning value only TIME_TO_SEC gave result even if wrong :/

  • 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?

  • I didn’t say it was wrong, but it didn’t work, it only returns null fields, all empty.

  • passes SQL here for me to see what you’re doing? with my example?

  • 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;

  • I need a copy of your table! because the command is correct! Have the layout of the table? and some data !

  • I will post on the question

  • @Kalebgabriel if your date is already a datetime you don’t need to use str_to_date, check now with this edition.

Show 11 more comments

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"

  • @Is that what Kalebgabriel is? http://sqlfiddle.com/#! 9/da653/2

  • 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.

  • sorry now that I saw that you created the fields as Bigint, but I search and returns only null values

  • @Kalebgabriel strange, pq in Fiddle worked by converting BIGINT

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

  • See here the example with the same data that returned NULL after your image. http://sqlfiddle.com/#! 9/95583/1.

  • Right, but it only worked because you used it unix_timestamp(str_to_date in Insert is not

  • @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

Show 4 more comments

Browser other questions tagged mysql date bigint

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