Jump to content
son

TIMEDIFF returns empty set

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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...