SOLVED: Spent Time rounding down to nearest whole number
Added by Ian Jones about 8 years ago
Greetings,
I am having an issue where if any of my redmine users or myself log any kind of time for an issue in the Log Time Module in the Spent Time box, it will drop any decimal upon hitting the submit button.
Example:
In Spent Time box of an issue I put 2h30m as soon as I switch to the comments box it will correct it to the proper decimal/float number of 2.5.
Once the submit button is pressed, and the successful edit refreshes the page I look at the Spent Time field it shows 2.00 h.
This can be reproduced indefinitely with 2,5, 150min, 2.5, ect. I have tested with all of the proper time formats that redmine uses. I do not understand why or where this dropping of the decimals is occurring. I have spent several days googling and searching the redmine forums looking for anyone having a similar issue and can not find anything even remotely close to my issue. Even when the plugin time logger is used it will drop the decimals in the same fashion. I am not a PostgreSQL or redmine guru by any means but based on the googling I did, I can decipher that I am either an edge case or I'm missing something.
If anyone can shed any light on this or has had this issue please chime in or if more information is needed please ask.
Thank you,
~Ian
OS:
CentOS 7 x64
Environment:
Redmine version 3.2.1.stable.15273
Ruby version 2.0.0-p598 (2014-11-13) [x86_64-linux]
Rails version 4.2.5.2
Environment production
Database adapter PostgreSQL 9.5.1
SCM:
Subversion 1.7.14
Mercurial 2.6.2
Bazaar 2.5.1
Git 1.8.3.1
Filesystem
Redmine plugins:
redmine_checklists 3.1.3
redmine_issue_badge 0.0.2
time_logger 0.5.3
SOLUTION:
After another few days of searching and some help from a good PostgreSQL person, it came to light that I was missing something...
It is a setting in the PostgreSQL redmine database.
Specifically the hours entry in the time_entries database, the numeric(11,0) was holing us back. While the numeric was correct the "(11,0)" was stopping us from saving any decimals due to the 0. In the numeric entry in PostgreSQL it can be read like this, numeric(precision,scale), where scale is the amount of digits allowed to be displayed after the decimal, and precision is the total number of digits allowed in the number (including decimals). Since scale was set to 0 no decimal numbers were being kept in the database.
Our solution:
$ psql redmine
redmine=# \d+ time_entries
Table "public.time_entries"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+-----------------------------+-----------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('time_entries_id_seq'::regclass) | plain | |
project_id | integer | not null | plain | |
user_id | integer | not null | plain | |
issue_id | integer | | plain | |
hours | numeric(11,0) | not null | main | |
comments | character varying(1024) | | extended | |
activity_id | integer | not null | plain | |
spent_on | date | not null | plain | |
tyear | integer | not null | plain | |
tmonth | integer | not null | plain | |
tweek | integer | not null | plain | |
created_on | timestamp without time zone | not null | plain | |
updated_on | timestamp without time zone | not null | plain | |
Indexes:
"time_entries_pkey" PRIMARY KEY, btree (id)
"index_time_entries_on_activity_id" btree (activity_id)
"index_time_entries_on_created_on" btree (created_on)
"index_time_entries_on_user_id" btree (user_id)
"time_entries_issue_id" btree (issue_id)
"time_entries_project_id" btree (project_id)
redmine=# ALTER TABLE time_entries ALTER COLUMN hours TYPE numeric;
ALTER TABLE
redmine=# \d+ time_entries
Table "public.time_entries"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+-----------------------------+-----------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('time_entries_id_seq'::regclass) | plain | |
project_id | integer | not null | plain | |
user_id | integer | not null | plain | |
issue_id | integer | | plain | |
hours | numeric | not null | main | |
comments | character varying(1024) | | extended | |
activity_id | integer | not null | plain | |
spent_on | date | not null | plain | |
<truncated>
The explanation of the above:
First we connect to the redmine database with psql redmine
Second we looked at the table_entries table to find that numeric(11,0) was set
Third we adjusted the numeric value of the table using the ALTER TABLE command
Fourth be sure to restart redmine (on my system: systemctl restart httpd) in order to see these adjustments.