MS-SQL：如何选择编码UTC时间的记录？

Summary: How to extract records that belong to the UTC time interval, when the UTC time in the key column is encoded?

摘要：当密钥列中的UTC时间被编码时，如何提取属于UTC时间间隔的记录？

The UTC encoding: I do have a third-party table where records contain UTC time encoded as strings like '2456065.80700942:0000002F'. To get the UTC of the DATETIME type, the following formula can be used:

UTC编码：我有一个第三方表，其中记录包含编码为'2456065.80700942：0000002F'等字符串的UTC时间。要获取DATETIME类型的UTC，可以使用以下公式：

CAST((CAST(LEFT(encodedUTC, 16) AS FLOAT) - 2415020.5) AS DATETIME)

They say, the part before the colon is the Julian date encoded to float (and the float to the string). I did not check, it seems to work.

他们说，冒号之前的部分是Julian日期编码浮动（浮点数到字符串）。我没有检查，似乎工作。

The query: Now I have @fromUTC and @toUTC of the DATETIME type. I can select:

查询：现在我有DATETIME类型的@fromUTC和@toUTC。我可以选择：

SELECT CAST((CAST(LEFT(d.UTC, 16) AS FLOAT) - 2415020.5) AS DATETIME) AS UTC,
       ...
FROM dbo.Data AS d
WHERE CAST((CAST(LEFT(d.UTC, 16) AS FLOAT) - 2415020.5) AS DATETIME) > @fromUTC 
      AND CAST((CAST(LEFT(d.UTC, 16) AS FLOAT) - 2415020.5) AS DATETIME) < @toUTC

(I am not using BETWEEN as I do not want to get the records with equal times.) It works, but the problem is that the conversion formula is repeated 3 times (and I do not like the repeating of the same code if it is not neccessary).

（我不使用BETWEEN，因为我不想在相同的时间内获取记录。）它有效，但问题是转换公式重复了3次（我不喜欢重复相同的代码，如果它是不是必要的）。

My question is: How can I write the SELECT better?

我的问题是：如何更好地编写SELECT？

Thanks for your time and information,

感谢您的时间和信息，

Petr

切赫

2 个解决方案

#1

I'd consider either adding a view that includes this calculation, or a computed column on the table itself (if you can change the table definition).

我考虑添加包含此计算的视图，或者在表本身上添加计算列（如果可以更改表定义）。

Either of these approaches would lead to a cleaner final select. There's likely some performance considerations based on how often you select versus insert, so will probably shade you one way or the other.

这些方法中的任何一种都会导致最终选择更清晰。根据您选择与插入的频率，可能会考虑一些性能因素，因此可能会以某种方式遮挡您。

#2

use CTE like

像CTE一样使用

;with  cteName (UTC) as 
(
select SELECT CAST((CAST(LEFT(d.UTC, 16) AS FLOAT) - 2415020.5) AS DATETIME) AS UTC
FROM dbo.Data
)
select UTC
from cts where uct > @fromUTC and utc < @toUTC

#1