Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Hi,

    I've created a web based booking system for a nightclub and they have requested an additional feature that tracks how long it takes them to call customers back.

    In my callback database table I have the fields:

    call_date - This is a Date type field
    call_time - This is a Time type field
    complete_time - This is a datetime type field

    So basically I want to get the difference in days and minutes from the time the call is supposed to be made and the time that it is actually made.

    I think what I need to do is to combine the call_date and call_time field to create the date and time it should be made.

    Then I think I need to convert this to a timestamp and the complete_time to a timestamp as well. Then calculate the difference and turn it back to date time format.

    Does that sound about right? I get really confused working with dates in PHP for some reason.

    Any pointers would be a big help

    Thanks

  2.  

  3. #2
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    I usually do the date/time things a bit differently.
    I don't use date or type type fields (columns).

    What I usually do is make them INT(11) integer 11 character and then use UNIX timestamp. Yes UNIX timestamp is 10 digits, but I make it 11 in case they change it soon. 2038 will be the year that UNIX exceeds 10 digits. The consortium may decide to change the length before then.

    Using UNIX timestamp allows the exact date AND time in one second resolution. You can have the timestamp written by your server using your server timezone. If you know the user's timezone, you can serve the date/time to them in THEIR timezone if you wish.

    There's nothing wrong with what you're doing, I just think it's easier to deal with the UNIX timestamp (just a personal preference). You can leave the columns you have and add two more columns to experiment with the 10 digit UNIX timestamp. You only need two columns ... one for call_timestamp and one for complete_timestamp.

    The timestamp you write in them is done at the moment you update the row.

    $now=time();

    That assigns the 10 digit timestamp to $now, based on your server timezone.

    You can then do math with the two integers. The difference is in seconds.
    The difference * 60 would be the difference in minutes.

    If you choose to use the current date field, your date format should be YYYY-MM-DD


  4. #3
    Junior Member 'Edward De Leau's Avatar
    Join Date
    Jan 2013
    Posts
    8
    Member #
    34693
    1. Another reason to store timestamp is that converting takes some time, (I got thrown off a shared hosting site when I actually did the conversion in a SQL query... : WHERE unix_timestamp(itime) >
    unix_timestamp(now()) - 30 ) (use ->getTimeStamp(); in PHP)

    2. I take NOW() from the Database (in the same SQL queryline in the select clause) since then I am sure it is the exact time used on the database server (since I am not sure if the other servers would all be running exact the same time) (have experienced differences) (I use it in the same query where I request the latest time).

    3. on http://php.net/manual/en/datetime.diff.php you will find some pieces of code you ask for: calculating the diff in years, months, days, hours, minutes

  5. #4
    Member
    Join Date
    Nov 2012
    Location
    Tampa. FL
    Posts
    59
    Member #
    33844
    Liked
    21 times
    To expand on what mlseim said, using unix timestamps versus SQL datetime is a matter of where you want to place the burden of the math operation (on your server or your database). I like to use the unix timestamps because it makes more sense for me to develop time conversions in PHP. If you set up a table field to store the time the call is requested and the time the call is actually made, your code to show how long it took would be something like this:
    PHP Code:
    $TimeSpent $call_request $call_time
    $days 
    floor($TimeSpent / (60 60 24));
    $remainder $TimeSpent % (60 60 24);
    $hours floor($remainder / (60 60));
    $remainder $remainder % (60 60);
    $minutes floor($remainder 60);
    $seconds $remainder 60;
    if(
    $days 0){
        echo 
    $days.' Days';
        }elseif(
    $days == && $hours == && $minutes == 0){
        echo 
    $seconds.' seconds';
        }elseif(
    $days == && $hours == 0){
            if(
    $minutes == 1){echo $minutes.' minute';
        }else{ echo 
    $minutes.' minutes';}
        }elseif(
    $days == && $hours >=1){
        if(
    $hours ==1){echo $hours.' hour';
        }else{ echo 
    $hours.' hours';}
        } 
    I haven't tested this code but it should give you a foundation to work with.
    Freelance Web Developer

  6. #5
    Member
    Join Date
    Nov 2012
    Location
    Tampa. FL
    Posts
    59
    Member #
    33844
    Liked
    21 times
    Hm, the very first line of my code is wrong and should be call_time - call_request.
    Like I said, the code isn't tested and it's a good idea to proofread before clicking on "Post reply"
    Freelance Web Developer


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:23 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com