son Posted May 14, 2016 Share Posted May 14, 2016 (edited) 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 May 14, 2016 by son Link to comment Share on other sites More sharing options...
dsonesuk Posted May 14, 2016 Share Posted May 14, 2016 (edited) 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 May 14, 2016 by dsonesuk 1 Link to comment Share on other sites More sharing options...
son Posted May 15, 2016 Author Share Posted May 15, 2016 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 More sharing options...
son Posted May 15, 2016 Author Share Posted May 15, 2016 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 More sharing options...
dsonesuk Posted May 15, 2016 Share Posted May 15, 2016 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. 1 Link to comment Share on other sites More sharing options...
son Posted May 17, 2016 Author Share Posted May 17, 2016 Thanks for new reply. Will leave it as it is as there will never be an instance where the start and end would be on separate days... Son Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now