I get a datetime field, that's currently in the query as:
我得到一个datetime字段，当前在查询中：
SELECT DATE_FORMAT(x.date_entered, '%Y-%m-%d') AS date FROM x ORDER BY date ASC
What I want to do is to subtract 3 hours from that date (GMT issues), but I can't do it in PHP as PHP only knows the date part, not the time.
我想要做的是从那个日期减去3个小时（GMT问题），但我不能用PHP来做，因为PHP只知道日期部分，而不是时间。
2 个解决方案
#1
64
mySQL has DATE_SUB():
mySQL有DATE_SUB（）：
SELECT DATE_SUB(column, INTERVAL 3 HOUR)....
but would it not be better to try and sort out the underlying time zone issue instead?
但尝试解决潜在的时区问题会不会更好？
#2
0
Assuming you have some timezone issue and know source and destination timezone, you could convert it like so
假设您有一些时区问题并知道源和目标时区，您可以像这样转换它
SELECT DATE_FORMAT(CONVERT_TZ(x.date_entered, 'UTC', 'Europe/Berlin'),
'%Y-%m-%d') AS date
FROM x ORDER BY date ASC;
#1
64
mySQL has DATE_SUB():
mySQL有DATE_SUB（）：
SELECT DATE_SUB(column, INTERVAL 3 HOUR)....
but would it not be better to try and sort out the underlying time zone issue instead?
但尝试解决潜在的时区问题会不会更好？
#2
0
Assuming you have some timezone issue and know source and destination timezone, you could convert it like so
假设您有一些时区问题并知道源和目标时区，您可以像这样转换它
SELECT DATE_FORMAT(CONVERT_TZ(x.date_entered, 'UTC', 'Europe/Berlin'),
'%Y-%m-%d') AS date
FROM x ORDER BY date ASC;