Ken Ingram
Ken Ingram

Reputation: 1608

MySQL CONCAT DOUBLE values in trigger

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

Answers (1)

P.Salmon
P.Salmon

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

Related Questions