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 dataPHP 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 timeAs 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.
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 valueThere 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:
mktime(): Windows does not support negative values for this function in c:\geeksengine\test.php on line 11
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:
PHP native date functions use integer timestamps for computations. Because of this, dates are restricted to the years 1901-2038 on Unix and 1970-2038 on Windows due to integer overflow for dates beyond those years. This library overcomes these limitations by replacing the native function's signed integers (normally 32-bits) with PHP floating point numbers (normally 64-bits).
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
Use Greenwich Mean Time (GMT) to record current timeDo 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:
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:
Here comes to the end of this article. Hope you have learned something new and can apply to your applications immediately.
Copyright © 2013 GeeksEngine.com. All Rights Reserved.
This website is hosted by LunarPages.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.