MySQL, How to Convert True/False to 1/0 in query

Question

How do I convert boolean to integer in a MySQL query. I have tried numerous, things and have come up empty. I'm simply looking to convert the "value" column of true/false to 1 and 0 respectively into the "finalvalue" column. Please see link of screenshot.

MySQL Query:

SELECT
  UNIX_TIMESTAMP(timestamp) as time_sec,
  topic as metric,
  data as value,
cast(DATA AS SIGNED) as finalvalue
FROM tcl_mqtt_log
WHERE topic = 'jace/home/heatpump/uvlight_ss';

screenshot of mysql_query_result

Answer 1

I think you'll need to use a CASE expression here:

SELECT
    UNIX_TIMESTAMP(timestamp) AS time_sec,
    topic AS metric,
    data AS value,
    CASE WHEN data = 'true' THEN 1 ELSE 0 END AS finalvalue
FROM tcl_mqtt_log
WHERE topic = 'jace/home/heatpump/uvlight_ss';