I have been stuck for a while with this query and was wondering if anyone here knows what I may be doing wrong.
The problem I'm facing is that the following query sends me a 0 instead of a -5 and if I remove the "CAST" function, I get -05, so it would appear as if the string -05 is not being converted to -5, however if I replace the entire "GROUP_CONCAT" function by '-05' then the "CAST" function DOES convert -05 to -5... is there something I'm missing here?
SELECT CAST(GROUP_CONCAT(if(FIELD_ID = 8, LEFT(VALUE,3), NULL)) as SIGNED INTEGER) as 'user_timezone'
FROM tn_cimy_uef_data
WHERE user_id = '58';
Expected Result: -5
Actual Result: 0 (NOT GOOD)
SELECT GROUP_CONCAT(if(FIELD_ID = 8, LEFT(VALUE,3), NULL)) as 'user_timezone'
FROM tn_cimy_uef_data
WHERE user_id = '58';
Excpected Result: -05
Actual Result: -05 (GOOD)
SELECT CAST('-05') as 'user_timezone'
FROM tn_cimy_uef_data
WHERE user_id = '58';
Excpected Result: -5
Actual Result: -5 (GOOD)
The table looks like this (table tn_cimy_uef_data):
ID USER_ID FIELD_ID VALUE
1 58 1 12121212
2 58 2 12121212
3 58 3 lorem ipsum
4 58 4 lorem ipsum
5 58 5 lorem ipsum
6 58 6 lorem ipsum
7 58 7 lorem ipsum
8 58 8 −05:00:00 -> America/Miami
9 58 9 lorem ipsum
10 58 10 12121212
I appreciate if anyone could shed some light in this darkness xD
Thanks, P
