How to calculate duration using diff function

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mirku
    New Member
    • Nov 2009
    • 4
    #1

    How to calculate duration using diff function

    Hello!
    I have a table in a database where there is a column called DATE (datetime type)that logs the date/time of an event.The first index of the table it represent the start date/time and the last index of the table represent the final date/time.
    How can i make a query that returns the duration between the start date/time and final date/time?
    i tryed to do:
    Code:
    select DateDiff(hour,select DATE from mytable where INDEX=(select min(INDEX) from mytable),select DATE from mytable where INDEX=(select max(INDEX) from mytable)) from mytable
    but it is not working.
    Others solutions?
    Tags: None
    • ck9663
      Recognized Expert Specialist
      • Jun 2007
      • 2878
      #2
      Is there a key that identifies the event? Like an EVENTID or something? Or do you have a table per event?

      ~~ CK

        Comment

        • Mirku
          New Member
          • Nov 2009
          • 4
          #3
          yes,i have an ID column called INDEX (unique value) for each row.

            Comment

            • Oralloy
              Recognized Expert Contributor
              • Jun 2010
              • 988
              #4
              Would something like this work, or must the first "table" in the FROM clause be a table, not a query?:

              Code:
              SELECT DateDiff(maxValue.DATE, minValue.DATE) AS 'Difference'
  FROM (select min(INDEX) AS minimus, max(INDEX) AS maximus
          FROM mytable) AS range,
       mytable AS minValue,
       mytable AS maxValue
  WHERE (range.minimus = minValue.INDEX)
    AND (range.maximus = maxValue.INDEX)
              Luck!

                Comment

                • ck9663
                  Recognized Expert Specialist
                  • Jun 2007
                  • 2878
                  #5
                  If it's a unique value for each row, how can you identify which dates belong to what event? Could you post some sample data of your table? If you have multiple events on that table, kindly include them...

                  A 10 record sample will do.

                  ~~ CK

                    Comment

                    • helalreza
                      New Member
                      • Apr 2016
                      • 9
                      #6
                      Code:
                      <body>
<input type="time" step="1" id="startTimeInput"  name="start_time" value=""><br>     <!--   write as like       02:31:55   -->
<input  type="time" step="1" id="endTimeInput"  name="end_time" value=""><br>
<button id="calcBtn">Seconds</button><br>
<div id="result"></div>
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">
document.getElementById('calcBtn').onclick = function(){
	var startTime = document.getElementById('startTimeInput').value;
	var endTime = document.getElementById('endTimeInput').value;
  var result = getDeltaSeconds(startTime, endTime);
  document.getElementById('result').innerHTML = result + ' sec';
}
function getDeltaSeconds(startTime, endTime){
	var startTimeSeconds = getSeconds(startTime);
  var endTimeSeconds = getSeconds(endTime);
  return endTimeSeconds - startTimeSeconds;	
}
function getSeconds(timeString){
	var parts = timeString.split(' ');
  var time = parts[0];
  var ampm = parts[1];
  var timeParts = time.split(':');
  var seconds = Number(timeParts[2]) + Number(timeParts[1]*60) + Number(timeParts[0]*60*60);
  return ampm == 'AM' ? seconds : seconds + 12*60*60;
}
</script>
</body>

                        Comment

                        Previous template Next
                        Working...