Tools Blog Learn Quizzes Smile API Log In / Sign Up
Tools Blog Learn Quizzes Smile API Log In / Sign Up
« Return to the tutorials list
We have updated the website and our policies to make sure your privacy rights and security are respected.
Click here to learn more about the way our website handles your data.

Remove this message.

The Basics of Time and Date in PHP and MySQL

Difficulty: 20 / 50 Tweet
date-time-php-mysql

In this tutorial we will go through doing basic operations with Date/Time functions in PHP and MySQL and learn how to keep time zones in sync inside an application. We'll start by understanding why application level synchronization between the MySQL and PHP time zones is necessary.

Syncing MySQL and PHP time zones

Assume the following scenario:

  1. PHP runs on EST

    Set this via date_default_timezone_set('America/New_York'); and/or use date_default_timezone_get() to test.

  2. MySQL runs on UTC - 4 hours later than EST

    To get the current setting for MySQL execute this query: SELECT @@global.time_zone, @@session.time_zone;.

    If the query returns "SYSTEM", then go to your terminal and run date to see the Linux system time zone. If the query returns an offset, then the time zone is represented by the hour difference between UTC and whatever is set in MySQL (Ex: -4:00 is Eastern Time/EST).

  3. Then insert a UNIX Time Stamp in a MySQL table from PHP

    Code would be something similar to this: INSERT INTO demo (`id`,`when`) VALUES (NULL, '.time().');

  4. Display the inserted result as a formated date using MySQL to format the time stamp. Your code will be similar to this: SELECT FROM_UNIXTIME(`when`, '%Y-%m-%d %H.%i.%s') as `when` FROM demo;

If everything happened according to the plan, the time you are displaying is 4 hours late, which would really suck if that time was an important meeting or a job interview :).

Of course, the same problem would occur if you would insert a time using NOW() in MySQL and then display it using a PHP function.

To avoid such problems always make sure you are keeping the two time zones in sync when "bootstrapping" your application. For MySQL you should set the offset right after setting up the connection, while for PHP it's even easier:

        
            <?php
            date_default_timezone_set('America/New_York'); //setup the PHP time zone
            
            //connection details for mysql
            $default_tz = '-4:00'; //eastern time offset
            $q = "SET time_zone = '$default_tz'";
            //execute query and your session time zone will remain on eastern time
        
    

The Interval

Time and date operations are a little tricky until you get the hang of it so lets take a look at time intervals in PHP and MySQL and see how those are used to add and subtract dates and time.

The most common approach when dealing with time comparisons and intervals is to convert everything to UNIX time stamps and compare the numbers. Let's see how that is done with a basic example:

        
            //in php
            <?php
                $now_obj = new DateTime();
                $now = $now_obj->getTimestamp();
                $lastweek_obj = new DateTime();
                $lastweek = $lastweek_obj->getTimestamp() - 7*24*3600;
                $anunknowntime_obj = new DateTime();
                $anunknowntime = $anunknowntime_obj->getTimestamp() - rand(1,10)*24*3600;
                if($anunknowntime>$lastweek and $anunknowntime<$now) {
                    echo $anunknowntime . " is between last week and now";
                }
                else {
                    echo $anunknowntime . " is not between last week and now";
                }
            ?>
        
    

For MySQL we will try to fetch articles that have been published between some random time in the last week and now from an imaginary table.

        
        //in mysql
        //don't get confused about ROUND((RAND() * (7-1))+1) * 24 * 3600
        //it returns a random integer between 1 and 7
        SELECT `title` 
        FROM `articles`
            WHERE 
        UNIX_TIMESTAMP(`published_date`)
            BETWEEN
            UNIX_TIMESTAMP(NOW()) - ROUND((RAND() * (7-1))+1) * 24 * 3600 
                AND
            UNIX_TIMESTAMP(NOW());
        
    

The above works very well and you have the advantage that you're always dealing with numbers which makes things easier to debug. However, I believe that a cleaner approach is to use intervals both in MySQL and PHP instead of doing all those transformations. Here's the same code that we have above, but created using intervals in PHP and then evaluated using comparison operators directly on the resulting objects - Reference.

        
            <?php
                $now_obj = new DateTime();
                $lastweek_obj = new DateTime();
                $lastweek_obj = $lastweek_obj->sub(new DateInterval('P7D'));

                $anunknowntime = new DateTime();
                $anunknowntime = $anunknowntime->sub(new DateInterval('P' . rand(1,10) . 'D'));

                //yeah - as of PHP 5.2.SOMETHING you can use comparison operators on objects directly
                if($anunknowntime>$lastweek_obj and $anunknowntime<$now_obj) {
                    echo $anunknowntime->format('Y m d') . " is between last week and now";
                }
                else {
                    echo $anunknowntime->format('Y m d') . " is not between last week and now";
                }
        
    

If you're confused by how intervals are written in PHP, take a look at the interval spec. Now, here's the same example using intervals in MySQL:

        
            //In MySQL, get the articles that have been published between some random time in the last week and now
            SELECT `title` 
            FROM `articles` 
                WHERE 
            `published_date` 
                BETWEEN 
                DATE_SUB( NOW(), INTERVAL ROUND((RAND() * (7-1))+1) DAY ) 
                    AND 
                NOW();
        
    

Thanks for reading through this. Let me know if you have any qestions about how to deal with time and intervals in PHP and/or MySQL.

comments powered by Disqus