Jump to content

TIMEDIFF returns empty set


son

Recommended Posts

Have table1 to hold date as DATE and a start and end time as TIME as I need to calculate difference between the start and end time for various dates (which all hold varying times).

 

However, using TIMEDIFF on my columns 'starter' and 'ender' which hold the TIME components simply returns an empty set

SELECT TIMEDIFF('starter','ender') FROM table1;

 

Do you have any ideas? Have posted below the formatting used in columns just in case the issue lies there...

 

Son

 

2010-09-09 09:30:00

11:00:00

Edited by son
Link to comment
Share on other sites

Apply it to an alias, and remove quotes

 

"SELECT TIMEDIFF(starter,ender) AS timediff FROM table1";

 

Then use 'timediff' to show result typical example would be as you loop through each loop

 

echo $row[timediff']

 

Are you just entering start and end time? You need to enter date as well, else the time values would be to current date.

Edited by dsonesuk
  • Like 1
Link to comment
Share on other sites

Query works now, thanks:-) However, had to change the time fields in query though as otherwise negative difference coming up. I want to add up several time differences to a total of used slots. Query now:

"SELECT TIMEDIFF(ender, starter) AS timediff FROM table1";

which works.

 

What you said regarding date: I have a separate field for DATE and then the two TIME columns. But now you got me thinking and I wonder if it was an idea to loose the DATE column and just add DATE bit to the two TIME columns. Would that make sense? Guess it would enable me to loose one column and I could still extract date and time parts separately. Or, are there other reasons to do this?

 

Son

Link to comment
Share on other sites

Actually, had a play around with this and seems I cannot undo the automatical insertion of current timestamp when I make changes made to a row. This is not useful as the timestamp will never be the current one and would only mess up my data, so will leave it in separate fields as it is...

 

Son

Link to comment
Share on other sites

You can see the difference with and without date value with

            //SQL
               $query_producttest = "SELECT date,  starter, TIMEDIFF(starter, ender) AS timediff FROM table1"

            echo '<hr>';
            echo date("Y/m/d H:i:s", strtotime($row['date'] . ' ' . $row['starter'])); // to date set in date field
            echo '<hr>';
            echo date("Y/m/d H:i:s", strtotime($row['starter'])); // to current date

It probably won't be a problem if times on same day, but if the time period extends over two different day periods, then just using single date field with time fields is not going to give a correct result.

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...