在MySQL中添加秒数到datetime [英] Adding Seconds to datetime in MySQL

问题描述

我正在尝试根据发生的某些事件添加几秒钟的日期。通常情况下，如果这些事件同时发生，则会添加太多秒。以下是我目前在PHP和MySQL中使用的方式。

I'm trying to add seconds to a date based on certain events that occur. Often times, if these events occur at the same time, too many seconds get added on. Here's how I am currently doing it in PHP and MySQL

$time_add_on = 15 - $seconds_left;
DATE_ADD(STR_TO_DATE(end_dt,'%Y-%m-%d %H:%i:%s'), INTERVAL '".$time_add_on."' SECOND

这样做是从'end_dt'中减去15秒的当前秒数，并将其添加到'end_dt'，基本上给你15秒左边。

What this is doing is taking the current seconds left from the 'end_dt' subtracting 15 and adding it to the 'end_dt', basically giving you 15 seconds left.

这里的目标基本上也是增加15秒，或者将日期重置为只剩下15秒，这导致问题，而不是重置为15秒剩下的时间会增加30秒或45秒。

The goal here is basically too add 15 seconds or reset the date to where only 15 seconds are left. This is causing issues, where instead of resetting to 15 seconds left, it'll add 30 seconds or 45 seconds.

有人会知道最好的方法吗？

Would anyone know the best way to do this?

推荐答案

UPDATE table end_dt = DATE_ADD(end_dt, INTERVAL 15 second)
WHERE DATE_SUB(end_dt, INTERVAL 15 second) <= NOW()

我认为这是你想要的，当end_dt是15秒钟时，基本上增加了15秒的end_dt从现在开始

I think that's what you want, basically adds 15 seconds to end_dt when end_dt is 15 seconds away from now

编辑新查询
此查询应该工作：

EDIT NEW QUERY This query should work:

UPDATE `table`
    SET end_dt = DATE_ADD(end_dt, INTERVAL (15 - TIMESTAMPDIFF(SECOND, NOW(), end_dt)) SECOND)
WHERE DATE_SUB(end_dt, INTERVAL 15 second) <= NOW()

这篇关于在MySQL中添加秒数到datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！