I see a lot of people on forums and on my training courses asking about the best way (or any way) to manage dates stored in 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 timestamp format. A timestamp is simply an integer. Specifically, it’s the number of seconds that have elapsed since midnight on January 1st 1970 (greenwich mean time).
MySQL has three date types for use in columns. These are 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, are nothing like the unix timestamps used in PHP. A TIMESTAMP column is simply a DATETIME column that automatically updates to the current time every time the contents of that record are altered. (That’s a simplification but broadly true and the details are 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 how to work with these two very different date formats – the PHP timestamp integer and the MySQL DATETIME string. There are three common solutions…
One common solution is to store the dates in DATETIME fields and use PHPs date() and strtotime()functions to convert between 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 to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp andFROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are 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 is simply to use the PHP timestamp format everywhere. Since a PHP timestamp is a signed integer, use an integer field in MySQL to store the timestamp in. This way 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, that 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 are dates. You can still sort records on your date fields since php timestamps increase regularly over time, but if you want to use any of MySQL’s date and time functions on the data then you’ll need to use FROM_UNIXTIME to get a MySQL DATETIME for the function to work on.
However, if you’re just using the database to store the date information and any manipulation of it will take place in PHP then there’s no problems. www.2cto.com
So finally we come to the choice of which to use. For me, if you don’t need to manipulate the dates within MySQL then there’s no contest and the last option is the best. It’s simple to use and is the most efficient in terms of storage space in the data table and speed of execution when reading and writing the data.
However, some queries will be more complicated because your date is not in a date field (e.g. select all users who’s birthday is today) and you may lose out in the long run. If this is the case it may 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 use option 2 but there’s no right or wrong answer – take your pick.
So to summarise, for those who’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.
作者:chenjie3392593

热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境

螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

SublimeText3汉化版
中文版,非常好用

EditPlus 中文破解版
体积小,语法高亮,不支持代码提示功能

Atom编辑器mac版下载
最流行的的开源编辑器