Page 1 of 1
Forum

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.

Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is most likely no translated for the post!




MySQL - Round CURRE...
 
Share:
Notifications
Clear all

MySQL - Round CURRENT_TIMESTAMP to nearest minutes


 Hans
(@hans)
Noble Member Admin
Joined: 8 years ago
Posts: 1840
Topic starter  

For the Arduino Temperature logged, I was looking for an answer to get temperatures nicely logged by the exact minute.
Now we can't do much on the Arduino side, but we might have been able to do something on the MySQL side of this project.

select CURRENT_TIMESTAMP, FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(CURRENT_TIMESTAMP)/(60))*(60));

Now to put that in a project like that, we might need to introduce triggers, a working example:

CREATE TABLE `roundingtest` (
  `value` int(11) NOT NULL,
  `datetimeNormal` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `datetimeRounded` timestamp
);
CREATE TRIGGER `roundingtest_before_ins_tr` BEFORE INSERT ON `roundingtest`
  FOR EACH ROW
BEGIN
  SET new.datetimeRounded = FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(new.datetimeNormal)/(60))*(60));
END;
CREATE TRIGGER `roundingtest_before_upd_tr` BEFORE UPDATE ON `roundingtest`
  FOR EACH ROW
BEGIN
  SET new.datetimeRounded = FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(new.datetimeNormal)/(60))*(60));
END;

Whenever a record is being added or updated, the time will DatetimeNormal will be set to "Now", and the update or insert trigger will round that timstamp to the nearest minute.


ReplyQuote

Like what you see and you'd like to help out? 

The best way to help is of course by assisting others with their questions here in the forum, but you can also help us out in other ways:

- Do your shopping at Amazon, it will not cost you anything extra but may generate a small commission for us,
- send a cup of coffee through PayPal ($5, $10, $20, or custom amount),
- become a Patreon,
- donate BitCoin (BTC), or BitCoinCash (BCH).

Share: