PHP MYSQL手动更新db中的日期和时间字段

I am trying to figure out how to update a MYSQL DATE and TIME field manually (NOT to todays date!)ie set it the date field to a certain date value and time field to a certain time field correctly in the correct SQL required field format.

我正在试图弄清楚如何手动更新MYSQL日期和时间字段(而不是todays DATE !)(在正确的SQL所需的字段格式中，将日期字段设置为特定的日期值和时间字段到某个时间字段)。

I am using an UPDATE query not INSERT as my action is to update a users field

我使用的是更新查询而不是插入，因为我的操作是更新用户字段

Done some research and came up with something along the lines of (obviously this example wont work but does anyone know how to format this query?

做了一些研究，得出了一些类似的结论(显然这个例子行不通，但是有人知道如何格式化这个查询吗?)

UPDATE mytblname SET date='DATE: Manual Date', '2011-06-14'', time='TIME: Manual Time', '12:10:00' WHERE email='somevalue'

If I just enter the value as normal SQL way it gives 0000-00-00 for date and 00:00:00 for time - ie

如果我以常规的SQL方式输入值，它会为date提供000000，为time提供00:00

SET date='2011-06-14',time='12:33:35'

Thanks for any suggestions, really appreciate it!!!

谢谢您的建议，非常感谢!!

2 个解决方案

#1

UPDATE mytblname SET `date`="2011-06-14", `time`="12:10:00" WHERE email="somevalue";

This should work fine. Make sure you have the appropriate backticks around date and time.

这应该工作很好。确保你在日期和时间前后有合适的背景。

#2

Please refer to the MySQL Documentation on DATE, TIME and DATETIME formats. You can see there, that there are multiple possibilities of the values that can be assigned to fields of these types.

请参阅MySQL文档的日期、时间和日期时间格式。您可以看到，可以为这些类型的字段分配值的可能性有很多。

So this should work:

这应该工作:

UPDATE `mytblname` SET `date`=NOW(), `time`=NOW() WHERE `email`='somevalue';

or to any specific date like that (the string will be automatically converted to DATE, TIME or DATETIME format):

或任何特定的日期(字符串将自动转换为日期、时间或时间格式):

UPDATE `mytblname`
SET
`date`='1987-01-02 11:22:33',
`time`='1987-01-02 11:22:33'
WHERE `email`='somevalue';

You can also assign it like that, which is more clear:

你也可以这样分配，更明确的是:

UPDATE `mytblname`
SET
`date`='1987-01-02',
`time`='11:22:33'
WHERE `email`='somevalue';

The only question is, which path will you choose :)

唯一的问题是，你会选择哪条路?

#1

UPDATE mytblname SET `date`="2011-06-14", `time`="12:10:00" WHERE email="somevalue";