0

Greetings.

I have a MySQL table Time_worked with a column Time_worked as follows:

| TIME_WORKED                 | varchar(500) | YES  |     | NULL    |       |

I am trying to find the sum(Time_Worked) in a Spring application using the JDBCTemplate as follows:

String totalEffortQuery = "SELECT sum(TIME_WORKED) FROM time_worked WHERE USER = ? and UPDATED>= now() - INTERVAL 1 DAY";
        Long totalEffortSeconds = jdbcTemplate.queryForObject(
                totalEffortQuery, new Object[] { assignedTo }, Long.class);

But the totalEffortSeconds always has a NULL value.

But when I run this query directly in the database as follows it returns results:

SELECT sum(TIME_WORKED) FROM time_worked WHERE USER = 'Vishwanath Krishna Bhat' and UPDATED>= now() - INTERVAL 1 DAY;
+------------------+
| sum(TIME_WORKED) |
+------------------+
|            43200 |
+------------------+
1 row in set (0.02 sec)

I read it somewhere that you cannot use SUM on a non numeric value, but it does work when i use it directly in the database.

Kindly note that I am passing correct USER value in my Java code and that is not the issue.

Can someone please help me out on this ?

Regards, Vishwa

Vishwa Bhat
  • 77
  • 11

1 Answers1

0

You can cast the varchar value to UNSIGNED.

the modified query will be 

String totalEffortQuery = "SELECT sum(CAST(TIME_WORKED AS UNSIGNED)) FROM time_worked WHERE USER = ? and UPDATED>= now() - INTERVAL 1 DAY";
        Long totalEffortSeconds = jdbcTemplate.queryForObject(
                totalEffortQuery, new Object[] { assignedTo }, Long.class)

but this is not a right way because this can consume lot of cup resources, better you can convert the column to numeric data type, if not possible add another column with numeric data type and add a trigger on insert and update to store the value in that column so that you can use for this operation.

Nitish Dhapodkar
  • 86
  • 3
  • This still gives the same error. I think I will have to change the column to numeric. – Vishwa Bhat Sep 26 '18 at 04:10
  • Even after changing the column datatype to BIGINT and running sum(Time_worked) in my java code, it still has the same issue. What am i doing wrong ? – Vishwa Bhat Sep 26 '18 at 04:35