Reputation: 1608
UPDATE
The question needs clarity.
I'm converting converting milliseconds to hours minutes seconds to create a date independent timestamp.
Given time in milliseconds, I want the output to be a string in H:M:S E.g.
9999999ms = 2.7777775 hrs
.7777775hrs * 60 = 46.66665 min
.66665min * 60 = 39.999sec
Desired output 02:46:39.999
This has nothing to do with TIMESTAMP. It is a simple calculation followed by a string concatenation.
I'm having a frustrating time with the CONCATENATION. IT only returns hours and it does not ROUND the returned value.
DECLARE v_timestamp VARCHAR(8);
DECLARE v_hours DOUBLE(5,3);
DECLARE v_minutes DOUBLE(5,3);
DECLARE v_seconds DOUBLE(5,3);
SET v_hours = (NEW.amount_viewed_ms)/(3600000);
SET v_minutes = (v_hours - FLOOR(v_hours)) * 60;
SET v_seconds = (v_minutes - FLOOR(v_minutes)) * 60;
SET v_timestamp = CONCAT(ROUND(v_hours), ":", ROUND(v_minutes), ":", ROUND(v_seconds));
SET NEW.timestamp = v_timestamp;
I could use another pair of eyes to help figure out where this is going wrong.
Upvotes: 0
Views: 87
Reputation: 17655
I really don't see your problem simply increasing a couple of field sizes seems to produce a desired result without an example of NEW.amount_viewed_ms it's not possible to say more
DROP PROCEDURE IF EXISTS P;
DELIMITER $$
CREATE PROCEDURE P()
BEGIN
DECLARE v_timestamp VARCHAR(20);
DECLARE v_hours double(20,3);
DECLARE v_minutes DOUBLE(5,3);
DECLARE v_seconds DOUBLE(5,3);
select (UNIX_TIMESTAMP(CONCAT(DATE(NOW()), ' ', CURTIME(3))))/(3600000);
SET v_hours = (UNIX_TIMESTAMP(CONCAT(DATE(NOW()), ' ', CURTIME(3))))/(3600000);
SET v_minutes = (v_hours - FLOOR(v_hours)) * 60;
SET v_seconds = (v_minutes - FLOOR(v_minutes)) * 60;
select v_hours,v_minutes,v_seconds, (UNIX_TIMESTAMP(CONCAT(DATE(NOW()), ' ', CURTIME(3))))/(3600000);
SET v_timestamp = CONCAT(ROUND(v_hours), ":", ROUND(v_minutes), ":", ROUND(v_seconds));
select v_timestamp;
END $$
DELIMITER ;
CALL P();
MariaDB [sandbox]> call p();
+------------------------------------------------------------------+
| (UNIX_TIMESTAMP(CONCAT(DATE(NOW()), ' ', CURTIME(3))))/(3600000) |
+------------------------------------------------------------------+
| 457.2335910 |
+------------------------------------------------------------------+
1 row in set (0.002 sec)
+---------+-----------+-----------+------------------------------------------------------------------+
| v_hours | v_minutes | v_seconds | (UNIX_TIMESTAMP(CONCAT(DATE(NOW()), ' ', CURTIME(3))))/(3600000) |
+---------+-----------+-----------+------------------------------------------------------------------+
| 457.234 | 14.040 | 2.400 | 457.2335910 |
+---------+-----------+-----------+------------------------------------------------------------------+
1 row in set (0.025 sec)
+-------------+
| v_timestamp |
+-------------+
| 457:14:2 |
+-------------+
1 row in set (0.032 sec)
Upvotes: 1