Skype持久聊天tblComplianceParticipant表joinedAt字段日期转换及过滤需求

我之前处理过类似的Skype合规数据库的日期转换需求，那个以63开头的18位数字其实是**.NET DateTime.Ticks**值——它代表从公元0001年1月1日00:00:00以来经过的100纳秒间隔数，是.NET生态里常用的日期存储格式。下面给你几种实用的转换和过滤方案，适配不同的场景：

1. SQL Server 直接转换并过滤 #

如果你的工具直接操作SQL Server数据库，可以用这个公式把ticks转成可读日期，同时完成范围过滤：

SELECT 
    joinedAt,
    -- 转换为yyyy-MM-dd格式的日期
    CONVERT(VARCHAR(10), DATEADD(MILLISECOND, (joinedAt - 621355968000000000) / 10000, '1970-01-01'), 23) AS joinedAt_Date
FROM tblComplianceParticipant
-- 按日期范围过滤
WHERE DATEADD(MILLISECOND, (joinedAt - 621355968000000000) / 10000, '1970-01-01') BETWEEN '2024-01-01' AND '2024-06-01'

公式说明：621355968000000000是0001年1月1日到1970年1月1日的总ticks数，减去这个值后除以10000（1ticks=100纳秒，转换为毫秒），再通过DATEADD映射到标准时间起点，最后用CONVERT格式化你需要的日期格式。

2. .NET代码中转换（更高效的过滤方式） #

因为Skype的合规数据本身基于.NET技术栈，如果你的工具用C#/VB.NET开发，直接在代码里处理会更灵活，而且能提升查询性能：

// 从数据库读取的joinedAt是long类型
long rawTicks = 636572952018269911;
DateTime joinDateTime = new DateTime(rawTicks);
// 格式化为你需要的yyyy-MM-dd字符串
string formattedDate = joinDateTime.ToString("yyyy-MM-dd");

如果要做范围过滤，推荐先把日期范围转成ticks再查询，这样数据库可以直接利用joinedAt字段的索引，避免全表转换：

// 定义你需要的日期范围
DateTime startDate = new DateTime(2024, 1, 1);
DateTime endDate = new DateTime(2024, 6, 1);
// 转换为对应ticks值
long startTicks = startDate.Ticks;
long endTicks = endDate.Ticks;

// 后续SQL查询直接用ticks范围过滤
// SELECT * FROM tblComplianceParticipant WHERE joinedAt BETWEEN @startTicks AND @endTicks

3. MySQL数据库的转换方案 #

如果你的工具对接的是MySQL，转换逻辑类似，只是需要把ticks转成Unix时间戳：

SELECT 
    joinedAt,
    -- 转换为yyyy-MM-dd格式
    DATE_FORMAT(FROM_UNIXTIME((joinedAt - 621355968000000000) / 10000000), '%Y-%m-%d') AS joinedAt_Date
FROM tblComplianceParticipant
WHERE FROM_UNIXTIME((joinedAt - 621355968000000000) / 10000000) BETWEEN '2024-01-01' AND '2024-06-01'

公式说明：MySQL的FROM_UNIXTIME接收秒级时间戳，所以把ticks减去基准值后除以10000000（1ticks=1e-7秒），得到对应的Unix时间戳，再用DATE_FORMAT格式化。

内容的提问来源于stack exchange，提问作者user9032400