将0

将0-1范围内的浮点数转换为时间(Converting a float number in 0-1 range to time)

在我的应用程序中，时间信息存储在db中的float字段中，其中：

0 means 0 AM; 0.5 12 AM 0,999305555555556 is 11:59 PM

转换为时间信息目前在我的应用程序中由Ui组件完成，这些组件旨在以这种方式工作以将时间信息保存为float（这是在SQL Server 2008R2中引入TIME数据类型之前决定的）。

我的问题是： 如何进行这种转换？

In my application time info is stored in db in a float field, where:

0 means 0 AM; 0.5 12 AM 0,999305555555556 is 11:59 PM

The conversion into time information is currently done in my application by Ui components that are designed to work with this way to save time info as float (this was decided before TIME datatype was introduced in SQL Server 2008R2).

My question is: how to perform this conversion?

最满意答案

分钟有60秒， 小时有3600秒， 一天有86400秒，

将你的（0-1）浮点数乘以86400（我们称之为total_seconds），

1）total_seconds除以3600的分区将是你的小时 2）从你的total_seconds减去小时* 3600 3）total_seconds除以60的分层将是你的分钟 4）从你的total_seconds减去分钟* 60 6）剩下的将是你的秒数

DECLARE @raw_input FLOAT = 0.999305555555556 DECLARE @total_seconds INT DECLARE @hours INT, @minutes INT, @seconds INT SET @total_seconds = @raw_input * 86400 SET @hours = FLOOR(@total_seconds/3600) SET @total_seconds = @total_seconds - @hours * 3600 SET @minutes = FLOOR(@total_seconds/60) SET @seconds = @total_seconds - @minutes * 60

编辑：或更简单，适应类似的问题/答案 ：

DECLARE @raw_input FLOAT = 0.999305555555556 DECLARE @secondss decimal = @raw_input*86400 SELECT CAST(CONVERT(VARCHAR,DATEADD(SECOND, @secondss, 0),108) AS TIME) >23:59:00.0000000

Minute has 60 seconds, Hour has 3600 seconds, Day has 86400 seconds,

Multiply your (0-1) float by 86400 (let's call it total_seconds),

1) Floor of division of total_seconds by 3600 will be your hours 2) Subtract hours*3600 from your total_seconds 3) Floor of division of total_seconds by 60 will be your minutes 4) Subtract minutes*60 from your total_seconds 6) What's left will be your seconds

DECLARE @raw_input FLOAT = 0.999305555555556 DECLARE @total_seconds INT DECLARE @hours INT, @minutes INT, @seconds INT SET @total_seconds = @raw_input * 86400 SET @hours = FLOOR(@total_seconds/3600) SET @total_seconds = @total_seconds - @hours * 3600 SET @minutes = FLOOR(@total_seconds/60) SET @seconds = @total_seconds - @minutes * 60

Edit: or much simpler, adapting a similar question/answer:

DECLARE @raw_input FLOAT = 0.999305555555556 DECLARE @secondss decimal = @raw_input*86400 SELECT CAST(CONVERT(VARCHAR,DATEADD(SECOND, @secondss, 0),108) AS TIME) >23:59:00.0000000