将24小时格式化的时间转换为十进制数 [英] Convert 24-Hour Formatted Time Into Decimal Number

问题描述

我想显示17.5而不是17:30:00.如何实现呢?我已经搜索了，但没有找到.

I want to display 17.5 instead of 17:30:00. How to achieve this? I've searched but nothing found.

我想在查询中执行以下操作:

I want to do it inside my query like:

SELECT o.*, u.* ,p.* ,
        SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto, '%m/%d/%y %H:%i:%s'), '%Y-%m-%d %H:%i:%s')) - TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(datefrom, '%m/%d/%y %H:%i:%s'), '%Y-%m-%d %H:%i:%s')))) AS totalHrs,

        number_format((select SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto, '%m/%d/%y %H:%i:%s'), '%Y-%m-%d %H:%i:%s')) - TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(datefrom, '%m/%d/%y %H:%i:%s'), '%Y-%m-%d %H:%i:%s')))) FROM overtime WHERE approve_by=0)) AS grandTotal

        FROM overtime AS o, users AS u, position AS p
        WHERE o.user_id=u.user_id and u.p_id=p.p_id and o.approve_by=0
        GROUP BY p.p_id
        ORDER BY p.position_name ASC

推荐答案

如果您有时间值，则类似以下内容:

If you have a time value, then something like this:

select time_to_sec(timeval) / (60 * 60)

如果要将其设置为小数点后1位，则转换为小数点:

If you want it to 1 decimal, then convert to a decimal:

select cast(time_to_sec(timeval) / (60 * 60) as decimal(10, 1))

这不太适合您的查询，这似乎是不重要的.

It is not obvious how this fits into your query, which seems a non-sequitur.

这篇关于将24小时格式化的时间转换为十进制数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！