How to use Date and Time data as integer value in PHP and MySQL



When it comes to storing date and time data in MySQL, sometimes it is more convenient to store the equivalent integer data rather than storing the data as date or datetime data type. The main reason to do so is related to easiness of data computation and comparison, data portability, and increased query performance with indexes on integer columns.

How PHP uses integer to represent date and time data

PHP can convert date and time to Unix timestamp value which is a long integer containing the number of seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified. Date and time after January 1 1970 00:00:00 GMT will be converted to positive integer value. Date and time before January 1 1970 00:00:00 GMT will be converted to negative integer value. PHP has rich Date and Time Functions to help you manipulate the Unix timestamp value.

The benefits of using integer to represent date and time

As mentioned above, there are three benefits of using integer data to represent date and time data type.

Benefit #1: Easiness of data computation and comparison.

Because in PHP we can convert date and time to Unix timestamp value, we can store the integer in MySQL database. When we need to display the date and time back to users, we can use PHP Date and Time Functions to convert the integer back to normal date and time format.

These integer values are extremely easy for calculation purpose when we need to add or subtract certain time from it. For example, the date and time 2007-11-30 00:00:00 has a timestamp value 1196348400 (which is the number of seconds since January 1 1970 00:00:00 GMT). To add one day to 2007-11-30 00:00:00, we convert one day to 86400 seconds (1 x 24 x 60 x 60) and then add 86400 seconds to 1196348400. That is 86400 + 1196348400 = 1196434800. We then convert 1196434800 back to date and time format for displaying - we get 2007-12-01 00:00:00.

Two useful MySQL functions can be used for conversion between Unix timestamp and normal date and time format.

1. UNIX_TIMESTAMP function

   If called with no argument, UNIX_TIMESTAMP returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT.

   SELECT UNIX_TIMESTAMP('2007-11-30 00:00:00') returns 1196348400.

   

2. FROM_UNIXTIME function

   FROM_UNIXTIME returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' format or other formats depending on whether the function is used in a string or numeric context.

   SELECT FROM_UNIXTIME(1196348400 + 86400) returns 2007-12-01 00:00:00.

   

Benefit #2: Data portability

There are counter arguments about using Unix timestamp for date and time data. The argument is mainly related to the benefits and support we can get from using MySQL date and time functions, especially for dates before 1970 and after 2038.

However, using Unix timestamp makes your application more portable and workable by other languages such as Flash API which only understands Unix timestamp. Using Unix timestamp is a standard way of dealing with date and time across different platforms and will make your application more portable.

Benefit #3: Increased query performance

Comparisons between two values of date and time type are common operations in business applications. Database indexes are often created on date and time columns to improve query speed. Integer data type is a perfect candidate for indexing, especially when (1) not many null values in the column, and (2) the uniqueness of data is very high. Unix timestamp values meet these two conditions nicely.

For example, social networking websites provide onsite messaging system. Members send messages to each other without leaving the site. Message sent time can be defined and store as integer values in Unix timestamp format. Because all messages must have a sent time so no null values involved. Also because Unix timestamp is accurate up to the second, the uniqueness of message sent time is high (if the site is not as big as MySpace). When creating an index on message sent column, it will greatly improve query response time.

Data range limitation of Unix timestamp value

There is a limitation in PHP versions prior to PHP 5.1.0. Note that PHP 5.1.0 and newer versions overcome this limitation though.

On Unix and some Linux operating systems, the valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer.

On Windows and some Linux distributions, the date range is limited to no earlier than the Unix epoch. This means that dates prior to Jan 1, 1970 will not work on these operating systems.

For example, if you use PHP function mktime on Windows to get any date and time before January 1 1970 00:00:00 GMT, you get this error:

You can use code below to replicate this error on your machine:

Because of this limitation, dates in Unix timestamp format are restricted to the years 1901-2038 on Unix or Linux, and 1970-2038 on Windows due to integer overflow for dates beyond those years.

How to overcome the data range limitation of Unix timestamp

To overcome the limitation, use the Date Library which is part of ADOdb Abstraction Library from Sourceforge. ADOdb is a Database Abstraction Library for PHP. There is also a version for Python.

The following is quoted from the Introduction section of the page:

This Date Library is actually an include file (file name adodb-time.inc.php) with various date and time related functions that are similar to those defined in native PHP Date and Time Functions. Therefore, Date Library can be used as a stand-alone include file. The date library does not require the rest of ADOdb. Or, it can be used as part of the ADOdb database wrapper library.

The Date Library replaces native PHP Date and Time functions as follows:

	getdate()  with  adodb_getdate()
	date()     with  adodb_date() 
	gmdate()   with  adodb_gmdate()
	mktime()   with  adodb_mktime()
	gmmktime() with  adodb_gmmktime()
	strftime() with  adodb_strftime()
	strftime() with  adodb_gmstrftime()

How to download and use the Date Library

1. Open the download page at http://sourceforge.net/project/showfiles.php?group_id=42718

2. Click to download the package for PHP 4 and 5.

   

3. Download either the .zip or .tgz file on the subsequently opened page to your computer.

4. Unzip the downloaded file.

5. Find the file named as adodb-time.inc.php

   

6. Copy this file to your website's include directory.

7. Then include it in your PHP page. Assume you have defined your include path.

   <?require_once "adodb-time.inc.php";?>

8. The way functions are used in this include file is similar to the way you normally use PHP native Date and Time functions.

   For example:
   

   <?$birthdate_unix = adodb_mktime(0,0,0, $b_month, $b_date, $b_year);?>

   Very important: Always define the column as signed bigint data type in MySQL.

   When you run the following code, you get -19529622 rather than an error as seen previously on this page when using PHP function mktime:

   <?echo adodb_mktime(7,6,18, 5, 20, 1969);?>

Use Greenwich Mean Time (GMT) to record current time

Do you save current date and time value in database? Most likely your answer is yes. If so, you'd better save it in GMT time.

Using GMT is very handy no matter where your application is used. When display the date and time to the end user, you can convert the GMT to the user's local time based on his/her time zone.

How to get current GMT time value in Unix timestamp format

You can get current GMT by either PHP function or using MySQL function:

1. Use PHP to get current GMT value in Unix timestamp format.

   Here is the PHP code to get current GMT value:

   <?echo time() - date("Z");?>

   Here is a sample PHP code which uses code above to insert a new record in MySQL for the current GMT value. Assume a member sends a message to another member on a social networking website.

   

   <?
       // This is current gmt value in Unix timestamp format.
       $current_gmt = time() - date("Z");
       
       $to_member_id = 123;
       $from_member_id = 567;
       $message_body = "Hi, how are you?";
       
       $sql = "insert into message(to_member_id, from_member_id, body, sent_gmt) ".
              " values(". $to_member_id. ", ". $from_member_id. 
              ", '". mysql_real_escape_string($message_body). 
              "', ". $current_gmt. ")";
   
       mysql_query($sql);
   ?>

2. Use MySQL function to get current GMT value in Unix timestamp format.

   Run the following query in SQLyog and you will get your current GMT value in Unix timestamp format:

   select unix_timestamp(UTC_TIMESTAMP);

   First, UTC_TIMESTAMP returns the current UTC (Universal Time Coordinated, which is just another name for GMT) date and time as a value in 'YYYY-MM-DD HH:MM:SS' format. Then we use unix_timestamp function to convert it to Unix timestamp value format.

   Here is a sample PHP code (similar to the previous one except it uses MySQL function instead of PHP function) to insert a new record in MySQL for the current GMT value. Assume a member sends a message to another member on a social networking website.

   <?
       $to_member_id = 123;
       $from_member_id = 567;
       $message_body = "Hi, how are you?";
       
       $sql = "insert into message(to_member_id, from_member_id, body, sent_gmt) ".
           " values(". $to_member_id. ", ". $from_member_id. 
           ", '". mysql_real_escape_string($message_body). 
           "', unix_timestamp(UTC_TIMESTAMP))";
           
       mysql_query($sql);
   ?>

How to determine and use the user's time zone info

We have mentioned that time zone info can be used to adjust a GMT time to the user's local time for displaying purpose. There are three ways to obtain a user's time zone info in your application:

1. Let the user specify his/her time zone.

   This is the most flexible way as when a user relocates to a different time zone, the time zone info can be updated by themselves.

   For more accuracy, you can even design Daylight Saving feature into your application to let the user select Daylight Saving adjustment - How many hours they need to move their clock ahead.

2. Determine the user's time zone by IP address.

   IP and geo-location can be purchased from companies such as IP2Location. For example, IP-Country-Region-City-Latitude-Longitude-ZIPCode-time zone database enables solution to determine country, region or state, city, latitude, longitude, US zip code and time zone of any IP address in a few simple steps.

3. Hardcode the time zone info in PHP if you know the time zone will never change in your application.

Here comes to the end of this article. Hope you have learned something new and can apply to your applications immediately.


Copyright© GeeksEngine.com

Related Articles: 1. Steps to install PHP 5.x on Windows as a development machine 2. How to install Apache 2.x web server on Windows 3. How to connect two different versions of MySQL server on the same computer 4. How to configure MySQL server 5.1 on Windows 5. How to install MySQL server 5.1 on Windows with screenshots 6. Five ways to create include path for PHP 7. How to use Apache Virtual Host to run multiple local websites on Windows 8. Install all PEAR packages by yourself 9. How to install PEAR on Windows 10. How to use PHP and Microsoft SMTP Virtual Server to send emails 11. How to install PHP server-side scripting language on Windows 12. How to install Apache 1.3 web server on Windows 13. How to install two different versions of MySQL server on the same PC 14. How to configure MySQL server 4.1 on Windows 15. How to install MySQL server 4.1 on Windows with screenshots 16. Export Northwind Access database to MySQL via ODBC Other Recent Articles from the Web Development category: 1. Connect to a MySQL Database from PHP version 5 or later versions 2. Java / JSP lost session value on redirect - FIXED 3. Fix the problem with PHP5 XML removeChild() method 4. How to integrate PHP HTML Help .chm file with Crimson Editor 5. How to Connect to a MySQL Database from PHP (version lower than PHP 5.0.0) 6. Use MySQL String Functions to Build Printable ASCII Character Chart 7. Five ways to create include path for PHP 8. Absolute Path and Relative Path Explained