如何在MySQL / PHP中插入和读取时间/日期？

I want to read the single day, month and year, without adding 3 extra MySQL-rows, in this format (with PHP):

我希望阅读单日，月和年，而不添加3个额外的MySQL行，采用这种格式（使用PHP）：

day: 01
month: Jan, Feb, Mar..(first three letters)
year: 2011

This is table and PHP script, which I use now:

这是表和PHP脚本，我现在使用它：

I add the date with PHP:

我用PHP添加日期：

mysql_query("INSERT INTO news (...,`time`) VALUES (...'".date(d.".".m.".".Y)."');");

I read it out with:

我读了以下：

$query = "SELECT * FROM news";
$result = mysql_query ($query);

while ($row = mysql_fetch_assoc ($result)) {
echo $row['time'];
}

MySQL table:

MySQL表：

news:
time(text):
"27.03.2011"

5 个解决方案

#1

Query should be:

查询应该是：

mysql_query("INSERT INTO news (...,`time`) VALUES (...'".date(d.".".M.".".Y)."');");

M instead of m gives you the 3 letter textual representation of the month.

M而不是m为您提供当月的3个字母的文字表示。

Get it with:

得到它：

while ($row = mysql_fetch_assoc ($result)) {
    echo date( 'd', strtotime( str$row['time'] ) );
    echo date( 'M', strtotime( str$row['time'] ) );
    echo date( 'Y', strtotime( str$row['time'] ) );
}

#2

you can either do it in PHP, check the strftime function or use in SELECT something like

你可以用PHP做，检查strftime函数或在SELECT中使用

   SELECT DAY(date) as day, MONTH(date) as month, YEAR(date) as year FROM table

and in php you would acccess it as $result['day']. $result['month'] etc. the "date" in SELECT query is of course the name of the column in which you store your date. I would recommend strftime

在PHP中你会把它作为$ result ['day']。 $ result ['month']等SELECT查询中的“日期”当然是您存储日期的列的名称。我会推荐strftime

#3

You can use MONTH(), DAY(), YEAR() Mysql functions., i.e,

你可以使用MONTH（），DAY（），YEAR（）Mysql函数。，即

SELECT MONTH(`time`) AS `month`, DAY(`time`) AS `day`, YEAR(`time`) AS `year` FROM `news` [...]

#4

SELECT *, substring(1,2) as day, substring(4,2) as month, substring(7) as year FROM table

Also you can(and should) use date table format and use DAY(), MONTH(), YEAR() functions

您也可以（并且应该）使用日期表格式并使用DAY（），MONTH（），YEAR（）函数

#5

You should be using a DATETIME column in your mysql table. MySQL is then responsible for storing the date in its own internal format, and you can retrieve it in any format you need.

您应该在mysql表中使用DATETIME列。然后，MySQL负责以自己的内部格式存储日期，您可以以任何所需的格式检索它。

To insert the current date, you can simply

要插入当前日期，您可以简单地

INSERT INTO news (...,`time`) VALUES (...,NOW())

To retrieve it in the format you want, use

要以您想要的格式检索它，请使用

SELECT DATE_FORMAT(`time`, '%d.%b.%Y');

Documentation on MySQL DATE_FORMAT()

有关MySQL DATE_FORMAT（）的文档

#1