Dates in PHP and mysql_php tutorials

Source: Internet
Author: User
I see a lot of people in forums and on my training courses asking about the best-of-the-the-part to manage dates stored I n a MySQL database and used in PHP. Three options follow, but first the problem.
PHP uses UNIX timestamps for all its date functionality. It has methods to convert these timestamps into pretty much any text format you could want but internally it uses the time Stamp format. A timestamp is simply an integer. Specifically, it's the number of seconds that has elapsed since midnight on January 1st 1970 (Greenwich Mean Time).
MySQL has three date types for use in columns. These is DATETIME, DATE, and TIMESTAMP. DATETIME Columns Store date and time as a string in the form Yyyy-mm-dd HH:MM:SS (e.g. 2006-12-25 13:43:15). Date columns use just the date part of this format–yyyy-mm-dd (e.g. 2006-12-25). TIMESTAMP columns, despite their name, is nothing like the Unix timestamps used in PHP. A TIMESTAMP column is simply a DATETIME column, automatically updates to the current time every time the contents of T Hat record is altered. (That's a simplification but broadly true and the details is not important here). In particular, since version 4.1 of MySQL the TIMESTAMP format is exactly the same as the DATETIME format.
So the problem is what to work with these-very different date formats–the PHP timestamp integer and the MySQL datetim E string. There is three common solutions ...
One common solution is to store the dates in DATETIME fields and use PHPs date () and Strtotime () functions to convert Betwe En PHP timestamps and MySQL DateTimes. The methods would be used as follows-
$mysqldate = Date (' y-m-d h:i:s ', $phpdate);
$phpdate = Strtotime ($mysqldate);
Our second-option is-let MySQL does the work. MySQL have functions we can use to convert the data at the point where we access the database. Unix_timestamp would convert from datetime to PHP TIMESTAMP andfrom_unixtime would convert from PHP TIMESTAMP to DateTime. The methods is used within the SQL query. So we insert and update dates using queries like this-
$query = "UPDATE table SET
Datetimefield = From_unixtime ($phpdate)
WHERE ... ";
$query = "Select Unix_timestamp (Datetimefield)
From table WHERE ... ";
Our last option was simply to use the PHP timestamp format everywhere. Since a PHP timestamp is a signed integer with the use of an integer field in MySQL to store the timestamp in. This is the There s no conversion and we can just move PHP timestamps into and out of the database without any issues at all.
Be aware, however, so by using an integer field to store your dates you lose a lot of functionality within MySQL because MySQL doesn ' t know that your dates is dates. You can still sort records in your date fields since PHP timestamps increase regularly NY of MySQL ' s date and time functions on the data so you'll need to use from_unixtime to get a MySQL DATETIME for the FU Nction to work on.
However, if you ' re just using the database to store the date information and any manipulation of it would take a place in PHP Then there ' s no problems. Www.2cto.com
So-finally we come to the choice of which. For me, if you don ' t need to manipulate the dates within MySQL then there's no contest and the last option was the best. It's simple-to-use and was the most efficient in terms of storage space in the data table and speed of execution when Readi Ng and writing the data.
However, some queries'll be more complicated because your date was not in a date field (e.g. select All users who's birth Day is today) and your may lose out in the long run. If the case it could be better to use either option 1 or 2. Which of these you use depends on whether you ' d rather place the work on MySQL or PHP. I tend to with option 2 but there's no right or wrong answer–take your pick.
So-summarise, for those who's ve skipped straight to the last paragraph, most of the time I use option 3 but occasionally I use option 2 because I need MySQL to know the field contains a date.

Author: chenjie3392593

http://www.bkjia.com/PHPjc/478085.html www.bkjia.com true http://www.bkjia.com/PHPjc/478085.html techarticle I see a lot of people in forums and on My training courses asking about the best by (or any) to manage dates Stor Ed in a MySQL database and used in PHP. Three options follow, b ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.