What is the way to store "Time only" values with php?

Question

I have a Mysql table which has a column of type TIME.

When I retrieve the values of this TIME column with PHP, how should I store it in php variable for later use in my php classes?

• Should I keep it as string : "12:45:23"?
• Should I convert it to a fraction of a day : 0.25 is 06:00:00 ?
• Should I convert it to a number of seconds : 15152 is 04:12:32 ?
• other?

I would like to use the standard php 5 way of dealing with time.

Thanks!

---

UPDATE

My data is stored in a mysql table of type "TIME" and not "DATETIME".

I just want to deal with time (a number of hours, minutes and seconds). the date is NOT relevant in my case, so unix timestamp or date/time objects seems to not be appropriate.

Solution 1

Best and most common way is number of seconds as all php functions working in this format. Also see: "Unix timestamp"

Solution 2

Use ISO 8601, which is hh:mm:ss for better understanding, and high readbility

Solution 3

Time is an interval, so you can store it as seconds.

The only thing is that seconds are not an object, and you can't give it handy functions, neither does it maintain the specific number of minutes provided.

For example say you use a time like 45:61:59, after converting it to seconds you will not be able to convert it back to this format.

PHP solves these problems using DateInterval

41:61:45 would be stored as

$interval = new DateInterval('PT41H61M45S');

The problem is that DateInterval is not as easy to use or useful as it could be, so why not create your own class?

Here, I created one just for you (or anyone looking)
(I know I'm late, but this question still pops up high in SERPs)

class Time extends DateInterval {
    const SEC_IN_MINUTE = 60;
    const SEC_IN_HOUR = 3600;
    const SEC_IN_DAY = 86400;
    const SEC_IN_YEAR = 31536000;

    private $interval, $time;

    // takes an $time input of 48:23:12 or seconds or DateInterval spec
    public function __construct($time){
        // support interval spec format
        if (strpos($time, 'P') === 0) {
            parent::__construct($time);

        } else

        // support seconds
        if (is_int($time)) {
            parent::__construct('PT' . $time . 'S');

        } else
        // support 00:00:00 format
        if (preg_match('/([0-9]+):([0-9]+):([0-9]+)/', $time, $match)) {
            $this->time = $time;
            $this->h = (int)$match[1];
            $this->i = (int)$match[2];
            $this->s = (int)$match[3];
            parent::__construct('PT' . $this->h . 'H' . $this->i . 'M' . $this->s . 'S');
        // support human format
        // e.g. "5 minutes"
        } elseif(strtotime($time)) {
            $dt = new DateTime('@0', new DateTimeZone('UTC'));
            $dt->modify($time);
            parent::__construct('PT' . $dt->getTimeStamp() . 'S');
        }else {
            throw new Exception('"' . $time . '" is an unknown time format');
        }
    }
    public function toSeconds(){
        $zero = new DateTime('@0'); // zero date
        return $zero->add($this)->getTimestamp();
    }
    public function toMinutes(){
        return $this->toSeconds() / 60;
    }
    public function toHours(){
        return $this->toMinutes() / 60;
    }
    public function toDays(){
        return $this->toHours() / 24;
    }
    public function toYears(){
        return $this->toHours() / 365;
    }

    // recalculate carry over points
    // this is to convert a time like 22:98:74 to 23:39:14
    // warning: intervals don't know how long a month is, and as such can't support them
    public function recalculate()
    {
        $zero = new DateTime('@0'); // zero date
        $to = clone $zero;
        $to = $to->add($this);
        $diff = $zero->diff($to);
        foreach ($diff as $k => $v) $this->$k = $v;

        $dt = new DateTime('@0'); // zero date
        $dt->add(new self('P'.$this->m.'M'));
        $seconds = $dt->getTimeStamp();

        // add what was months to days
        $this->m = 0;
        $this->d += $seconds / 86400;

        // move excess days to years 
        if($this->d > 365){
            $this->y = floor($this->d / 365);
            $this->d = $this->d % 365;
        }

        return $this;
    }

    // remove all whole chunks of interval from seconds and return the amount of chunks
    protected function popTimeSpan(&$seconds, $interval){
        $timespan = $seconds / $interval;
        $timespan = floor($timespan);
        $seconds -= $timespan * $interval;
        return $timespan;
    }

    // a special version of format() that will maintain the full interval in the formatted string
    // warning: it does not support %m or %M, which will always be converted to 0
    public function reformat($format){

        $seconds = $this->toSeconds();


        if(strpos($format, '%y')!==false || strpos($format, '%Y')!==false){
            $timespan = self::popTimeSpan($seconds, self::SEC_IN_YEAR);
            $format = str_replace('%y', $timespan, $format);
            $format = str_replace('%Y', str_pad($timespan,4,'0',STR_PAD_LEFT), $format);
        }
        if(strpos($format, '%m')!==false || strpos($format, '%M')!==false){
            $format = str_replace('%m', '0', $format);
            $format = str_replace('%M', '00', $format);
        }
        if(strpos($format, '%d')!==false || strpos($format, '%D')!==false){
            $timespan = self::popTimeSpan($seconds, self::SEC_IN_DAY);
            $format = str_replace('%d', $timespan, $format);
            $format = str_replace('%D', str_pad($timespan,2,'0',STR_PAD_LEFT), $format);
        }
        if(strpos($format, '%h')!==false || strpos($format, '%H')!==false){
            $timespan = self::popTimeSpan($seconds, self::SEC_IN_HOUR);
            $format = str_replace('%h', $timespan, $format);
            $format = str_replace('%H', str_pad($timespan,2,'0',STR_PAD_LEFT), $format);
        }
        if(strpos($format, '%i')!==false || strpos($format, '%I')!==false){
            $timespan = self::popTimeSpan($seconds, self::SEC_IN_MINUTE);
            $format = str_replace('%i', $timespan, $format);
            $format = str_replace('%I', str_pad($timespan,2,'0',STR_PAD_LEFT), $format);
        }
        if(strpos($format, '%s')!==false || strpos($format, '%S')!==false){
            $timespan = floor($seconds);
            $format = str_replace('%s', $timespan, $format);
            $format = str_replace('%S', str_pad($timespan,2,'0',STR_PAD_LEFT), $format);
        }

        return $this->format($format);
    }
}

$time = new Time('23:10:15');
echo 'Seconds: '.$time->s.'<br>'; // 15
echo 'toSeconds: '.$time->toSeconds().'<br>'; // 83415

// lets try with times that are above 24 hour
$time = new Time('48:10:16');
echo 'Seconds: '.$time->s.'<br>'; // 16
echo 'toSeconds: '.$time->toSeconds().'<br>'; // 173416

// lets try with times that are messy
$time = new Time('23:98:75');
echo 'Seconds: '.$time->s.'<br>'; // 75
echo 'toSeconds: '.$time->toSeconds().'<br>'; // 88755
echo 'Formatted: '.$time->format('%Y-%d %H:%i:%s').'<br>'; // 00-0 23:98:75
echo 'Recalculated: '.$time->reformat('%Y-%d %H:%i:%s').'<br>'; // 0000-1 00:39:15

// lets try with months!!
$time = new Time('13044:98:74');
echo 'Seconds: '.$time->s.'<br>'; // 74
echo 'toSeconds: '.$time->toSeconds().'<br>'; // 46964354
echo 'Formatted: '.$time->format('%Y-%d %H:%i:%s').'<br>'; //  00-0 13044:98:74
echo 'Recalculated: '.$time->reformat('%Y-%d %H:%i:%s').'<br>'; // 0001-178 13:39:14


// ok, now with years
$time = new Time('87630:98:74'); // 10 years, 30 hours 98 minutes  and 74 seconds
echo 'Time: 87630:98:74<br>';
echo 'Formatted at year level: '.$time->format('%Y-%d %H:%i:%s').'<br>'; // 00-0 87630:98:74
echo 'Formatted at day level: '.$time->format('%d %H:%i:%s').'<br>'; // 0 87630:98:74
echo 'Formatted at hour level: '.$time->format('%H:%i:%s').'<br>'; // 87630:98:74
echo 'Formatted at minute level: '.$time->format('%i:%s').'<br>'; // 98:74
echo 'Formatted at second level: '.$time->format('%s seconds').'<br>'; // 74 seconds
echo 'Formatted at year + second level: '.$time->format('%y years %s seconds').'<br>'; // 0 years 74 seconds

echo 'Recalculated at year level: '.$time->reformat('%Y-%d %H:%i:%s').'<br>'; // 0010-1 07:39:14
echo 'Recalculated at day level: '.$time->reformat('%d %H:%i:%s').'<br>'; // 3651 07:39:14
echo 'Recalculated at hour level: '.$time->reformat('%H:%i:%s').'<br>'; // 87631:39:14
echo 'Recalculated at minute level: '.$time->reformat('%i:%s').'<br>'; // 5257899:14
echo 'Recalculated at second level: '.$time->reformat('%s seconds').'<br>'; // 315473954 seconds
echo 'Recalculated at year + second level: '.$time->reformat('%y years %s seconds').'<br>'; // 10 years 113954 seconds

echo 'Test %a: '.$time->reformat('%a').'<br>'; // (unknown)
echo 'Test %R: '.$time->reformat('%r').'<br>'; // 
echo 'Test %r: '.$time->reformat('%R').'<br>'; // +

Now you can take that MySQL time and easily do whatever you want with it.

$time = new Time('864:23:59');
$seconds = $time->toSeconds();
$formatted_time = $time->reformat('%d days %h:%i:%s');

Feel free to edit this and make it shorter or better

Solution 4

The unix timestamp is probably the best for most common use, since it is also compatible with all the PHP date/time function and is smaller in size than a string.

Solution 5

In general you should always store a date/time value in the database's native date/time formats. This lets you use the DB's native date/time manipulation functions in queries. Otherwise it's just a chunk of text and you're stuck with using string manipulation, or having to select each time field and manipulating it in your app.

View more solutions

Comments

[Amr Mostafa]

My goodness, with the exception of one answer, all other answers are answering a different question

[Marc B]

Then you can do SELECT unix_timestamp(datetimefield) which produces a simple signed integer value, which can be put into PHP's date/time functions directly.

[Marc B]

There's the time_to_sec() mysql function for that as well.

[Roy de Jong]

A Unix timestamp also contains information about the date, and doesn't seem like an appropriate solution here.