SQL Server 以"X 秒/分钟/小时/天"格式获取时间

Question

我正在开发SQL SERVER 2016。我需要向用户显示自上次操作以来经过的时间。它的格式应该类似于"；最后一次行动发生在3分钟前；或"；最后一次行动是在5天前"；。我需要间隔的动态日期部分，因为我不知道已经过去了多少时间。系统应获取时间间隔(如果小于一小时，则必须为分钟。如果小于一天，则应为小时(和以该间隔格式通过的时间(如3小时前或1天前(。

我现在写这个

FORMAT(DATEDIFF(MINUTE,MAX(myDate),GETDATE()), 'MINUTE' , 'en-US')

但它的结果是

"最后一次行动是在几分钟前"；

并且我还想获得其他格式的时间，如"；5天前"；或"；4小时前"；如果时间间隔已经改变。

我怎么能做到这一点，我找不到正确的datediff间隔和FORMAT

Answer 1

我想你想要：

select 'Last action was '
(case when datediff(day, MAX(myDate), GETDATE()) > 0
then concat(datediff(day, MAX(myDate), GETDATE()), ' day(s) ago')
when datediff(hour, MAX(myDate), GETDATE()) > 0
then concat(datediff(hour, MAX(myDate), GETDATE()), ' hours(s) ago')
when datediff(minute, MAX(myDate), GETDATE()) > 0
then concat(datediff(minute, MAX(myDate), GETDATE()), ' minute(s) ago')
when datediff(second, MAX(myDate), GETDATE()) > 0
then concat(datediff(second, MAX(myDate), GETDATE()), ' second(s) ago')
else 'Just happened!'
end)

case以最大单位(天(开始，并检查是否适用。

实际上，datediff()可能不是您想要的。它与日历天对齐。因此，"；1天前"；真正的意思是"；昨天"；不管时间如何。也许更准确的方法是：

select 'Last action was '
(case when dateadd(day, 1, MAX(myDate) < GETDATE()
then concat(datediff(day, MAX(myDate), GETDATE()), ' day(s) ago')
when dateadd(hour, 1, MAX(myDate)) < GETDATE())
then concat(datediff(hour, MAX(myDate), GETDATE()), ' hours(s) ago')
when dateadd(minute, 1, MAX(myDate)) < GETDATE()
then concat(datediff(minute, MAX(myDate), GETDATE()), ' minute(s) ago')
when dateadd(second, 1, MAX(myDate)) < GETDATE())
then concat(datediff(second, MAX(myDate), GETDATE()), ' second(s) ago')
else 'Just happened!'
end)

这保证了在选择该单元之前，过去至少有一个完整的单元。

Answer 2

以下是一个示例，它使用CONCAT和一些硬编码值来根据您的问题生成输出：

SELECT
CONCAT('Last Action was ',
DATEDIFF(MINUTE, '2020-10-07T11:06:22', '2020-10-07T11:07:22'),
' MINUTE ago'
) AS "Elapsed"

输出为

|         Elapsed            |
------------------------------
|Last Action was 1 MINUTE ago|

如果您想考虑不同的单位，您必须用您选择的值和DATEDIFF函数的间隔来替换这些硬编码的时间戳。

另一个可能的解决方案是：

SELECT
'Last Action was ' +
CASE WHEN DATEDIFF(DAY, '2020-10-07T11:06:22', '2020-10-08T11:07:22') > 0
THEN CONCAT(DATEDIFF(DAY, '2020-10-07T11:06:22', '2020-10-08T11:07:22'), ' DAY(S)')
ELSE ''
END +
CASE WHEN DATEDIFF(HOUR, '2020-10-07T11:06:22', '2020-10-08T11:07:22') % 24 > 0
THEN CONCAT(DATEDIFF(HOUR, '2020-10-07T11:06:22', '2020-10-08T11:07:22') % 24, ' HOUR(S)')
ELSE ''
END + ' and ' +
CASE WHEN DATEDIFF(MINUTE, '2020-10-07T11:06:22', '2020-10-08T11:07:22') % 60 > 0
THEN CONCAT(DATEDIFF(MINUTE, '2020-10-07T11:06:22', '2020-10-08T11:07:22') % 60, ' MINUTE(S) ago')
ELSE ''
END AS "Message"

输出

|                  Message                     |
------------------------------------------------
| Last Action was 1 DAY(S) and 1 MINUTE(S) ago |

正如您所看到的，结果列中没有提到小时，因为它们的值正好是24小时，而这已经在一天内打印出来了。

为了获得每一个可能的结果，你必须四处走动上次操作是2小时1分钟前或最后操作是1天2小时1分前